1

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
player0
  • 124,011
  • 12
  • 67
  • 124
mcclosa
  • 943
  • 7
  • 29
  • 59

1 Answers1

1

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • 3
    OMG, I was just about to post. Oh well... this would probably work too... Your trying to apply this to the entire column. Use Filter to minimize the calculations to only be the area you're using. Regarding the average, this is unfortunately difficult when using sums and beverages. Try something simple like this: `=FILTER(If(B2:B="Admin",(C2:C+D2:D+F2:F)/3,(C2:C+F2:F+G2:G+H2:H)/4),A2:A<>"")` – pgSystemTester Sep 08 '22 at 21:41
  • @player0 This worked perfectly, thank you. If I wanted to add an `OR` operator instead of an `IF` how would I do that? I tried `=INDEX(IF(OR(B2:B="Admin", B2:B="Sales"),` but the value is always truth, even if column B is neither `Admin` or `Sales`? – mcclosa Sep 08 '22 at 23:12
  • 1
    @mcclosa either: `IF((B2:B="Admin") + (B2:B="Sales"),` or: `IF(REGEXMATCH(B2:B, "Admin|Sales"),` – player0 Sep 08 '22 at 23:30
  • 1
    @player0 Thanks, works a charm! – mcclosa Sep 08 '22 at 23:39