Help using a set of discrete cells as a range in a formula

[danielp33]

I have a table that is generally set up like this

In each row, I have the data corresponding to a person and in the columns, it is arranged such that every 2 columns corresponds to pair of numbers.

What I want to be able to do is use formulas to analyze just the x-values or just the y-values in each row. But that requires using a non-contiguous range of cells and I don't know how to do that.

For example, I want to be able to add up the 10 highest values of y for each person (row).  Normally, i would use "=SUM(  LARGE(  B3:G3,  {1,2,3,4,5,6,7,8,9,10}  )  )". But I can't do that in this situation because I don't know how to format a non-contiguous range of cells in such a way that it can be used as an array variable in a formula. How can I just use "C3,E3,G3" as an array?

I've seen that it is possible to do so with named ranges. I can do it that way such that the formula looks like "=SUM(  LARGE(  Range_1,  {1,2,3,4,5,6,7,8,9,10}  )  )", but that's not really a scalable solution for me. I have almost a thousand rows and I'm definitely not manually creating a named range for every row. Especially because I frequently sort and reorder the data in various ways.

SSo How can i use a range of noncontiguous cells as an array parameter in a formula?

[#2 · Accepted solution]

@danielp33 

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!

[#3]

@danielp33 

Perhaps something like this:

=LET(y,CHOOSECOLS(Data,SEQUENCE(,12,2,2)),filtered,FILTER(y,Person=A3),LARGE(filtered,SEQUENCE(,10)))

[#4]

@NikolinoDE 

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.

[#5]

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.