Selective STDEV.P problems

[Cullen Roth]

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**

[#2]

Hi @Cullen Roth,

Still you did not specified your requirement.

You told only about what you doing. What you want now?

[#3]

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

[#4]

@Sergei Baklan 

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?

[#5]

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

[#6]

Oh that is fabulous! Thank you so much@Sergei Baklan 

[#7]

@Ruhurl , you are welcome