where “data” is an Excel Table in B5:E15. The result is $30.00, the price of a Large Red Hoodie. This is an array formula, and must be entered with control + shift + enter, except in Excel 365. The VLOOKUP function does not handle multiple criteria natively. Normally VLOOKUP looks through the leftmost column in a table for a match, and returns a value from a given column in a matching row....

Let’s take a look. Here we have a worksheet that contains property listings. Let’s quickly build a pivot table without using a table. We’ll select a cell in the data, choose Pivot Table from the Insert tab on the ribbon, and accept default settings. As you can see, we get a pivot table on a new worksheet. Let’s add the Address field to get a count of all properties, then add the Status field as a Row Label....

Because the lookup result in cell D9 is empty, the final result is an empty string (""). By contrast, a standard XLOOKUP formula in cell H5 returns nothing, which displays as “0-Jan-00”. In this example, the goal is to trap an empty lookup result from the XLOOKUP function and display the result as an empty cell. For example, in the worksheet shown, the formula in cell H5 is: Because H5 is formatted as a date, and because the result comes from cell D9 (which is empty) the result from XLOOKUP behaves like zero and displays as “0-Jan-00”....

In Boolean algebra, there are only two possible results for a math operation: 1 or 0, which, as we know, correspond to the logical values TRUE and FALSE. AND logic corresponds to multiplication. Anything multiplied by 0 is 0, and anything multiplied by 1 remains unchanged. If I multiply A times B, you can see how this works. To get a result of 1, both A and B must be 1....

rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: per - the period we want to work with. Supplied as 1 since we are interested in the the principal amount of the first payment. pv - The present value, or total value of all payments now. In the case of a loan, this is input as a negative value by adding a negative sign in front of C5 to supply -5000....

Both of these functions let you join (concatenate) text in different cells together. TEXTJOIN lets you join values with a delimiter of your choice, and has an option to ignore empty values. CONCAT simply mashes all values together without options. What’s nice about both of these functions is that they can handle cell ranges. That means you can do things like this: Maybe you’re old enough to recognize that number?...

Which converts the text value in B5 “29/02/16” into a proper Excel date. The year value is extracted with with the RIGHT function: RIGHT gets the right-most 2 characters from the original value. The number 2000 added to the result to create a valid year. This number goes into DATE as the year argument. The month value is extracted with: MID retrieves characters 4-5. The result goes into DATE as the month argument....

COUNTIF returns 9, since there are nine cells in D5:D16 that are not equal to “red”. Not equal to In Excel, the operator for not equal to is “<>”. For example: COUNTIF function The COUNTIF function counts the number of cells in a range that meet supplied criteria: To use the not equal to operator (<>) in COUNTIF, it must be enclosed in double quotes like this: This is a requirement of COUNTIF, which is in a group of eight functions that share this syntax....

where “keywords” is the named range E5:E9. The core of this formula is the ISNUMBER + SEARCH approach to finding text in a cell, which is explained in more detail here. In this case, we are looking in each cell for all words in the named range “keywords” (E5:E9). We do this by passing the range into SEARCH as the find_text argument. Because we pass in an array of 5 items: we get an array of 5 items back as a result: Numbers correspond to matches, and the #VALUE!...

In the example shown, the formula in D5 is: First, the LEFT function is used to get the first letter from the first name in column C. Usually, the LEFT function gets a “num_chars” value for the second argument, but the argument is optional and defaults to 1 if omitted. The first letter of the first name is then joined to the last name using the concatenation operator (&), and the result is wrapped in the LOWER function, which forces all text to lower case....

To work correctly, many (but not all) array formulas need to be entered with control + shift + enter. When you enter with control + shift + enter, you’ll see the formula wrapped in curly braces {} in the formula bar. Do not enter curly braces manually, or the formula won’t work. What is an Array? An array is a collection of more than one item. Arrays in Excel appear inside curly brackets....

For example, the screen above shows two groups that contain five values each. Three values match and two are different. The formula used to count matching values is: Working from the inside-out, the B5:B9 is compared to C5:C9 in a simple expression that creates an array of five TRUE FALSE values: We want to count matches (TRUE values) but the SUMPRODUCT function will ignore non-numeric values, so we use a double unary to change the TRUE FALSE values to ones and zeros....

Where “data” is a dynamic named range corresponding to B5:B13. The formula used to create this dynamic named range is based on the OFFSET function: See detailed explanation here. The primary advantage of a dynamic named range is that the reference responds to changes. If values are removed from the bottom of the list, the range contracts. If values are added to the bottom of the list, the range expands....

INDIRECT takes two arguments, ref_text and a1. Ref_text is the text string to evaluate as a reference. A1 indicates the reference style for the incoming text value. When a1 is TRUE (the default value), the style is “A1”. When a1 is FALSE, the style is “R1C1”. For example: The purpose of INDIRECT may at first seem baffling (i.e. Why use text when you can just provide a proper reference?) but there are many situations where the ability to create a reference from text is useful, including:...

On the Mac, this shortcut works directly on the worksheet, and adds a top border to each cell in the selection. 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.

No Mac equivalent that we know of. 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.

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: STDEV.P replaces the STDEVP function, with identical behavior. Although STDEVP still exists for backwards compatibility, Microsoft recommends that people use the newer STDEV.P function instead. Standard Deviation functions in Excel The table below summarizes the standard deviation functions provided by Excel. Notes: STDEV.P calculates standard deviation using the “n” method, ignoring logical values and text. STDEV.P assumes data is an entire population. When data is a sample only, use the STDEV....