Posts

Thank you for confirming your email.

Explanation In this example, the goal is to sum numeric values by month while ignoring the year that contains the date. The solution below is based on the SUMPRODUCT function, the MONTH function, and Boolean algebra . For convenience, amount (C5:C16) and date (B5:B16) are named ranges . Basic concept The basic concept in this formula is to extract just the month number from all dates and test this number against the month number of interest....

Explanation In this example, the goal is to sum amounts in column C when the date in column B is between two given dates. The start date is provided in cell E5, and the end date is provided in cell F5. The date range should be inclusive - both the start date and end date should be included in the final result. A good way to solve this problem is with the SUMIFS function ....

Purpose Return value Syntax =TIME(hour,minute,second) hour - Number of hours. minute - Number of minutes. second - Number of seconds. Using the TIME function The TIME function creates a valid Excel time using the given values for hour , minute , and second . Like all Excel time, the result is a number that represents a fractional day . The TIME function will only return time values up to one full day, between 0 (zero) to 0....

Transcript In this video, we’ll look at how to use a throwaway chart to explore data. In most cases, you’ll want to take time to build clean, uncluttered charts that are easy to read. However, a quick disposable chart is a great way to check and explore data. For example, in this worksheet, I have some sample sales data from a Microsoft tutorial on pivot tables. I don’t really know much about this data, but I can learn a lot quickly with a chart....

Transcript What’s a function? A function is a special type of formula. You can think of a function as a pre-built formula with a specific purpose created to save time. Excel has hundreds of functions. To introduce the idea of a function, we’ll look at the SUM function . The SUM function is one of the most useful and widely used functions in Excel. Its purpose is to add things up....

About This Shortcut This shortcut zooms in or out with a scrolling gesture. To use the shortcut, hold down the Control key, and scroll up or down using a trackpad or mouse wheel. As you scroll, you will see the zoom level changing. About This Shortcut This shortcut will select the entire pivot table, excluding report filters. On Windows, Ctrl Shift * also works to select the entire pivot table

About This Shortcut This shortcut will accept and apply the settings in the dialog box. About This Shortcut This shortcut will check and uncheck checkboxes in a dialog box. First select the checkbox using tab or shift tab.

Explanation At the core, this formula builds a level 1 and level 2 number and concatenates the two numbers together with a period (".") as a separator. The result is a value like “1.1”. The “level 1” number is generated with COUNTA like this: =COUNTA($B$5:B5) Note the range is an expanding reference , so it will expand as it is copied down the column. The “level 2” number is generated with this code:...

Purpose Return value Syntax =BITXOR(number1,number2) number1 - A positive decimal number. number2 - A positive decimal number. Using the BITXOR function The input numbers must be greater than or equal to zero and no larger than 2^48 - 1. Difference Between OR and XOR When a waiter in a breakfast diner asks if you want coffee OR orange juice, they are really asking if you want coffee XOR juice. You can have one or the other, but not both....

Transcript In this video, we’ll look at why boolean operations are important in array formulas. Boolean operations are a key building block in the world of dynamic array formulas. To illustrate, let’s look at some simple order data. Given the data shown, how can we total orders from Texas using an array formula? To start off, I’ll enter an expression that tests the state column in the data to see if it equals “TX”:...

Explanation In this example, the goal is to create a formula that will calculate the number of days between a start date in column B and an end date in column C that overlap a period defined by date 1 and date 2, which are variables that can be easily changed. You can use a formula like this to do things like: Check if one project overlaps another Check if a project overlaps a planned holiday Check if a task overlaps a period of travel Check for overlapping vacation schedules for team planning The solution explained below involves sorting out the two sets of start and end dates and then performing basic date arithmetic....

Explanation The goal is to filter the horizontal data in the range C4:L6 to extract members of the group “fox” and display results with data transposed to a vertical format. For convenience and readability, we have two named ranges to work with: data (C4:L6) and group (C5:L5). The FILTER function can be used to extract data arranged vertically (in rows) or horizontally (in columns). FILTER will return the matching data in the same orientation....

Purpose Return value Syntax =FLOOR.PRECISE(number,[significance]) number - The number that should be rounded. significance - [optional] Multiple to use when rounding. Default is 1. Using the FLOOR.PRECISE function The Excel FLOOR.PRECISE function rounds a number down to a given multiple, where multiple is provided as the significance argument. If the number is already an exact multiple, no rounding occurs and the original number is returned. The FLOOR.PRECISE function takes two arguments , number and significance....

Explanation In the example shown, row 5 is a header row and which contains a series of valid dates, formatted with the custom number format “d”. With a static date in D5, you can use this formula in E5 (copied across) to populate the calendar header in row 5: =D5+7 // header row This makes it easy to set up a conditional formatting rule that compares the date associated with each column with the dates in columns B and C....

Explanation Note: the values in E5:E8 are actual dates, formatted with the custom number format “mmm”. Working from the inside out, the expression: MATCH(TRUE,TEXT(date,"mmyy")=TEXT(E5,"mmyy") uses the TEXT function to generate an array of strings in the format “mmyy”: {"0117";"0117";"0117";"0217";"0217";"0217";"0317";"0317";"0317"} which are compared to a single string based on the value in E5, “0117”. The result is an array of TRUE / FALSE values: {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} This array is fed into the MATCH function as the lookup_array , with a lookup_value of TRUE, and a match_type of zero for exact match....

Explanation In this example, the goal is to determine the original price from a discounted price (sale price) and the percentage discount. For example, given a sale price of $60.00, and a discount of 10%, we want a result of $70.00 for the original price. The discounted price is in column C and the percentage discount is in column D. The general formula for this calculation, where “x” is the original price, is:...

Explanation To use the GETPIVOTDATA function, the field you want to query must be a value field in the pivot table, subtotaled at the right level. In this case, we want a subtotal of the “sales” field, so we provide the name the field in the first argument, and supply a reference to the pivot table in the second: =GETPIVOTDATA("Sales",$B$4) This will give us the grand total. The pivot_table reference can be any cell in the pivot table, but by convention we use the upper left cell....

Transcript In this lesson we’ll look at how to set row heights in Excel. The default row height in Excel is determined by the font size. As you increase or decrease the font size, Excel will adjust the row height to fit. However, you can still adjust the row height manually. Let’s take a look. All rows in Excel have a set height. To check on the current height of a row, just click the row divider at the bottom....

Transcript In this lesson we’ll look at how to change the font color. Let’s take a look. Let’s continue with our menu project and add some font colors. Let’s make the headings one color and the menu items another. Even though we want the headings to be a different color, it will be easiest if we apply one color to the entire worksheet, and then come back and work on the headings separately....