Calculating the standard deviation (in a pivot table) of my data (hourly) and adding it to the hourly average of the data

[memassey99]

I have 3 years of data in 6 minute averages that I would like to get the standard deviation by hour and add it to the hourly average. I created a standard deviation in the "Values" section, but this field does not show up in the calculation field list. Is there a way to create a calculation of this data with a formula to get the standard deviation or is there a way to use the "Value" that I have already created and add it to the hourly average of this data? (Using Excel 2010)

Small sample of the data:
Date/Time Data
1/1/2012 6:00 10.84569136
1/1/2012 6:06 28.04859838
1/1/2012 6:12 24.08591953
1/1/2012 6:18 20.12661887
1/1/2012 6:24 25.46215515
1/1/2012 6:30 46.34870561
1/1/2012 6:36 28.83552512
1/1/2012 6:42 20.8179772
1/1/2012 6:48 22.8592935
1/1/2012 6:54 26.39748099
1/1/2012 7:00 22.12178046
1/1/2012 7:06 15.69875709
1/1/2012 7:12 11.95078562
1/1/2012 7:18 12.54260447
1/1/2012 7:24 10.50615389
1/1/2012 7:30 12.95169199
1/1/2012 7:36 16.45792276
1/1/2012 7:42 52.81641644
1/1/2012 7:48 150.8381466
1/1/2012 7:54 98.14184085

Small sample of the Pivot Table:
Row Labels StdDev of RB Data Average of RB Data
2012
Jan
1-Jan
6 AM 9.0 25.4
7 AM 47.7 40.4
8 AM 19.7 39.0
9 AM 26.2 37.2
10 AM 35.5 60.5
11 AM 88.5 155.0
12 PM 39.8 131.1
1 PM 27.0 63.3
2 PM 67.5 89.2
3 PM 14.6 32.0
4 PM 95.8 204.5
5 PM 59.9 113.3
6 PM 169.7 153.6
7 PM 17.9 59.2
8 PM 20.0 65.9
9 PM 12.9 50.0
10 PM 3.8 17.8
11 PM 51.6 54.4
2-Jan
12 AM 11.0 42.7
1 AM 24.0 43.1
2 AM 3.0 11.1
3 AM 0.6 6.8
4 AM 50.9 59.8
5 AM 1.0 7.4
6 AM 65.4 72.9
7 AM 69.6 135.8
8 AM 60.4 34.2
9 AM 72.6 60.0
10 AM 57.6 49.2
11 AM 7.1 10.3

 

[CastroTheCat]

You need a Calculated Item- go to PivotTable Tools/Options/Fields, Items & Sets/Calculated Items

Create a new item, give it a name, and then in the Formula section, enter it as ='StdDev of RB Data' + 'Average of RB Data'

It will add considerably to the calculating times for the table whenever you change things.

 

[memassey99]

"'StdDev of RB Data' + 'Average of RB Data' do not show up in the list of available fields, so I am unable to use them in the calculation. I will try to type it in and see if it will work that way.

One thing that I also noticed when I tried to perform the standard deviation in a calculation versus using the function "stdev"- the function will use the 6 minute data, but the calculation only uses one point of the data (not sure which point), because I get a div/0 error. If I perform an average on the same data in a calculation, it will average all of the 6 minute data. Not sure why it is doing this either.