Excel-standard deviation "if"
How do I do such a task? There is a table in Excel, simplifying it as much as possible - there is a column A with sample numbers, there is a column B with values. To calculate the average for the entire sample is simple-like
=СРЗНАЧ(B2:B25)
Simple and standard deviation -
=СТАНДОТКЛОН.Г(B2:B25)
For groups, it is also not so difficult to calculate the average-in a separate cell (for example, X1), the number of the group, and the type
=СРЗНАЧЕСЛИМН(B2:B25;A2:A25;X1)
And how to calculate the deviation for each group? Only the table is wide, there are many columns, and the groups the rows can change - so I don't want to explicitly specify the ranges of the groups. It is better to have a number in a separate cell that you can refer to.
2 answers
You can use the function ДСТАНДОТКЛП
Only your values should be signed, and the condition add
No need to complicate the calculations. To determine the group average, СРЗНАЧЕСЛИ is sufficient:
=СРЗНАЧЕСЛИ(A2:A25;X1;B2:B25)
Database functions are computationally heavy (this is not critical for a pair of formulas) and require additional fields. You can do it without them.
=СТАНДОТКЛОН.Г(ЕСЛИ(A2:A25;X1;B2:B25))
Array formula, entered by a combination of three keys - Ctrl+Shift+Enter (after the input is completed, the formula should be framed with curly brackets)