Standard Deviation between Two Dates

[jessebh2003]

Hello all!

I am working on a very large spreadsheet and need to calculate the mean and standard deviation for each month of 2021. I've figured out the formula for the mean but the standard deviation isn't giving me the correct information.

Cell References

• $D$1 is the year (2021)
• $D$2 is the month (1)
• Data!$E contains the dates
• Data!$F contains the numeric values

I tried this formula first, which returns a standard deviation of 1.64.

=IFERROR(STDEV.P(IF(Data!$E$2:$E$22459>=DATE($D$1,$D$2,1),IF(Data!$E$2:$E$22459<=DATE($D$1,$D$2,31),Data!$F$2:$F$22459))),0)

However, when I filter to only the values for January 2021 and run the below, the formula returns 0.68.

=STDEV.P

I also tried this formula, which returns a standard deviation of 0.64.

=IFERROR(STDEV.P(IF(AND(Data!$E$2:$E$22459,">="&DATE($D$1,$D$2,1),Data!$E$2:$E$22459,"<="&DATE($D$1,$D$2,31)),Data!$F$2:$F$22459)),0)

But the formula returns the same value for every month ($D$2 replaced with $F$2, $H$2, etc. through end of the year).

Not sure what I'm doing wrong. Appreciate any help! Thank you.

 

[jessebh2003]

I'm looking at the original formula, and I see "YEAR($E$1)" but "MONTH($E$2)" one of those might have some junk in it.

$E$1 is 2021 and $E$2 is 1, both formatted as General. I tried changing them to numbers then dates but the formula still returns #CALC!.

 

[JamesCanale]

Ah, then try:
=STDEV.P(FILTER(Data!$F$2:$F$22459,(YEAR(Data!$E$2:$E$22459)=$E$1)*(MONTH(Data!$E$2:$E$22459)=$E$2)))

 

[jessebh2003]

And we have a winner!! Thank you so very much @JamesCanale !!