google-sheetssplitaveragearray-formulasflatten
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)''")))
