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 STDEV.P calculation.

 

Currently, I have created seperate columns with a recurring function below

 

=IF(ISBLANK(ACN63),"",IF(AND(ACN63<4000,ACN63>500),ACN63,""))

 

all the way from colums ACN to AZO

 

=IF(ISBLANK(AZO63),"",IF(AND(AZO63<4000,AZO63>500),AZO63,""))

 

I then calculate the STDEV.P of columns ACN to AZO for each row **this is a data set of over 300 rows**

Formulas & Functions
Need Help

6 Replies

  • 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))

Resources