- Level: AS and A Level
- Subject: Maths
- Word count: 1129
Using a Spreadsheet to Analyse Statistics
Extracts from this document...
Introduction
Using a Spreadsheet to Analyse Statistics
In this tutorial, you will enter data about the number of goals scored by various football teams and you will carry out statistical analysis on the data.
1. Entering the Data
1. Enter the data as shown below.
2. Some of the columns will not be wide enough. To remedy this, re-size the columns or double-click on the intersection between the column headings. See below:
Double-click here
3. Highlight the names of the teams and click the Bold button on the Excel toolbar.
2. Using the Format Painter
1. Make the heading “Football Club” blue, bold and change the font to Brush Script (or another font of your choice). Make the font slightly bigger.
2. Make sure cell B2 is still selected and double-click the Format Painter button
Format Painter Button
3. Then click once on cells C2 and E2, which also contain column headings. Adjust the width of the columns again, as necessary.
4. Deselect the Format Painter button.
The Format Painter
Middle
2. Click in Cell F4
3. Start the Function Wizard by clicking the button on the toolbar:
4. In the Function Category select Statistical and for Function Name select Max.
5. Click OK
6. Enter Goals
7. The highest number of goals scored (46) should now be displayed in cell F4
In cell F5 you want to have the lowest number of goals displayed. You can achieve this by using the MIN function:
8. Click in cell F5
9. Start the Function Wizard
10. Select the MIN function from the Statistical category and click OK
11. Enter the range Goals
12. Click OK and the lowest number of goals scored (9) is displayed in cell F5
5. The Average Function
In cell F6 you want Excel to display the average number of goals scored. You will do this by using the Average function.
1. Click in cell F6
2. Start the Function Wizard
3. Select the Average function from the Statistical category
4. Click OK
5. Enter the range Goals
6. Click OK and the average number of goals scored should be displayed in F6
7.
Conclusion
Hint: Select Name/Define from the Insert menu
6. See if you can get Excel to automatically display a “Best Team” and a “Worst Team”, as follows:
Hint: the solution to this one is extremely simple!
7. Use the Rank Function to show the position of each team in the table.
Hint to get you started: the formula in cell A4 is =RANK(C4,goals,0)
8. Create some calculation formulas to show the number of goals scored by London teams, Manchester teams, teams from Merseyside, etc.
9. You can automatically sort the list of teams by creating a macro button, as follows:
- You need to view the Forms Toolbar (View/Toolbars/Forms)
- Add a button and label it “Sort List”
- From the Tools menu, select Macro/Record New Macro
- Give the Macro a name and start recording
- Sort the list and then stop the recording
- Click the Right Mouse Button on the Sort Button
- Assign the macro you created to the button
- Finally, test your button and make sure it works
10. How else could this spreadsheet be developed? Use your imagination!
Page of
This student written piece of work is one of many that can be found in our AS and A Level Core & Pure Mathematics section.
Found what you're looking for?
- Start learning 29% faster today
- 150,000+ documents available
- Just £6.99 a month