CODEWITHSUNDEEP
google-sheetsfiltergoogle-sheets-formulamatchstdev
Tom
Tom

Reputation: 233

How can I group Standard Deviation of multiple sets

I have a group of respondents (e.g. 4), each answering a few questions (e.g. 10 questions). The questions belong to 3 groups (Basic, Advanced, Other). I want to calculate the Standard deviation for each group (STDev for the Basic questions, STDev for the Advanced questions, STDev for the Other questions). How can I do that?

Here's an example google sheet, where A:K are the answers of the respondents and M:N is the grouping of the different questions. I want the Standard deviations, per group, in column Q using a formula. https://docs.google.com/spreadsheets/d/10CjMTFVU5qmGbKmqeo2FxuSf2G712cKxYliBN0cE7KU/edit#gid=0

Upvotes: 1

Views: 82

Answers (2)

rockinfreakshow
rockinfreakshow

Reputation: 29982

try:

=STDEV(FILTER(B2:K5,XMATCH(B1:K1,FILTER(M2:M11,N2:N11=P2))))

enter image description here

Upvotes: 1

player0
player0

Reputation: 1

use:

=STDEV(FLATTEN(FILTER(B$2:K$5, REGEXMATCH(B$1:K$1&"", 
 "^"&TEXTJOIN("$|^", 1, ""&FILTER(M$2:M$11, N$2:N$11=P2))&"$"))))

arrayformula:

=BYROW(P2:P4, LAMBDA(x, STDEV(FLATTEN(FILTER(B$2:K$5, REGEXMATCH(B$1:K$1&"", 
 "^"&TEXTJOIN("$|^", 1, ""&FILTER(M$2:M$11, N$2:N$11=x))&"$"))))))

Upvotes: 1

Related Questions