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!
4 Replies
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.
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!
Thank you, this is exactly what I was looking for. Only, I did want to add up only the 15 largest numbers, so I had to nest the CHOOSE into a LARGE set up the same way. The expression I that worked and am actually using ended up like this:
SUM(LARGE(CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34},D3,F3,H3,J3,L3,N3,P3,T3,AA3,AC3,AE3,AG3,AI3,AK3,AM3,AO3,AQ3,AS3,AU3,AW3,AY3,BE3,BG3,BI3,BK3,BM3,BO3,BQ3,BS3,BU3,BW3,BY3,CA3,CC3),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))
It's a bit tedious to set up the first time because of all those terms, but it is exactly what I wanted. I can copy-paste it or drag it to any cells I want and it survives sorts and insertions/deletions. Thank you very much.
