Standard Deviation between Two Dates

J

jessebh2003

Board Regular
Joined
Feb 28, 2020
Messages
71
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula: 
=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.
Excel Formula: 
=STDEV.P

I also tried this formula, which returns a standard deviation of 0.64.
Excel Formula: 
=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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
You are figuring all of those zeros into the stdev.p. Which impacts the output.

Try this,
MrExcelPlayground12.xlsx
ABCDE
10Jan 2021
111/1/20211.563211.563210.610542
121/2/20210.9384180.938418
131/3/20212.7196642.719664
141/4/20210.8973540.897354
151/5/20211.617941.61794
161/6/20211.6032861.603286
171/7/20210.8887340.888734
181/8/20212.4334242.433424
191/9/20212.3380442.338044
201/10/20211.0259061.025906
211/11/20212.718182.71818
221/12/20211.8696951.869695
231/13/20211.6517811.651781
241/14/20211.2267881.226788
251/15/20212.0573452.057345
261/16/20210.9382450.938245
271/17/20212.9830282.983028
281/18/20212.1259642.125964
291/19/20212.7439262.743926
301/20/20211.9971621.997162
311/21/20211.3950751.395075
321/22/20211.3999831.399983
331/23/20211.9788421.978842
341/24/20211.2526311.252631
351/25/20211.894721.89472
361/26/20211.2142221.214222
371/27/20212.6038622.603862
381/28/20212.105562.10556
391/29/20211.9619221.961922
401/30/20212.6231932.623193
411/31/20212.0018022.001802
422/1/20210.622358
432/2/20210.600511
442/3/20212.966286
452/4/20210.648982
462/5/20212.731784
472/6/20211.785264
482/7/20211.884239
492/8/20211.702197
502/9/20211.643003
512/10/20211.064571
522/11/20211.427195
532/12/20211.036147
542/13/20212.490683
552/14/20212.616271
562/15/20211.220778
572/16/20212.860444
582/17/20210.721177
592/18/20211.96276
602/19/20212.36145
612/20/20212.365361
622/21/20210.959272
632/22/20210.773844
642/23/20211.045906
652/24/20211.102685
662/25/20212.22779
672/26/20212.275199
682/27/20211.279757
692/28/20211.647638
703/1/20211.759044
Sheet1
Cell Formulas
RangeFormula
D11:D41D11=FILTER(B11:B70,(YEAR(A11:A70)=YEAR($A$10))*(MONTH(A11:A70)=MONTH($A$10)))
E11E11=STDEV.P(D11#)
Dynamic array formulas.


You can do it in one step of course.
 
Upvote 0
JamesCanale said:
You are figuring all of those zeros into the stdev.p. Which impacts the output.

Try this,
MrExcelPlayground12.xlsx
ABCDE
10Jan 2021
111/1/20211.563211.563210.610542
121/2/20210.9384180.938418
131/3/20212.7196642.719664
141/4/20210.8973540.897354
151/5/20211.617941.61794
161/6/20211.6032861.603286
171/7/20210.8887340.888734
181/8/20212.4334242.433424
191/9/20212.3380442.338044
201/10/20211.0259061.025906
211/11/20212.718182.71818
221/12/20211.8696951.869695
231/13/20211.6517811.651781
241/14/20211.2267881.226788
251/15/20212.0573452.057345
261/16/20210.9382450.938245
271/17/20212.9830282.983028
281/18/20212.1259642.125964
291/19/20212.7439262.743926
301/20/20211.9971621.997162
311/21/20211.3950751.395075
321/22/20211.3999831.399983
331/23/20211.9788421.978842
341/24/20211.2526311.252631
351/25/20211.894721.89472
361/26/20211.2142221.214222
371/27/20212.6038622.603862
381/28/20212.105562.10556
391/29/20211.9619221.961922
401/30/20212.6231932.623193
411/31/20212.0018022.001802
422/1/20210.622358
432/2/20210.600511
442/3/20212.966286
452/4/20210.648982
462/5/20212.731784
472/6/20211.785264
482/7/20211.884239
492/8/20211.702197
502/9/20211.643003
512/10/20211.064571
522/11/20211.427195
532/12/20211.036147
542/13/20212.490683
552/14/20212.616271
562/15/20211.220778
572/16/20212.860444
582/17/20210.721177
592/18/20211.96276
602/19/20212.36145
612/20/20212.365361
622/21/20210.959272
632/22/20210.773844
642/23/20211.045906
652/24/20211.102685
662/25/20212.22779
672/26/20212.275199
682/27/20211.279757
692/28/20211.647638
703/1/20211.759044
Sheet1
Cell Formulas
RangeFormula
D11:D41D11=FILTER(B11:B70,(YEAR(A11:A70)=YEAR($A$10))*(MONTH(A11:A70)=MONTH($A$10)))
E11E11=STDEV.P(D11#)
Dynamic array formulas.


You can do it in one step of course.
Click to expand...
Hi,

Sorry I'm not sure I'm following.

Using my ranges, are you saying that it should be this?

=FILTER(Data!$F$2:$F$22459,(YEAR(Data!$E$2:$E$22459)=YEAR($E$1))*(MONTH(Data!$E$2:$E$22459)=MONTH($E$2)))

How do I put the standard deviation into that formula to get the answer for the month? Thanks.
 
Upvote 0
Just put that into a STDEV.P around it:
Excel Formula: 
=STDEV.P(FILTER(Data!$F$2:$F$22459,(YEAR(Data!$E$2:$E$22459)=YEAR($E$1))*(MONTH(Data!$E$2:$E$22459)=MONTH($E$2))))
 
Upvote 0
JamesCanale said:
Just put that into a STDEV.P around it:
Excel Formula: 
=STDEV.P(FILTER(Data!$F$2:$F$22459,(YEAR(Data!$E$2:$E$22459)=YEAR($E$1))*(MONTH(Data!$E$2:$E$22459)=MONTH($E$2))))
Click to expand...
Thanks. I tried that and it returned a #CALC!.
 
Upvote 0
When you take away the STDEV.P part, what does the filter function output? Is there any errors in the sequence that is returned.
 
Upvote 0
You might go back to your original formula and change it with a blank instead of a 0:
=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))),"")
 
Upvote 0
JamesCanale said:
You might go back to your original formula and change it with a blank instead of a 0:
=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))),"")
Click to expand...
Changing the 0 to "" at the end didn't make a difference unfortunately.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Standard deviation of a subset in VBA
Replies
7
Views
877
SHW2022
S
J
  • Solved
Calculating the standard deviation of two weighted arrays.
Replies
5
Views
799
StephenCrump
S
D
  • Question Question
VBA Code Copy/Replace Values base on Date
Replies
1
Views
170
Anthony47
A
T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
245
Thlama
T
P
  • Solved
What will be formula on Search Box as i type data filter accordingly...
2 3
Replies
26
Views
616
pankajgrover
P

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top