Google Sheets QUERY function

Question

I'm trying to create a query function that will average the numbers in columns B, C and D, based on what's in column f

example:

buckape 1000    10  1000    defense e6vowe0
aiden   500 9   900 defense e6vowe0
faxyst  400 8   800 defense e6vowe0
lumi    300 4   500 defense e6vowe0
refresh 200 2   400 defense e6vowe0
sahsook 100 1   200 defense e6vowe0
doltic  0   1   100 defense e6vowe0 

I'm able to, currently, make it average out the rows grouped by column F like so:

=Query(A:F, "select F, avg(B), avg(C), avg(D) where F is not null and E='defense' group by F",-1)

which, in turn, results in my averages displayed by ID (which is good)

event ID    avg heal    avg kills   avg damage
e6vowe0     357.14      5.00        557.14
hfgk3og0    514.29      5.29        814.29

but, I'm struggling to get it to (in hopefully the same formula) also divide each original number (i.e B2 / average of B based on F), then repeating the process for columns C and D as well.

Hope this made sense, hope someone out here knows the query function better than I do. Thank you

Answer 1

try:

=ARRAYFORMULA(IF(A2:A="",,{A2:A, B2:D/QUERY(QUERY(A:F, 
 "select avg(B),avg(C),avg(D)
  where F is not null 
    and E='defense' 
  group by F", 0), "offset 1", 0)}))

---

update:

=ARRAYFORMULA(IF(E2:E="defense", {A2:A, B2:D/
 IFNA(VLOOKUP(F2:F, QUERY(QUERY(A:F, 
 "select F,avg(B),avg(C),avg(D)
  where F is not null  
    and E='defense' 
  group by F", 0), "offset 1", 0), {2,3,4}, 0))}, ))