Using average in array formula

I have the following table structure

Name Role rank 1 rank 2 rank 3 rank 4 rank 5 rank 6 Average
Jon Admin 5 6 7 8 8 9
Tomas Accountant 3 2 2 9 3 3
Ellie Admin 2 9 7 3 9 1
Sam Sales 4 7 3 9 1 8

The actual table has circa 2k rows, and has a lot more columns, before I was using the following formula then dragging down.

=IF(B2 = "Admin", AVERAGE(C2,D2,F2), AVERAGE(C2,F2,G2,H2))

But this was really slow (especially as other sheets are doing something similar) almost rendering the sheet to be unusable.

Unsure how to convert the above formula into an array formula so I just need to enter one fomula for a cells.

I’ve tried

=ArrayFormula(IFERROR(IF(B2:B = "Admin", AVERAGE(C2:C,D2:D,F2:F), AVERAGE(C2:C,F2:F,G2:G,H2:H)), ""))

But I get the following result

Name Role rank 1 rank 2 rank 3 rank 4 rank 5 rank 6 Average
Jon Admin 5 6 7 8 8 9 5.583333333
Tomas Accountant 3 2 2 9 3 3 5.3125
Ellie Admin 2 9 7 3 9 1 5.583333333
Sam Sales 4 7 3 9 1 8 5.3125

When it should be

Name Role rank 1 rank 2 rank 3 rank 4 rank 5 rank 6 Average
Jon Admin 5 6 7 8 8 9 6.333333333
Tomas Accountant 3 2 2 9 3 3 4.5
Ellie Admin 2 9 7 3 9 1 4.666666667
Sam Sales 4 7 3 9 1 8 5.5

>Solution :

use:

=INDEX(IF(B2:B="Admin", 
 QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&{C2:D, F2:F}), "×"),
 "select avg(Col2) 
  group by Col1  
  label avg(Col2)''"), 
 QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&{C2:C, F2:H}), "×"),
 "select avg(Col2) 
  group by Col1  
  label avg(Col2)''")))

enter image description here

Leave a Reply