Welcome to the second part of our 5 part series on using Excel.
In our last article, we looked at importing data into Excel with a simple example. Now we will look at some simple ways to check that the data is ‘reasonable.’ A vital step if we want results that make any sense.
2. Checking the data.
We can be confident in this case that the analysis done by the Met Office is correct. But what if we were not sure
We’ll use two methods, a simple method and a more thorough method
||Select the spring time values for the first year.
In the bottom right corner of your screen, Excel has computed the sum and the average of these three values. Yes, they’re in agreement with the Met office values.
With this more thorough method, we will check all the values.
|Click in cell S2 and calculate the average values for the spring as in the example, by clicking the fx button The answer you get in the cell should be reasonable, if not, you have likely made a mistake.
In cell T2, subtract your computed value from the Met office value. By typing the function: ‘=O2-S2’ into the cell.Now in the U column, write the following function:=IF(ABS(O2-S2)<0.3,0,1)
Explanation of the function
This equation says, IF the difference between your calculated mean (S2) and that given by the Met Office (O2) is less than 0.3 then place a ‘0’ in this cell, otherwise put a 1.You can set the tolerance to whatever value you want, but 0.3 seems reasonable with this data.
|Then select both cells and drag them down the columns until you get to the bottom of the table of data.
If the two columns are in agreement, you should have a column of zero’s and no ‘1’s.
Finally, sum the column and place it in the cell U1. If there’s an error, this value will be larger than zero.
In this tutorial, we learnt some simple tricks for checking our data. There are many other methods you can use. If you have any suggestions for further tutorials please contact me.
In part three we will look at using pivot tables to analyse our data.