Explanation The goal is to demonstrate how other formulas and functions can be nested inside the IF function . The example is a simple quantity-based discount formula. IF function The IF function evaluates a logical test and returns one value if the result is TRUE, and a different value if the result is FALSE. The generic syntax for IF looks like this: =IF(logical_test,value_if_true,value_if_false) For example, if cell A1 contains the value 75, then you could use IF to return “Pass” or “Fail” like this:...

Explanation In this example, the goal is to lookup data to the left of an ID that appears as the last column in the table. In other words, we need to locate a match in column E, then retrieve a value from a column to the left. This is one of those problems that is difficult with VLOOKUP , but easy with INDEX and MATCH or XLOOKUP . Both options are explained below....

Explanation In this example, the goal is to generate a list of “nth weekdays of the month” with a formula. For example, the formula should be able to create a list of any of the following: 2nd Tuesdays of the month 1st Fridays of the month 3rd Mondays of the month This is a somewhat challenging problem in Excel, because there is no built-in function to help you find, say, the 2nd Tuesday of a given month....

Explanation The core of this formula is INDEX, which is retrieving a value from the named range “amount” (B5:B13): =INDEX(amount,row_num) where row_num is worked out with the MATCH function and some boolean logic : MATCH(1,($F5=location)*(G$4=date),0) In this snippet, the location in F5 is compared with all locations, and the date in G4 is compared with all dates. The result in each case is an array of TRUE and FALSE values. When these arrays are multiplied together, the math operation coerces the TRUE and FALSE values to one’s and zeros, so that the lookup array going into MATCH looks like this:...

Explanation In this example, the goal is to get all names in a given group into separate rows grouped by column, as seen in the worksheet above. This is sometimes referred to as a “pivot” operation. The idea is to restructure the data into multiple columns where each column holds the names that belong to a group. The article below explains two options: (1) a modern approach based on the FILTER function and (2) a legacy approach based on INDEX and SMALL for older versions of Excel without the FILTER function....

Purpose Return value Syntax =NORM.INV(probability,mean,standard_dev) probability - The probability of an event occurring below a threshold. mean - The mean of the distribution. standard_dev - The standard deviation of the distribution. Using the NORM.INV function The NORM.INV function returns the inverse of the normal cumulative distribution. Given the probability of an event occurring below a threshold value, the function returns the threshold value associated with the probability. For example, NORM.INV(0.5, 3, 2) returns 3 since the probability of an event occurring below the mean of the distribution is 0....

Purpose Return value Syntax =NORM.S.INV(probability) probability - A probability corresponding to the standard normal distribution. Using the NORM.S.INV function The NORM.S.INV function returns the inverse of the standard normal cumulative distribution. Given the probability of an event occurring below a threshold value, the function returns the z-score of the threshold. For example, NORM.S.INV(0.8413) returns 1 since the probability of an event occurring below 1 standard deviation from the mean is 0....

Power Query is a tool in Excel that allows you to import data from a wide variety of sources, and manipulate that data to meet your needs. For example, you can: Import a CSV file on your computer Import a table from a web page Import data from an online database In addition to importing data to Excel, Power Query is designed to “transform” data. You can easily do things like remove columns or rows, rename and reorder columns, split columns, add new columns, fix date problems, join tables, and much more....

Purpose Return value Syntax =STDEV.S(number1,[number2],...) number1 - First number or reference in the sample. number2 - [optional] Second number or reference. Using the STDEV.S function The STDEV.S function calculates the standard deviation in a sample set of data. Standard deviation is a measure of how much variance there is in a set of numbers compared to the average (mean) of the numbers. The STDEV.S function is meant to estimate standard deviation in a sample....

Explanation In this example, the goal is to sum the values in F5:F16 when the Color in C5:C16 is “Red” and the State in D5:D16 is “TX”. This is an example of a conditional sum with multiple criteria and the SUMIFS function is the easiest way to solve this problem. SUMIFS function The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria ....

The EOMONTH function is one of those little gems in Excel that can save you a lot of trouble. It’s a simple function that does just one thing: given a date, it returns the last day of a month. This may seem a little silly — how hard can it be to figure out the last day of a month? — but it’s actually a bit tricky, since each month has a different numbers of days, and February changes in leap years....

Explanation In this example the goal is to “unwrap” a column of values into separate fields. The values are spaced evenly apart, and the result should be all related values on one row, where each column corresponds to a field of information. The input data appears in column B. Each “record” in the data has three values: Name, Street, and City/State/Zip (combined). New records start every 4th row, and records are separated by an empty cell, which can be thought of as a fourth field that will be ignored....

Explanation In this example, the goal is to look up amounts for 1000 invoice numbers in a table that contains 1 million invoices. The catch is that not all of the 1000 invoice numbers exist in the source data. In fact, most of the invoice numbers do not appear in column B . This means we need to take care to configure XLOOKUP to use an exact match , and exact match lookups on large data sets can be painfully slow....

Explanation For this example, we want to calculate the principal portion for payment 1 of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up PPMT like this: rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: =C6/12 per - the period we want to work with. Supplied as 1 since we are interested in the principal amount of the first payment....

Purpose Return value Syntax =COLUMN([reference]) reference - [optional] A reference to a cell or range of cells. Using the COLUMN function The COLUMN function returns the column number of a reference. For example, COLUMN(C5) returns 3, since C is the third column in the spreadsheet. COLUMN takes just one argument, called reference , which can be empty, a cell reference, or a range. When no reference is provided, COLUMN returns the column number of the cell which contains the formula....

Explanation This is a complex formula that uses FREQUENCY to count numeric values that are derived with the MATCH function. Working from the inside out, the MATCH function is used to get the position of each value that appears in the data: MATCH(B5:B11,B5:B11,0) The result from MATCH is an array like this: {1;1;3;1;1;6;7} Because MATCH always returns the position of the first match, values that appear more than once in the data return the same position....

Above: Using the FILTER function to return multiple matches on “red”. In fall 2018, Microsoft announced a new feature in Excel called “dynamic array formulas”. In a nutshell, this feature allows formulas to return multiple results to a range of cells on the worksheet based on a single formula entered in one cell. This behavior is called " spilling " and results appear in a " spill range “. All results are dynamic – if source content changes, the results dynamically update to stay in sync....

Explanation Dates in Excel are just serial numbers , formatted to display as dates. This means you can perform math operations on dates to calculate days in the future or past. In the example shown, the date in the named range “start” is provided by the TODAY function: =TODAY() //returns current date The formula in B5 begins with the start date, and increments the date by one using an expanding range inside the ROWS function:...

Transcript In this video, we’ll look at a couple ways to use the FILTER function to extract data between dates. In this worksheet, we have sample order data that contains a date field. Let’s set up the FILTER function to extract data between two dates. Before we begin, I want to remind you that Excel dates are just large serial numbers, as you can see if I temporarily apply general number formatting....

Explanation The goal is to return the full address of a range or named range as text. The purpose of this formula is informational, not functional. It provides a way to inspect the current range for a named range, an Excel Table, or even the result of a formula. The core of the solutions explained below is the ADDRESS function , which returns a cell address based on a given row and column....