Use Excel to Do data description--moving average

A chemical reaction process, every 2 minutes to the system to test the pressure data. Due to the specificity of the reaction, it is necessary to investigate the pressure average of every 8 minutes, if the pressure average is higher than 15MPa, it is considered that the first pressure data from the range of the average value is entered into the reaction phase, please use Excel to give the reaction stage time interval.

The moving average is a series of changes in the data according to the specified number of data in order to obtain the average, and as the trend of data changes for the analyst reference. Moving averages are also common in life, and the four seasons in the meteorological sense are the best applications for moving averages.

Note: This feature requires Excel extensions, and if your Excel does not have data analysis installed, select Tools-add-ins to load the analysis database with the support of the installation CD. After successful loading, you can see the data analysis option in the Tools Drop-down menu.

Operation Steps

1. Open the original data table, make the original data requirements for this example, a single column, please confirm the type of data. In this example, the pressure changes with time to the data, in the data analysis, only the use of pressure data columns.

It should be noted that, because the average value of the need for a certain amount of data, then require the original data is not less than the number of averages, in Excel, the amount of data is not less than 4.

2. Select Tools-Data Analysis-histogram, the Property settings box appears, and then select:

Input range: The original data area, if there is a data label can choose "logo in the first row";

Output region: Moving average value display area;

Interval: Specifies the use of several sets of data to derive an average;

Chart output; The original data and the moving average values are displayed in the form of graphs for comparison;

Standard error: The standard deviation between actual and predicted data (moving average data) to show the difference between the forecast and the actual value. A smaller number indicates the better the forecast.

3. After the input is completed, the corresponding data and chart can be generated immediately.

A lot of information can be seen from the generated graphs.

According to the requirement, the generated moving average value reached 15.55MPa at 9:02, that is to say, the four data including this data has reached 15MPa before, then 8 minutes ago, 8:56, the system entered the reaction phase, the same analytical method can be known, The reaction phase ended at 9:10 and the response period was 8:56-9:10, which lasted for 14 minutes.

Click one of the cell "D6" to see that it is the average of "b3-b6", and the Cell "E11" is "SQRT (SUMXMY2 (B6:B9,D6:D9)/4)", which means that the average of the square of the difference between the corresponding data is b6-b9,d6-d9 and the square root is taken. Which is the standard deviation of the array.