Putting EXCEL to work with data  
 

Having found some good data, what to do with it? Excel has gone through several iterations since I find came across it in the mid-90s, but its statistical functions are much the same, and of course it gobbles up calculations with no fuss!  
  USING A SPREADSHEET  
 

 

 

An ARRAY is the word used for a selection of one or more columns of data in a spreadsheet. It is referred to by coordinates, eg A2:B16

Here s a selection of relevant statistical formulae, each of which must be preceeded with '=':

 
   

∑x
∑fx

∑fx2
m = (1/n)∑x

(1/n)∑(x2) − m2

n


kth smallest
kth largest
Minimum
Maximum
Mode
Median

Quartiles

 


=SUM(array)
=SUMPRODUCT(array, array)

=SUMPRODUCT(array, array, array)
=AVERAGE(array)

=STDEV(array)

=COUNT(array)
=COUNTIF(array, test)

=SMALL(array, k)
=LARGE(array, k)
=MIN(array)
=MAX(array)
=MODE(array)
=MEDIAN(array)

=QUARTILE(array, q)
q=0: Min,
q=1: LQ,
q=2: Median,
q=3: UQ,
q=4: Max
 
   

Calculating frequencies:

{=FREQUENCY(array1,array2)}
This is a complicated but useful command to generate a frequency table from an array of data. View the "Lottery" video below to see how it works.
 
  USING EXCEL TO TAKE A LOOK AT GLOBAL WARMING  
 


Using data form the Met Office website about weather at oxford since 1853. it is possible to look at a clear warming pattern over the last 20-30 years.

Watch the video to see how to create this spreadsheet.

oxford.xls

Excel's "Moving Point Average" facility is used to create the trend line.

Find out also how to reveal the highest recorded monthly average temperature in Oxford since records began,using the "=MAX()" command from the list above.

 
  USING EXCEL TO STUDY THE LOTTERY NUMBERS  
 


Is the UK Lottery fair? Do all the ball numbers come up equally likely?

There have been 1465 draws since it started in 1994, so it should have settled down by now.

Using data from the official lottery website, Excel shows that one number has been consistently in the lead since the start! In this exercise the frequency command is used to produce a bar chart.

Watch the video to see how to create this spreadsheet:

lottery.xls

Should you now definitely include 38? ... or avoid it?

 
   
Douglas Butler

Back to Statistics CONTENTS