STDEV from AVERAGEIFS using date ranges

**BAT3** · 05-30-2012, 04:50 PM

I am trying to find a way to calculate the Standard Deviation similar to the AVERAGEIFS function. I did do a search but couldn't find a post with this exact issue.

I have used the AVERAGEIFS function to calculate the mean within a specific three month date range. For example:

Please Login or Register  to view this content.

The dates are in column A and the actual values in column D.

I have about 5 years worth of data so the AVERAGEIFS function is brilliant for calculating the mean in a specific date range, but I haven't been able to get the StDev of the mean.

Thanks for any suggestions.

**haru** · 05-30-2012, 05:54 PM

Here's a tip, this is an array, after you enter the formula, do not just hit enter, you need to hold CTRL + SHIFT + ENTER, which will put {} around it:

=STDEV(IF(AND(Range<=condition 1,Range>=condition 2),range of stdev))

If you have a sample worksheet, I may be able to help more.

**Palmetto** · 05-30-2012, 07:49 PM

You could use a Pivot Table rather than an array formula.

Drag the applicable field to the Values quadrant of the PT dialog and change the default option from SUM to STDEV or STDEVP.
You can drag the same field to the Values quadrant more than once and set it up to show average, min, manx, etc.

Plus, by putting the date into the report filter you can select the date range for the data.
Or, put the date field in the Row Labels section of the dialog and group on specific date intervals using the grouping option of the PT.

**BAT3** · 05-31-2012, 09:06 AM

I tried your formula and suggestion to use array. Ctl + Shift + Enter. But it returns Div/0! error...

example.xlsx

I tried attaching an example. Thanks for your help!

**Palmetto** · 05-31-2012, 07:18 PM

I would use a couple of cells to contain dates to make the formula more flexible so that you can alter the date criteria w/out editing the formula

=STDEV(IF(A3:A266>=J1,IF(A3:A266<=K1,D3:D266)))

Where J1 contain the earliest date (12/1/2006 per your example)
and K1 contains end date (2/2/8/2007)