Finding the group of three cells with the highest average (Need help to optimize the formula)-CodePudding

The goal is ( it's simpler than it sounds ) :

To find a group of three cells (say, A1, A2, A3 or A20, A21, A22) that has a highest average in the column, and get the position of the cell in the middle of that group.

I already have formulaS that allow me to find the result. However, I need to do it in one line without using another column for calculations.

I use the column beside to find the group of cells with highest AVG using this formula:

=IF(AND(A1>AVERAGE(A1:A51), A2>AVERAGE(A1:A51), A3>AVERAGE(A1:A51)), SUM(A1:A3), 0)

Then, in the cell below the column for calculations, I'm looking for the row with the highest value:

=MATCH(MAX(C1:C51),C1:C51,0)

Is there a way to combine these formulas in one line, so all calculations can be done in one cell?

You can check this google sheet to see how it was done:

CodePudding user response：

In Office 365 you could use

=LET(maxaverage,BYROW(A1:A51,LAMBDA(r,AVERAGE(OFFSET(r,,,3)))),
XMATCH(MAX(maxaverage),maxaverage) 1)

The BYROW function used in combination with LAMBDA makes it possible to make a calculation row by row over a range.

Since you want to calculate the average of 3 cells in a row, I used OFFSET to loop the average through given range with the height offset of 3.

So the first average will be the first cell in the range including the following 2 cells. The second references the second cell in the range including the following 2, etc.

In the end that results in a spill range (an array) of all the averages. I used LET to name that calculation maxaverage.

I could then reference that word in the function instead of recalculating it.

I then used MAX to get the maximum value in maxaverage and MATCH to get the position of the max value from maxaverage within maxaverage. That's the top cell of the 3, so I added 1 to get the middle position.

CodePudding user response：

in google sheets try:

=INDEX(SORT({ROW(A1:A51) 1, BYROW(IF((
 QUERY({{"";"";A1:A51}, {"";A1:A51;""}, {A1:A51;"";""}}, 
 "offset 2", )>AVERAGE(A1:A51))={TRUE, TRUE, TRUE}, 
 QUERY({{"";"";A1:A51}, {"";A1:A51;""}, {A1:A51;"";""}}, 
 "offset 2", ), ), LAMBDA(x, SUM(x)))}, 2, ), 1, 1)