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
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))&"$"))))))