Posts

Let’s take a look. To enter the current date in Excel, use the shortcut control-semicolon. To enter the current time, use the shortcut control-shift-semicolon. If you want to enter both the current date and the current time, type control-semicolon, a space, then type control-shift-semicolon. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

Notice data is sorted in ascending order (A-Z) by default. With this formula in F5, the SORT function outputs the sorted array in F5:H14. Ascending vs. Descending Data is sorted in ascending order (A-Z) by default. This behavior is controlled by an optional third argument, sort_order. The default value for sort_order is 1, so both formulas below return the same result as shown in the example above: To sort in descending (Z-A) order, set sort_order to -1....

where “data” is the named range B5:B14 In the example shown, “data” is the named range B5:B14. In this example, the main challenge is to increment a value for nth. This is done by using an expanding range inside the ROWS function: As the formula is copied down the table, the range expands and the number or rows increases, with supplies an incrementing value. Sort numbers in descending order To sort numbers in descending order, simply replace the SMALL function with the LARGE function: Like SMALL, the LARGE function extracts an “nth” value....

Pros Simple presentation can be red at a glance Can show part to whole changes over time Cons Generally harder to add data labels since there is less white space available Area charts can imply more data than actually available Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....

where data is the named range B5:G14, and headers is the named range B4:G4. The formula sums columns where headers begin with “A” and returns 201. Note: In the current version of Excel you can also use the FILTER function, as explained below. To apply a filter by matching column headers – columns with headers that begin with “A” – we use the LEFT function like this: This expression returns TRUE if a column header begins with “a”, and FALSE if not....

In this video we’ll look how to avoid and handle that situation. Here we have a VLOOKUP example we’ve looked at previously which uses VLOOKUP to retrieve employee information based on an ID. When you’re using VLOOKUP this way you’ll probably run into situations where VLOOKUP can’t find the value you’re trying to look up. The first thing to check is your lookup table. Make sure the table is properly defined and the lookup value is in the left-most column....

where split_table is the named range G7:I11 The commission amount is split between the agent and broker, starting with a 50/50 split, and changing at fixed intervals as shown in the named range split_table (G7:I11). The commission amount is calculated in column C with this formula: The Agent and Broker amounts in columns D and E are calculated like this: Notice these formulas are identical except for the column index....

Note: conditional formatting rules are evaluated relative to the upper left cell in the selection at the time the rule is created, in this case D5. Calculating variance in days The variances in column E are calculated by subtracting the original date from the current date with this formula: The result is the difference in days between the original date and the current date. A positive difference represents a late date (i....

The trick is to first convert the Excel time to a decimal time by multiplying by 24. Then you can multiply by the hourly rate: Note: technically, the parentheses in the formula above are not needed and added for clarity only. Formatting time durations By default, Excel may display time, even time that represents a duration, using AM/PM. For example, if you have a calculated time of 6 hours, Excel may display this as 6:00 AM....

COUNTIF returns 2, since there are two cells in C5:C16 with numbers greater than 90. All test scores are in the range C5:C16 and we want to count scores greater than 90, so we configure COUNTIF like this: COUNTIF returns 2, since there are two scores in C5:C16 that are greater than 90. Notice that criteria is given as a text value in double quotes (""). This is a requirement of the COUNTIF function which is in a group of eight functions that use a special syntax for criteria....

The result is 2, since there are two rows where the state is Texas (“TX”), the amount is greater than 100, and the month is March. COUNTIFS function You would think the COUNTIFS function would be the perfect tool for this job, but if we try to use COUNTIFS, we’ll run into a problem. The first two conditions are straightforward. We can count orders from Texas (“TX”) with amounts greater than $100, like this: COUNTIFS returns 4, since there are 4 orders that meet these conditions....

where data is the named range B5:B15. Note: The COUNTIF function will not count the numbers in this example correctly, as explained below. This problem is related to how Excel handles numbers. Excel can only handle 15 significant digits, and if you enter a number with more than 15 digits in Excel, you will see the trailing digits silently converted to zero. The counting problem mentioned above arises from this limit....

Note: the external workbook must be open for this reference to work. Note the square brackets ([ ]) around workbook name, single quotes (’ ‘) around the worksheet + sheet, and the exclamation mark (!) that follows. To create a reference like this using text, we use concatenation to join values from columns B, C, and D with the required brackets, quotes, and exclamation mark: The result is fed into INDIRECT as ref_text....

AGGREGATE can run a total of 19 functions, and the function to perform is given as a number, which appears as the first argument in the function, function_num. The second argument, options, controls how AGGREGATE handles errors and values in hidden rows. See the table below for all available options. The AGGREGATE function takes four arguments: function_num, options, ref1, and ref2. For the first 13 functions supported, only the first three arguments are required: function_num specifies the operation, options sets various behaviors, and ref1 is the array of values to process....

Example In the example shown, an asset was purchased on June 30, 2019 at an initial cost of $10,000. The end of the first period is December 31, 2019 and the depreciation rate is 20% per year. The salvage value is $1000, and the basis is European 30/360. The formula in F7, copied down the table is: Notice with the exception of the period numbers in column E, the other arguments use absolute references to prevent changes when copying....

AVERAGEA takes multiple arguments in the form of value1, value2, value3, etc. up to 255 total. Arguments can include numbers, cell references, ranges, arrays, and constants. Empty cells are ignored, but zero (0) values are included. Examples To average values in the range A1:A10, including logical the logical values TRUE (1) and FALSE (0) and numbers entered as text, use AVERAGEA like this: One confusing aspect of the AVERAGE function compared to the AVERAGEA function is that both functions will evaluate logicals and numbers entered as text when they are hardcoded as constants in a formula: However, the AVERAGE function will ignore logicals or numbers entered as text when they appear in cell references....

Using the example above, you can get the maximum value from the field “Price” for records where the color is “Red” and quantity is > 2 with these formulas: Criteria options The criteria can include a variety of expressions, including some wildcards. The table below shows some examples: Note: it appears support for wildcards is not as extensive as with other functions like COUNTIFS, SUMIFS, MATCH, etc. For example, the pattern ?...

Note: in Excel 365, you will can see lifting happen in real-time, since multiple results will spill onto the worksheet. In earlier versions, lifting still occurs, but only one result is displayed in the cell that contains the formula. Example The example shown illustrates what happens if you call the LEN function on the range C5:C7, which contains three values. LEN isn’t programmed to handle arrays natively, so LEN is run three times, once for each value in an operation like this: Notice the result is a vertical array with three values, just like the source range....

Pairwise lifting occurs when values in two or more arrays are combined pairwise to produce a different array holding the result of this operation. Example The example shown illustrates what happens if you call the SQRT function like this: Inside SQRT, two vertical arrays are added together with the addition symbol. The values in these arrays are combined pairwise, and result of this operation is a single array with 3 values: The SQRT function does not handle arrays natively, so the function is “lifted” and called three times, one for each value....

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.