Conditional Standard Deviation Formula in Excel

0
votes

I am trying to run a standard deviation formula on only a subset of a row. I have two conditions, the be below the top end of a range and above the bottom end of a range. Excel should the run STDEV formula on all numbers which meet this condition. I tried the following conditional array formula but consistently got the #Value error. Any suggestions?

The below formula was properly entered into excel as an array.

{=STDEV.P(IF(AND($V9:$V2000>($D$5*365-$D$7),$V9:$V2000<($D$5*365+$D$7)),$U$9:$U$2000,""))}

Any suggestions?

1
excelexcel-formula
Are you using Ctrl-Shift-Enter after editing the formula instead of Enter to force it to be an array. If done properly Excel will put {} around the formula. – Scott Craner
Yes, I am entering the above formula as an array. – Stuart Allan
are there any errors in the data range or non-numerical characters? – Scott Holtzman
I've never seen a function name with a . in the middle of it before. – Matt Cremeens
Array formulas do not like AND : =STDEV.P(IF(($V9:$V2000>($D$5*365-$D$7))*($V9:$V2000<($D$5*365+$D$7)),$U$9:$U$2000,"")) – Scott Craner

1 Answers

0
votes

Array formulas do not like the AND operator. A solution to the above formula is the following:

=STDEV.P(IF(($V9:$V2000>($D$5*365-$D$7))*($V9:$V2000<($D$5*365+$D$7)),$U$9:$U$2‌​000,""))