Forum Discussion

Cullen Roth's avatar
Cullen Roth
Copper Contributor
May 31, 2017

Selective STDEV.P problems

Hello,   I am attempting to find a way to calculate the Standard Deviation of 600 different values that range from 0 and up. I would like to exclude any values below 500 and above 4,000 from the ST...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 06, 2017

Hi Cullen,

 

You may use array formula (enter with Ctrl+Shift+Enter) like

 

=STDEV.P(IF(((ACN63:AZO63>=500)*(ACN63:AZO63<=4000)=1),ACN63:AZO63))

 

Ruhurl's avatar
Ruhurl
Copper Contributor
Mar 04, 2020

SergeiBaklan 

Hi, I am trying to use STdev.p with an IF (similar to AVERAGEIF). I need Excel to check if the value in column F ($F18:$F97) is equal to 1, and calculate STDEV for all those rows in column N ($N18:$N97)

 

I have tried a few different formale based on other answers  ...these give me a result (when I press CSE) but the result is not correct

 

Attempt1

=STDEV.p(IF($F18:$F97,1,$N18:$N97))

Attempt2

=STDEV.P(IF(($F18:$F97),1),$N18:$N97)

 

Can anyone help?

 

 

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 04, 2020

    Ruhurl 

    If you are on modern Excel that could be

    =STDEV.p(FILTER($N18:$N97,$F18:$F97=1))

    or

    =STDEV.p(IF($F18:$F97=1,$N18:$N97))
    • Ruhurl's avatar
      Ruhurl
      Copper Contributor
      Mar 04, 2020

      Oh that is fabulous! Thank you so muchSergeiBaklan 

Resources