The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by...
Excel doesn't have a way to cast the letters in a text string to an array directly in a formula. As a workaround, this formula uses the MID function, with help from the ROW and INDIRECT functions to achieve the same...
The SEQUENCE function is a dynamic array function that can generate multiple results. When used by itself on the worksheet, SEQUENCE outputs an array of results that "spill" onto the worksheet in a "spill range".
In...
The FILTER function, new in Excel 365, is can help simplify some some tricky formula problems.
In this example, the goal is to sum the first 3 scores for both Jake and Hailey, based on the order they appear in the...
The FILTER function is designed to filter and extract information based on logical criteria. In this example, the goal is to extract every 3rd record from the data shown, but there no row number information in the data...
The EDATE function moves forward or backwards in time in one-month increments from a given start date. The SEQUENCE function is a dynamic array function that can generate multiple results that "spill" onto the...
At the core, this formula uses the WEEKDAY function to test a number of dates to see if they land on a given day of week (dow) and the SUMPRODUCT function to tally up the total.
When given a date, WEEKDAY simply...
At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called "names" which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this...
The SEQUENCE function is a dynamic array function that can generate multiple results. Like other dynamic array functions, SEQUENCE outputs an array of results that "spill" onto the worksheet in a "spill range"....
The SEQUENCE function is a dynamic array function that can generate multiple results. When used by itself on the worksheet, SEQUENCE outputs an array of results that "spill" onto the worksheet in a "spill range"....
Working from the inside out, we use the SEQUENCE function to construct a row number value for INDEX like this:
SEQUENCE(3,1,1,1)
We are asking SEQUENCE for an array of 3 rows x 1 column, starting at 1, with a step...
At the core, this formula uses the MID function to extract each character of a text string in reverse order. The starting character is given as a list of numbers in descending order hardcoded as array constant:
MID(B5...