LARGE Function non array

[StevenEdmonton]

Does anybody know how to use the LARGE function without having to use an array. I have 4 columns that are not next to each other and I need to find the 3 largest value out of the 4 and then take the average value of the 3. Normally you would use the LARGE function but the columns need to be next to each other for it to work, but my columns are not next to each other. Does anybody have any idea how to solve this issue. For example my columns I need to find the 3 largest values are in column C, G, K, Y.

Thanks, Steven

 

[pgc01]

I have 4 columns that are not next to each other and I need to find the 3 largest value out of the 4 and then take the average value of the 3.

Normally you would use the LARGE function but the columns need to be next to each other for it to work, but my columns are not next to each other.

Does anybody have any idea how to solve this issue. For example my columns I need to find the 3 largest values are in column C, G, K, Y.

Hi Steven
Welcome to the board

That is not true, the columns do not need to be next to each other.

Try, for the average of the 3 largest values in C2, G2, K2, Y2:

=AVERAGE(LARGE((C2, G2, K2, Y2),{1,2,3}))

 

[Scott Huish]

That uses an array however.

=(C2+G2+K2+Y2-MIN(C2,G2,K2,Y2))/3

 

[pgc01]

That uses an array however.

Hi, Hotpepper

You are right, I interpreted it as not using an array formula (CSE), as some programs "compatible" with excel do not like them.

Even if I interpreted right, however, your formula is simpler and will surely work everywhere!

 

[StevenEdmonton]

Thank you so much. I like the approach from HOTPEPPER. Its so simple and does not use an array.

Thanks again, Steven