Analysis of data and its presentation

Analysis

Recording data accurately and regularly is the start for an increasing amount of statistics. This is the routine aspect of the hobby but the analysis and presentation, for me, is the interesting aspect, which becomes more relevant with passing years when comparisons can be made and trends become apparent. The longer the period for which data is recorded, the more relevant can be the trends that appear. Meteorologically, a 30-year period is necessary before trends can be discerned with any confidence.

I am not a statistician but what follows will give a guide to simple techniques that are appropriate with which to analyse your data. They can be done manually but are labour intensive with the possibility of errors occurring. The use of a computer with software such as an ‘Office Suite’ will enable analysis to be carried out automatically, quickly and accurately. What follows will be based on the Microsoft Office software using a Windows computer and an Excel spreadsheet. Those using an Apple computer will be able to use the same processes but which might be a little more complicated in some instances.

Finding Maximum & Minimum

After entering the data in an Excel sheet, which initially will probably consist of the date or year in two columns with the relevant statistics continuing down the page as each day, month or year is experienced. Scanning the figures and then changing the relevant number to a ‘bold’ font can highlight the maximum and or minimum values in a column of data. Alternatively, changing the font colour will help the value to be picked out much more easily at a later date.

However, I find the following technique the quickest and easiest. Having entered a column of figures running down the page, say from cell A2 to A32 for data covering a month, highlight the entire column. In a convenient empty cell insert the following formula =MAX(A2:A32) and then press ‘enter’. This can be applied to find the minimum by using MIN instead of MAX.There is a method of using the ‘Paste Function’ although I find that a little more complicated. Naturally, this can be used for columns of data covering rainfall, wind and much else as the station equipment expands.

Finding the Average

The average or mean value relating to a set of data covering a month or year is a most useful statistic. It is often referred to in weather broadcasts and can be compared to weather stations at other sites. As before, highlight the column of data and assuming it covers cells A2 down to A32 (enough for a 31 day month), place in a convenient empty cell the following =AVERAGE(A2:A32) to obtain the average or mean.

The above technique can be applied to all columns individually. However, if there are adjacent columns for which you wish to apply the same function, the following will save much time. Having found the maximum, minimum or average value for column one, highlight that cell and then with your mouse drag the spot at the bottom right hand corner of the cell to the right, where your additional columns are placed, and it will automatically apply the function to each column changing the column formulae from B to C and so on.

Conditional Formatting

There is an additional function called ‘Conditional Formatting’ that selects the highs and lows to be coloured automatically which can be applied after the initial maximum, minimum or average value has been found as explained above. This will allow these values to be reassessed automatically each time new data is added to the column. After highlighting the complete column click on ‘Conditional Formatting’ which can be found under the ‘Home’ tab. Select ‘Highlight Cells Rules’ which brings up a new panel called ‘Equal to’. Click on the small square with a central small red arrow at the end of the white line, next click on the value for ‘Maximum’ determined above on your spreadsheet. Then click back on the small square as before, next click on the line that has text something like ‘Light Red Fill with Dark Red Text to select this colour. However, selecting the arrow at the end produces a greater selection OR ‘Custom Format’ that brings up a new panel from which, using the ‘Fill’ tab, you can choose your own colour. The technique when using an Apple computer is a little more complicated but provides the same outcome.

Conditional Formatting can seem complicated and laborious to initially set up but can save much time when additional values are added each month or year.

Presentation

Glancing along a column of numbers will give an impression of the highs, lows and trends. However, a picture obtained form the data is often a very useful tool from which information can be gained quickly.

As mentioned above, I will give examples using a Windows computer and an Excel spreadsheet from a Microsoft Office Suite of software.

Initially, for the sake of clarity, I will describe a column chart for the maximum temperature against the date. Prepare on an Excel spreadsheet consisting of two columns with the date or year in column one and the relevant statistic in column two with titles at the top. Using the ‘Insert’ tab select ‘Column’ and ‘2D’ then employ the following procedure. Right click on blank graph, ‘select data’, highlight your data column, OK on ‘Select Data Source’ panel that puts your data on the vertical axis.
Right click on the graph again, choose ‘select data’, ‘Edit’ from the right hand panel of the ‘Select Data Source’ panel (Horizontal Axis Labels). A new small panel (Axis Labels) arrives so click on the small blue square with central red arrow to choose Axis label range, highlight your date or Year column and click back again on the small square on the ‘Axis Labels’ panel, which puts the date or year on the horizontal axis then finally OK and OK on the two panels.

For more adjustments to this newly created graph click on the vertical or horizontal axis, right click on it and choose ‘Format Axis’. From this new panel you can refine your graph by changing the range of the axis and much more.

Once the above techniques are mastered you can adapt your graph to suit your own requirements. Also under the ‘Insert’ tab there exists a range of different graphs such as line graph, pie chart, bar chart and scatter graph (where a trend line can be added) that you can utilise to present your data.

ana

Leave a Reply