Search code examples
google-sheetsgoogle-sheets-formula

Standard Deviation with advanced use of FILTER

I have 3 persons where each have a score in 5 tests (score may also be blank) Each test can have 1-3 Types (A,B and C). I can SUM the total of the scores per type but I can't calculate the Standard Deviation per type. What I thought I could do is use the FILTER function to bring the values for scores of the type. To do this, I tried an array of 3 FILTERs, each checks if the respective row (6-8) has Type A in it: ={FILTER(F2:J4,F6:J6="A"),FILTER(F2:J4,F7:J7="A"),FILTER(F2:J4,F8:J8="A")}

This works if each of the 3 rows has at least one "A" , but if any of the rows (6-8) doesn't have "A" I get a #REF error. I tried wrapping each of the FILTER functions in a IFERROR but it doesn't work as well IFNA(FILTER(F2:J4,F8:J8="A")

Here's the spreadsheet: https://docs.google.com/spreadsheets/d/1yk57wY6vnKNNYoN18_EvEcQ3atqQDr41M5cguDR1_d0/edit#gid=0

In C13 is my formula that fails since there's no "A" in row 8. If I add "A" in H8 it will work. In C15 is the formula with IFNA, that also doesn't work. Thanks

Solution

  • Using hstack instead of curved braces within your existing formula since it can handle the missing array(s) w/out error

    =stdev(ifna(hstack(FILTER(F2:J4,F6:J6="A"),FILTER(F2:J4,F7:J7="A"),FILTER(F2:J4,F8:J8="A"))))

    enter image description here