Posts

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....

Excel Advanced Filter is one of the most underrated and under-utilized features that I have come across. If you work with Excel, I am sure you have used (or at least heard about the regular excel filter). It quickly filters a data set based on selection, specified text, number or other such criteria. In this guide, I will show you some cool stuff you can do using the Excel advanced filter....

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....

You would notice that Excel will autocorrect that misspelled word to Drink (as shown below). Somehow, Excel knew that this is not the correct spelling and autocorrected it to the right one. Now, it won’t autocorrect all the misspelled words. Just a few! For example, try the word ‘dirnk’. It would not be auto-corrected. The reason some words are autocorrected and others aren’t is because there is already a fix list of words that are prefilled in Excel to autocorrect....

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.

Note: In Mac 2016, Control - also works (same as Windows). 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.

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.

Note: in Excel 2016, this shortcut doesn’t appear to work if slicers are on the worksheet. 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.

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.

In the example shown, the formula in D5 is: The SORT function returns 10 sorted results. Even though the formula is entered just once in cell D5, all 10 values “spill” into the range D5:D14 automatically. The range of results returned by a formula that spills is called a spill range. When something on the worksheet blocks a spilled array formulas, it will return a #SPILL! error. Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

SUMXMY2 takes two arguments, array_x and array_y. Array_x is the first range or array or range of numbers, and array_y is the second range or array of numbers. Both arguments can be provided as an array constant or as a range. Examples In the example shown above, the formula in E5 is: which returns 28 as a result. Equation The equation used to calculate the sum of squares is: This formula can be created manually in Excel with the exponentiation operator (^) like this: With the example as shown, the formula below will return the same result as SUMX2MY2:...

It can also be used to quickly calculate the time that had elapsed between today and the date when an event occurred. For example, if you were born on 10 December 1987, you can calculate your age using this formula =TODAY()-DATE(1987,12,10). What it Returns It returns a serial number that represents the current date. Syntax =TODAY() Input Arguments TODAY function does not take any input arguments. It is used with empty parenthesis....