Automatically updating standard deviation on a graph

[RICKDM]

Let me give some background informaiton on the project I am working on. I work for a construction company and we are doing work in a hospital, where we must keep airborne particles to a minimum. We collect data at 8 separate locations, 4 times a day. For each location, we take 5 readings per collection (160 data points each day - 8*4*5). We want to have a separate chart for each location 1-8. Example chart for Location 1: On the x-axis, there will be the date, and on the y-axis, there will be the average number of airborne particles taken on that particular day for location 1. We want the graph to also plot standard deviation so that we know when there is a significant peak and we must therefore investigate the cause for the peak. We want this graph to automatically update as we input more data into the excel spreadsheet. I am familiar with excel charts, but do not know how to make it continuously update itself to new data.

Thank you,

Rick

 

[GlennUK]

You can make a chart series reference update automatically:

http://peltiertech.com/WordPress/dynamic-chart-source-data/

You can then also use the same technique to calculate the mean of the data:
=AVERAGE(reference)
and Standard Deviation:
=STDEV(reference)
... with this data you could generate a dummy series next to the actual data, being the mean + the standard deviation ... this would be a straight line, which you could use to compare to the actual readings. [ you may want to use mean + ( 2 x Standard Deviation ) ... so that only peaks outside of 95% of readings will be above the line ]