What are Dynamic Array formulas?
Dynamic Arrays are the biggest change to the Excel formula engine in years. In this video, I'll explain the basic idea of dynamic array formulas...
Dave Bruns
Basic XLOOKUP example
In this video, we’ll set up the XLOOKUP function with a basic example. Matching on City name, we'll retrieve Country and Population...
FILTER function with two criteria
In this video, we’ll set up the FILTER function with two criteria.
FILTER function basic example
In this video, we’ll look at a basic example of filtering with the FILTER function.
Basic SORTBY function example
In this video, we’ll look at a basic example of sorting with the SORTBY function. With the SORTBY function, you specify the sort order in pairs of inputs. Each pair has an argument for a range and an argument for the sort order.
Basic SORT function example
In this video, we’ll look at a basic example of sorting with the SORT function. Sorting with formulas is a traditionally hard problem in Excel new dynamic array formulas make much easier.
...
Max value with variable column
Note: If you are new to INDEX and MATCH, see: How to use INDEX and MATCH
In a standard configuration, the INDEX function retrieves a value at a given row and column. For example, to get the value at row 2 and column 3 in a given range:
...Read more
Filter exclude blank values
The FILTER function is designed to extract data that matches one or more criteria. In this case, we want to apply criteria that requires all three columns in the source data (Name, Group, and Room) to have data. In other words, if a row is missing any of...Read more
BMI calculation formula
This example shows one way to calculate BMI (Body Mass Index) in Excel. The standard BMI formula is:
BMI = weight (kg) / height (m)2
The approach used here is to first convert height in inches and feet to meters, and weight in pounds to kilograms, then...Read more
COUNTIFS with variable range
In the example shown, the formula in B11 is:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")
Working from the inside out, the work of setting up a variable range is done by the OFFSET function here:
...Read more