Apr 09 2023 12:37 AM - edited Apr 09 2023 12:40 AM
Apr 10 2023 11:44 PM
SolutionIn 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!
Apr 11 2023 06:15 AM
Perhaps something like this:
=LET(y,CHOOSECOLS(Data,SEQUENCE(,12,2,2)),filtered,FILTER(y,Person=A3),LARGE(filtered,SEQUENCE(,10)))
Apr 11 2023 06:25 PM
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.
Apr 11 2023 06:37 PM