Std dev.

[jcb5489]

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.

 

[thenapolitan]

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

 

[jcb5489]

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

 

[jcb5489]

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]

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]

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]

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)))

 

[jcb5489]

---

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!