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