0

If I have a set A of numbers in Numbers (hah), I can use averageifs to calculate the average of the subset of A which satisfies certain conditions.

For example: Say I have the following table:

enter image description here

If I then type

=averageifs(A,A,"<=3",B,">5")

into another cell, it will calculate the average of the cells of column A which satisfy the following conditions:

  • is lesser than or equal to 3
  • the cell of column B in the same row is greater than 5

Only the cells in rows 1 and 6 satisfy both conditions, so the result is the average of those two cells (1.5).

I'd like to do the same thing with the standard deviation. However, there is no stdevifs function. There is a sumifs function, so I figured I could just type something like

=sqrt(sumifs((A-$C$1)^2,A,"<=3",B,">5")/(countifs(A,"<=3",B,">5")-1))

(assuming the conditional average calculated above is in C1), but I get an error; apparently sumifs only wants me to type in cells (e.g. A), not equations involving cells (e.g. (A-$C$1)^2).

Is there a way around this without creating new columns with commands such as =if(and(A1<=3,B1>5),(A1-$C$1)^2,"") and using those columns for the calculation?

If it helps, I'm using Numbers 6.1 on macOS 10.14.4.

Thanks in advance.

You must log in to answer this question.

Browse other questions tagged .