In this example, the goal is to map the numbers 1-6 to the arbitrary values seen in the table below. For example:
In this example, we have comma-separated text in column B. The goal is to split the text in column B into columns D through G while at the same time converting the numbers to true numeric values. The challenge is that TEXTSPLIT always returns text, so we need a way to convert the numbers...Read more
To list and count the most frequently occurring values in a set of data, you can use a pivot table. In the example shown, the pivot table displays the top Wimbledon men's singles champions...Read more
The YEAR function extracts the year from a valid Excel date. For example:
=YEAR("15-Jun-2021") // returns 2021
In this case, we are giving YEAR and array of dates in...Read more
In geometry, the area enclosed by a circle with radius (r) is defined by the following formula: πr2
The Greek letter π ("pi") represents the ratio of the circumference of a circle to its diameter. In Excel, π is represented in a formula with the...Read more
In this example, the goal is to count the number of cells in B5:B15 that contain a given number of characters, where the number of characters n is provided as a variable in cell E4.
One way to solve this problem is to use the ...Read more
In this example, we have a table that contains 6 columns of codes, and each row of codes belongs to a group in column B. The goal is to lookup any code in C5:H15, and return the name of the group the code belongs to. The challenge is that the code may be in any one of the six columns, and...Read more
An array constant is a hard-coded set of values provided in an Excel formula. Array constants appear in curly braces {} like this:
{"red","blue","green"}
Array constants are often used in array formulas to create or manipulate several...Read more
The Excel PERCENTOF function is designed to calculate the percentage of a subset of data with respect to all data. The output from PERCENTOF is a decimal number that can be formatted with Excel's percentage number format....Read more
The length of a line can be calculated with the distance formula, which looks like this:
...Read more
In this example, the goal is to count odd numbers in the range B5:B15, which is named data. This can be done with the SUMPRODUCT function together with the ISODD function. Instead of ISODD, the MOD function can also be used. Both approaches are explained below.
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an elegant and versatile function, which this example illustrates nicely.
In this example, SUMPRODUCT is...Read more
The Excel OR function is a logical function used to test multiple conditions at the same time. OR returns TRUE if any condition is TRUE. If all conditions are FALSE, the OR function returns FALSE. The OR function is often used with the IF function and can be combined...Read more
Excel's formula engine has some quirks that you should be aware of. One of these quirks is that Excel will treat a text value as larger than a number by default. For example:
=90>100 // returns FALSE
="A">100 // returns TRUE
The second...Read more
All the hard work is done by the COUNTIF function, which is configured to count the values in the named range "substrings" that appear the named range "rng" with like this:
COUNTIF(rng,"*"&substrings&"*"))
...Read more
In geometry, the area enclosed by a triangle is defined by this formula:
...Read more
This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the COUNTA function as an array like this:
...Read more
The Excel ISBLANK function returns TRUE when a cell is empty, and FALSE when a cell is not empty. For example, if A1 contains "apple", ISBLANK(A1) returns FALSE.Read more
First, for context, it's important to note that you can use COUNTIFS with a regular structured reference like this:
=COUNTIFS(Table1[Swim],"x")
This is a much simpler formula, but you can't copy it down column H, because the column...Read more
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....Read more
In this example, the goal is to build a formula that will return TRUE if a given cell contains all values that appear in a given range. We could build a formula that uses nested IF statements to check for each...Read more