STDEV from diferent date ranges. As if we were using AVERAGEIFS

[mcva]

Hi
I am trying to find a way to calculate the Standard Deviation similar to the AVERAGEIFS function.
The dates are in column P, i n the cell W is the year I want, and the actual values in column Q. I´m using the following approach:

{=STDEV(IF($P$2:$P$86,">="&DATE(W90,1,1),IF($P$2:$P$86,"<="&DATE(W90,12,31),Q2:Q86)))}

I´m getting #DIV/0!
I cant find a solution for this
Thank you

 

[Eric W]

It looks like you're not finding any values to include. Check that your dates are actual dates and not text values. Make sure there's actually some values in your date range. You can use the Evaluate Formula tool to see if you're getting any values at all.

 

[mcva]

It looks like you're not finding any values to include. Check that your dates are actual dates and not text values. Make sure there's actually some values in your date range. You can use the Evaluate Formula tool to see if you're getting any values at all.

Hi they are actual dates and there are actual values in the data range

 

[Eric W]

Sorry, I missed it on first glance. Your formula is in W16. The problem is that you have quote marks around your conditional, you only do that in a COUNTIF type formula. You could also shorten your formula to the W15 version, if you're always looking at the whole year. I'd also worry a bit that your date range has the absolute range reference ($), and the value range does not.

Book4
	P	Q	R	S	T	U	V	W
3	1/1/2020	1
4	6/5/2020	2
5	2/2/2021	3
6	3/5/2021	4
7	7/8/2021	5
8	9/10/2021	6
9	3/2/2022	7
10	5/5/2022	8
11
12
13
14
15								1.290994
16								1.290994
89
90								2021
Sheet7

Cell Formulas
Range	Formula
W15	W15	=STDEV(IF(YEAR($P$2:$P$86)=W90,$Q$2:$Q$86))
W16	W16	=STDEV(IF($P$2:$P$86>=DATE(W90,1,1),IF($P$2:$P$86<=DATE(W90,12,31),Q2:Q86)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mcva]

Sorry, I missed it on first glance. Your formula is in W16. The problem is that you have quote marks around your conditional, you only do that in a COUNTIF type formula. You could also shorten your formula to the W15 version, if you're always looking at the whole year. I'd also worry a bit that your date range has the absolute range reference ($), and the value range does not.

Book4
	P	Q	R	S	T	U	V	W
3	1/1/2020	1
4	6/5/2020	2
5	2/2/2021	3
6	3/5/2021	4
7	7/8/2021	5
8	9/10/2021	6
9	3/2/2022	7
10	5/5/2022	8
11
12
13
14
15								1.290994
16								1.290994
89
90								2021
Sheet7

Cell Formulas
Range	Formula
W15	W15	=STDEV(IF(YEAR($P$2:$P$86)=W90,$Q$2:$Q$86))
W16	W16	=STDEV(IF($P$2:$P$86>=DATE(W90,1,1),IF($P$2:$P$86<=DATE(W90,12,31),Q2:Q86)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Your solution is perfect. Thank you

 

[Eric W]

Glad to help. ?