Transcript In this video, we’ll look at how to count the unique values returned by the UNIQUE function . Before dynamic array formulas, counting unique values in Excel involved complex array formulas, especially if you needed to count values based on one or more conditions. Now that dynamic array formulas, and especially the UNIQUE function, are part of Excel, this is all much easier. On this first worksheet, this list of colors contains duplicates....
Transcript When you’re using VLOOKUP for approximate matches, you’re not as likely to run into the problem of values not being found. However, you still need to understand how approximate matches work. Here’s the commission example we looked at earlier which uses VLOOKUP to find approximate matches in a named range called “commission table.” Let’s take a look at how VLOOKUP handles approximate matches in more detail. First, when you’re using VLOOKUP for approximate matches, it’s likely that the lookup value won’t be in the table....
Purpose Return value Syntax =IMCOSH(complex_num) complex_num - The number to get the hyperbolic cosine of. Using the IMCOSH function The Excel IMCOSH function returns the hyperbolic cosine of a complex number. Given “1+1.5707963267949i” as input, the function returns “-5.4E-15+1.175201i” as output. =IMCOSH(COMPLEX(1, PI()/2)) // returns -5.4E-15+1.175201i When the function’s output is plotted over the complex plane, the real output along the real axis traces the shape of the COSH function....
Purpose Return value Syntax =ISLOGICAL(value) value - The value to test as logical. Using the ISLOGICAL function The ISLOGICAL function returns TRUE when a cell contains the logical values TRUE or FALSE, and returns FALSE for cells that contain any other value, including empty cells. The ISLOGICAL function takes one argument, value , which can be a cell reference, a formula, or a hardcoded value. When value is TRUE or FALSE, the ISLOGICAL function will return TRUE....
Explanation In this example, the goal is to get the minimum quiz score (i.e. the lowest quiz score) for each person listed in column B from the five quiz scores that appear in columns C through G. This is a job for the MIN function or the SMALL function, as explained below. MIN function The MIN function accepts one or more arguments , which can be a mix of constants, cell references, and ranges ....
What is the 4% retirement rule? How the 4% rule works What we need to model in Excel The model in Excel Traditional approach (Sheet1) Hybrid approach (Sheet2) Single formula approach (Sheet3) Summary and conclusion Functions used in the workbook Instructions What is the 4% retirement rule? If you look into retirement planning, you’ll probably run into the 4% retirement rule, one of the most well-known (and frequently cited) guidelines for retirement spending....
A multi-cell array formula is an array formula that returns multiple results to more than one cell at the same time. In the example shown, the formula in B3:B12 is: {=ROW(1:10)} Here, the ROW function returns an array with 10 items: {1;2;3;4;5;6;7;8;9;10} and each item in this array is displayed in a different cell. Steps to enter a multi-cell array formula To enter a multi-cell array formula, follow these steps:...
About This Shortcut This shortcut will display the Group Dialog Box as long as no whole rows or columns are selected. About This Shortcut This shortcut will display the Ungroup Dialog Box as long as no whole rows or columns are selected.
Pivot tables are an easy way to quickly sum unique values in a data set, and can easily be adapted to perform a two-way sum. In the example shown above, a pivot table is used to sum The Qty field for unique combinations of City and Size, based on data in the range B5:D17, defined as an Excel Table . Fields The pivot table shown is based on three fields: City, Size, and Qty....
Pivot tables are an easy way to quickly average unique values in a data set, and can easily be adapted to perform a two-way average. In the example shown above, a pivot table is used to average Ratings for unique combinations of Age and Gender, based on data in the range B5:D16, defined as an Excel Table . Fields The pivot table shown is based on three fields: Age, Gender, and Rating....
Explanation This formula uses the REPLACE function to replace the first character in a cell with an empty string (""). The arguments for REPLACE are configured as follows: old_text is the original value from column B start_num is hardcoded as the number 1 num_chars comes from column C new_text is entered as an empty string ("") The behavior or REPLACE is automatic. With these inputs, the REPLACE function replaces the first character in B5 with an empty string and returns the result....
Explanation The TRIM function is fully automatic. It removes both leading and trailing spaces from text strings, and also “normalizes” multiple spaces between words to one space character only. All you need to do is supply a reference to a cell. TRIM with CLEAN If you also need to remove line breaks from cells, you can add the CLEAN function like so: =TRIM(CLEAN(text)) The CLEAN function removes a range of non-printing characters, including line breaks, and returns “cleaned” text....
Explanation In this example, the goal is to repeat a sequence of numbers. This is a useful way to create repeating sequences of numbers by itself. In addition, this formula is a building block to the more general formula here , which can repeat ranges and arbitrary values that are not sequential numbers. Ingredients The formulas on this page are based on two functions: the SEQUENCE function and the MOD function....
Explanation This formula uses the named range key (C4:G4) for convenience only. Without the named range, you’ll want to use an absolute reference so the formula can be copied. In cell I7, we have this formula: =SUM(--(C7:G7=key)) working from the inside-out, this expression is evaluated first: C7:G7=key // compare answers to key The result is an array of TRUE FALSE values like this: {TRUE,TRUE,TRUE,FALSE,TRUE} TRUE values indicate a correct answer, FALSE values indicate an incorrect answer....
Purpose Return value Syntax =SEC(number) number - The angle in radians for which you want the secant. Using the SEC function The SEC function returns the secant of an angle provided in radians . In geometric terms, the secant of a right-triangle’s angle is equal to the ratio of the length of its hypotenuse over the length of its adjacent side. For example, the secant of PI()/6 (30°) returns the ratio 1....
Explanation The goal in this example is to create a self-contained lookup formula to assign a grade to the score in cell E7, based on the table in B6:C10. However, instead of providing B6:B10 as a reference for the table_array argument, the table is provided as a constant . Normally, the second argument for VLOOKUP is provided as a range like B6:C10: =VLOOKUP(E7,B6:C10,2,TRUE) When the formula is evaluated, the reference B6:C10 is converted internally to a two-dimensional array like this:...
Transcript In this video, we’ll look at shortcuts for working with Excel functions. Excel contains over 400 built-in functions, and a number of related shortcuts. To start off, there’s a shortcut for the Autosum feature, which automatically enters the SUM function. Select a range that includes a blank cell, then use Alt + = on Windows, Command + Shift + T on a Mac. You can use this for both rows and columns....
Explanation In this example, the goal is to sum Amounts in column C by state using the two-letter codes in column E. Note the states are abbreviated, “CA” is California, “FL” is Florida, “TX” is Texas, and “WA” is Washington. The challenge in this case is that the state abbreviations are embedded in a text string. This means we need to construct criteria that performs a “contains” type match. To solve this problem, you can use either the SUMIF function or the SUMIFS function with a wildcard ....
The sunburst chart is a built-in chart type in Excel 2016+. A sunburst chart is used to display hierarchical data in a circular format where each level of the hierarchy is represented as a ring. Top level categories make up the inner ring, and sub-categories are plotted as outer rings. Segments in each ring are sized proportionally using a value series. Like a treemap chart, the sunburst chart can be used to compare relative sizes....
Quick Links Quick Start Examples Troubleshooting Training If you’ve ever applied conditional formatting with your own formula, you know the hardest part is making sure the formula actually works. The problem is that the formula area in a conditional formatting rule isn’t very friendly. You don’t get highlighted cell references, you don’t get function autocomplete…heck….you don’t even get screen tips. As a result, it’s hard to “see” if a formula is going to work until after you save the rule....
