Google Sheets – How to Get Sample Standard Deviation Matching Conditions

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I am trying to get the standard deviation of a subset of data. I have two sheets

Name Value
a    1
a    2
a    1
b    5
b    5

In a separate sheet I have:

Name StdDev
a   
b

GOAL: I am trying to get:

Name StdDev
a    .57
b    0

I tried:

=query('Sheet1'!A1:B999,"select STDEV(B) where A='"&A1&"'")

Error:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "(" "( "" at line 1, column 13. Was expecting one of: "where" … "group" … "pivot" … "order" … "skipping" … "limit" … "offset" … "label" … "format" … "options" … "," … "" … "+" … "-" … "/" … "%" … "" … "/" … "%" … "+" … "-" …

Best Answer

  • first of all, there is no STDEV(Col) parameter in Google Sheets' QUERY so you need to create it from:

0

0

=SQRT(SUM(ARRAYFORMULA((QUERY({Sheet1!$A$2:$B}, "select Col2 where Col1='"&D2&"'")-
 AVERAGE(QUERY({Sheet1!$A$2:$B}, "where Col1='"&D2&"'")))^2))/
 (COUNTIF(Sheet1!$A$2:$A, "="&D2)-1))

0

Related Topic