# Std dev.

#### jcb5489

##### New Member
I have daily data over many years. I have calculated the monthly mean, and now I need to find the standard deviation of the daily data from the monthly mean over all months of the data set. Help please.

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### thenapolitan

##### Board Regular
Could you post some sample data for us to see? Does =STDEV work?

#### jcb5489

##### New Member
I have tried: =and(a:a<=e3,a:a>e4)stdev(c:c)

#### jcb5489

##### New Member
Column a is dates in descending order. And I need the standard deviation of column c. I need a formula that will group the data by month to give me the std.dev. for each month.

#### thenapolitan

##### Board Regular
Column a is dates in descending order. And I need the standard deviation of column c. I need a formula that will group the data by month to give me the std.dev. for each month.

Sounds like you need a pivot table, sort by date and group by month

#### jcb5489

##### New Member
Could you post some sample data for us to see? Does =STDEV work?

I have =if(and(a:a<=e2,a:a>e3),stdeva(c:c)) and it still gives me the standard deviation of the whole c column.

#### joeu2004

##### Banned user
I have tried: =and(a:a<=e3,a:a>e4)stdev(c:c)

Array-enter the following formula (press ctrl+shift+Enter instead of just Enter):
<a1:a40000,if(a1:a40000<=e3,c1:c40000)))
<a1:a40000,if(a1:a40000<=e3,c1:c40000)))

</a1:a40000,if(a1:a40000<=e3,c1:c40000)))
</a1:a40000,if(a1:a40000<=e3,c1:c40000)))
=STDEV(IF(A1:A40000<=E3,IF(A1:A40000>E4,C1:C40000)))<a1:a40000,if(a1:a40000<=e3,c1:c40000)))
<a1:a40000,if(a1:a40000<=e3,c1:c40000)))
</a1:a40000,if(a1:a40000<=e3,c1:c40000)))
<a1:a40000,if(a1:a40000<=e3,c1:c40000)))
</a1:a40000,if(a1:a40000<=e3,c1:c40000)))

<a1:a40000,if(a1:a40000<=e3,c1:c40000)))

although I might use STDEVP instead of STDEV in this case.</a1:a40000,if(a1:a40000<=e3,c1:c40000)))

Note that I use A1:A40000 instead of A:A. The latter forces Excel to process and create temp arrays of over 1 million entries. If you have "daily data over many years", you have much fewer rows of data.</a1:a40000,if(a1:a40000<=e3,c1:c40000)))

Last edited:

#### jcb5489

##### New Member

Array-enter the following formula (press ctrl+shift+Enter instead of just Enter):
<a1:a40000,if(a1:a40000<=e3,c1:c40000)))
<a1:a40000,if(a1:a40000<=e3,c1:c40000)))

</a1:a40000,if(a1:a40000<=e3,c1:c40000)))
</a1:a40000,if(a1:a40000<=e3,c1:c40000)))
=STDEV(IF(A1:A40000<=E3,IF(A1:A40000>E4,C1:C40000)))<a1:a40000,if(a1:a40000<=e3,c1:c40000)))
<a1:a40000,if(a1:a40000<=e3,c1:c40000)))
</a1:a40000,if(a1:a40000<=e3,c1:c40000)))
<a1:a40000,if(a1:a40000<=e3,c1:c40000)))
</a1:a40000,if(a1:a40000<=e3,c1:c40000)))

<a1:a40000,if(a1:a40000<=e3,c1:c40000)))

although I might use STDEVP instead of STDEV in this case.</a1:a40000,if(a1:a40000<=e3,c1:c40000)))

Note that I use A1:A40000 instead of A:A. The latter forces Excel to process and create temp arrays of over 1 million entries. If you have "daily data over many years", you have much fewer rows of data.</a1:a40000,if(a1:a40000<=e3,c1:c40000)))

Thanks a lot!

Replies
3
Views
4K
Replies
1
Views
1K
Replies
0
Views
322
Replies
6
Views
720
Replies
1
Views
257

1,191,354
Messages
5,986,173
Members
440,008
Latest member
Cmbuck

### 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.

### Which adblocker are you using?

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

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