With the introduction of dynamic array formulas, Excel includes 6 brand new functions that directly leverage dynamic array behavior. Each of these functions is covered in more detail later in the course, so this is just a brief demonstration. UNIQUE The UNIQUE function is designed to extract unique values from a set of data. This range contains a list of colors, some of which appear more than once. With UNIQUE, I can quickly build a list unique values....
where “rng” is the named range H4:H10 and contains the values to look for. The double negative will force the TRUE and FALSE values to 1 and 0 respectively. Since SUMPRODUCT receives just one array, it simply adds up the items in the array and returns the result. Logically, any result greater than zero means that at least one value exists in the range. So, the final step is to evaluate the SUMPRODUCT result to see if its greater than zero....
Here we have a set of sales data that spans 2 years, broken down by month. Using named ranges, I’m going to add several formulas to fill in the summary information, based on a name in I8. Generally, the fastest way to create a named range is to use the name box. Just make a selection and type a valid name. To see a list of existing names, use Control + F3....
In the range C6:E6, we count numbers greater than or equal to 5. The second requirement is to have at least 15 volunteer hours total. This requirement is tested with a simple logical expression based on the SUM function: If both the the logical conditions return TRUE, the AND function will return TRUE. IF either returns FALSE, AND will return false. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
With XLOOKUP’s default settings for match_mode (exact) and search_mode (first to last) the formula matches the first record where: account begins with “x” AND region is “east”, and month is NOT April. The first match is the fourth record (row 8) in the example shown. In this example, the required criteria is: account begins with “x” AND region is “east”, and month is NOT April. For each of the three separate criteria above, we use a separate logical expression....
where data is the named range B5:D14. To get the first row used, we use the ROW function together with the MIN function like this: Because data contains more than one row, ROW returns an array of row numbers: This array goes directly to the MIN function, which returns the smallest number: To get the first column, we use the COLUMN function in the same way: Since data contains three rows, COLUMN returns an array with three column numbers: and the MIN function again returns the largest number: Both results are returned directly to the ADDRESS function, which constructs a reference to the cell at row 5, column 2: If you want a relative address instead of an absolute reference, you can supply 4 for the third argument like this:...
where “group” (B5:B14), “region” (C5:C14), and “sales” (D5:D14) are named ranges. In the example shown, we have group values in column F and region values in column G. We use these values directly by using cell references for criteria. The first argument holds the range of values to average: To restrict calculation by group we provide: To restrict calculation by region we use: The result in cell J5 is 105:...
In the example shown, the formula in F5 is: Working from the inside out, VLOOKUP looks up and retrieves a link value from column 2 of the named range “link_table” (B5:C8). The lookup value comes from column E, and VLOOKUP is configured for exact match. The result is fed into HYPERLINK as link_location, and the text in column E is used for friendly_name. HYPERLINK returns a working link. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
The result is the number 1021 formatted as text “1021”. All numbers in column D are formatted as text with the formulas seen in column F. Convert with formatting The TEXT function can be used to convert numbers to text using a given number format. In the example shown, the TEXT function is used to convert each number in column B to a text value using the formula and number shown in column F....
where data is the named range B5:B15 and the numbers in column D are entered as text values. COUNTIF function The COUNTIF function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (,?) for partial matching. You would think you could use the COUNTIF function with the question mark (?) and asterisk () wildcards to count numbers where the third digit is 1 like this: However, COUNTIF will return zero....
where the coordinates of the two points are given in columns B through E. Distance is the square root of the change in x squared plus the change in y squared, where two points are given in the form (x1, y1) and (x2, y2). The distance formula is an example of the Pythagorean Theorem applied, where the change in x and the change in y correspond to the two sides of a right triangle, and the hypotenuse is the distance being computed....
For example: In the example shown, the formula in D6, copied down, is: Notes: 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.
The LARGE function takes two arguments, array and k. Array is an array or range of numeric values. The argument k represents position or rank. For example, to return the largest value in array, provide 1 for k. To return the fifth largest value in array, provide 5 for k. To get nth smallest values, see the SMALL function. Examples In the formula below, the LARGE function returns the third largest value in a list of five numbers provided in an array constant: Note values do not need to be sorted....
Example #1 - not green or red In the example shown, the formula in C5, copied down, is: The literal translation of this formula is “NOT green or red”. At each row, the formula returns TRUE if the color in column B is not green or red, and FALSE if the color is green or red. Example #2 - Not blank A common use case for the NOT function is to reverse the behavior of another function....
REPLACE function takes four separate arguments. The first argument, old_text, is the text string to be processed. The second argument, start_num is the numeric position of the text to replace. The third argument, num_chars, is the number of characters that should be replaced. The last argument, new_text, is the text to use for the replacement. Examples To replace the “C” in the path below with a “D”: To replace 3 characters starting at the 4th character: You can use REPLACE to remove text by specifying an empty string ("") for new_text....
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 + Shift + : stopped working to insert a time. The current shortcut on a Mac is Command + ; 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.
We don’t know an equivalent shortcut on the Mac. However, Control + Command + F will toggle full screen mode for almost any window although it won’t toggle the ribbon or status bar in Excel. 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....
Example In the example shown, there are 11 numeric values in two groups, A and B. The count of values in each group are the inverse of each other. There are four 1’s in group A, four 5’s in group B, etc. The formula in cell F12 returns a positive skew: The formula in J12 returns a negative skew: Excel also contains the SKEW.P function, which measures population skewness. The difference in calculation is related to an adjustment (n-1) made when data represents a sample versus the entire population....
