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)))- This does also work, but it's not really the best solution for my table in particular. I have the equivalent of subtotal columns for each quarter in the middle of the table. Those extra columns break the sequence since it looks like you are incrementing by 2 every time. With my limited experience, It's not clear to me how you would adjust that sequence to compensate.
