Standard Deviation between two specific cell values.

[ilbondo]

I can calculate the standard deviation when the cell condition is above or equal to 70 using this formula.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=IF(COUNTA(F4:F67)<=70,STDEVA(F4:F67),STDEVPA(F4:F67))
</code>This works fine.
However I am looking to also calculate standard deviation between 60 and 69 and I am unable to get the standard deviation like i am with values 70 and above.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">=STDEV.P(IF((F4:F67>59)*(F4:F67<70),F4:F67))

Any help is appreciated</code>

 

[Aladin Akyurek]

You need to confirm the formula you have with control+shift+enter, not just enter.

 

[JustynaMK]

Hi ilbondo,

You mean you want to use STDEV.P formula if your sample size is between 60 and 69? If yes, then try the following:
=IF(AND(COUNTA(F4:F67)>59,COUNTA(F4:F67)<70),STDEV.P(F4:F67),0)
You can replace 0 ("false" argument) with your original formula, in order to add conditions for sample sizes below 60 and above 70.

Is this what you were looking for?

 

[ilbondo]

Hi ilbondo,

You mean you want to use STDEV.P formula if your sample size is between 60 and 69? If yes, then try the following:
=IF(AND(COUNTA(F4:F67)>59,COUNTA(F4:F67)<70),STDEV.P(F4:F67),0)
You can replace 0 ("false" argument) with your original formula, in order to add conditions for sample sizes below 60 and above 70.

Is this what you were looking for?

This is perfect thank you so much for that, it was been most helpful. I wish you all the best.

 

[JustynaMK]

Thank you, take care!