Forum Discussion
Help using a set of discrete cells as a range in a formula
In Excel 365, you can use an array formula to analyze a non-contiguous range of cells.
An array formula is a formula that can perform multiple calculations on one or more items in an array. You can think of an array as a row or column of values, or a combination of rows and columns of values.
Array formulas can return either multiple results or a single result.
One way to use a non-contiguous range of cells as an array parameter in a formula is by using the CHOOSE function.
The CHOOSE function returns a value from a list of values based on a given index number.
You can use it to create an array of non-contiguous cells by listing the cells as arguments in the CHOOSE function.
For example, if you want to sum the values in cells C3, E3, and G3, you could use the following formula: =SUM(CHOOSE({1,2,3},C3,E3,G3)).
This formula creates an array of three values (C3, E3, and G3) and then sums those values.
Hope this helps!
Perhaps something like this:
=LET(y,CHOOSECOLS(Data,SEQUENCE(,12,2,2)),filtered,FILTER(y,Person=A3),LARGE(filtered,SEQUENCE(,10)))