When using large amounts of data it is often easier to use a spreadsheet, such as Excel, to analyse the data.
This activity is designed for you to create a frequency table and analyse data in Excel
Here is a list of goals scored in 20 football matches.
Create the Frequency table above in Excel.
See if you can make your headings Bold and Centre your numbers.
From the table above, you can identify the mode. Remember, the mode is the greatest frequency.
What is the mode number of goals scored in a game?
The mean can also be calculated from a frequency table. With practice, this can be done quickly and easily using excel.
On your spreadsheet create another column (in column C) and label it 'goals x frequency', (cell C1).
You now need to multiply the number of goals by the frequency and put it in this column.
For example,
0 x 4 = 0
1 x 6 = 6
2 x 4 = 8
etc.
Excel can quickly do this for you.
In Cell C2 type =A2*B2 and press enter.
Click on Cell C2, click on Edit in the toolbar and select Copy.
Click on cell C3, hold down the mouse key and drag to cell C8.
Then click on Edit and select Paste.
Your frequency table should look like below:
Now you need to create a Total for column B and C. To do this:
In cell A9 type Total and press enter
In cell B9 type =SUM(B2:B8) and press enter
In cell C9 type =SUM(C2:C8) and press enter
You should now have totals in cells B9 and C9.
Now you are ready to find the mean number of goals per game. To do this:
In cell A11 type Mean and press enter.
In cell B11 type =C9/B9 and press enter.
Your frequency table should now look like below:
Excel has calculated the mean number of goals per game as 1.85.
The whole process can be slow and awkward at first, however with practice, you will quickly be able to create presentable frequency tables and analyse the data, such as finding the mean. This can be a useful tool and is valued by employers.
You are now ready to try to find the mean of some other frequency tables. Try to make your frquency tables presentable.
Also, see if you can round the mean to 2 decimal places, where needed. Hint, try Format Cells.
Create the following frequency tables and calculate the mean using Excel.
