• Join over 1.2 million students every month
  • Accelerate your learning by 29%
  • Unlimited access from just £6.99 per month
Page
  1. 1
    1
  2. 2
    2
  3. 3
    3
  4. 4
    4
  5. 5
    5
  6. 6
    6
  7. 7
    7
  8. 8
    8
  9. 9
    9
  10. 10
    10
  11. 11
    11

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.  

image03.jpg

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:

image04.jpgimage00.png

Double-click here

3. Highlight the names of the teams and click the Bold button on the Excel toolbar.  

image14.jpg

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

image18.jpgimage01.png

                                     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.  

image19.jpg

4. Deselect the Format Painter button.

The Format Painter

...read more.

Middle

C4:C12)

2. Click in Cell F4

3. Start the Function Wizard by clicking the button on the toolbar:

image07.jpg

4. In the Function Category select Statistical and for Function Name select Max.

image05.png

5. Click OK

6. Enter Goals

image06.png

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

image07.jpg

10. Select the MIN function from the Statistical category and click OK

image08.png

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

image07.jpg

3. Select the Average function from the Statistical category

image09.png

4. Click OK

5. Enter the range Goals

6. Click OK and the average number of goals scored should be displayed in F6

7.

...read more.

Conclusion

F9 increased to 10?  Why not?  This is going to take some thought on your part!

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:

image15.jpg

Hint: the solution to this one is extremely simple!

7. Use the Rank Function to show the position of each team in the table.  

image16.jpg

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

image17.jpg

10.  How else could this spreadsheet be developed?  Use your imagination!

Page  of

...read more.

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

Not the one? Search for your essay title...
  • Join over 1.2 million students every month
  • Accelerate your learning by 29%
  • Unlimited access from just £6.99 per month

See related essaysSee related essays

Related AS and A Level Core & Pure Mathematics essays

  1. Marked by a teacher

    The Gradient Function

    5 star(s)

    I have noticed that the gradient is always equal to 9x�. I predict that when x = 5, the gradient will be equal to 225. 5 125 375 225 My theory was correct. Binomially expanding this, yet again, will allow me to get to the root of the equation, and see whether it is correct using algebra.

  2. Marked by a teacher

    Estimate a consumption function for the UK economy explaining the economic theory and statistical ...

    3 star(s)

    Therefore, the higher the R^2 (0?R^2?1), the closer the estimated regression equation fits the sample data. In this case, R^2 is 0.99624, which very close to unity. It indicates that the absolute equation fits the data very well according to R^2.

  1. Estimate a consumption function for the UK economy explaining the statistical techniques you have ...

    However, during the 1950s there appeared to be a discrepancy between the consumption function estimated from long run, short run and cross-section series of data. It also failed to explain some of the more interesting features of aggregate consumer behaviour and failed to predict certain periods of sharp fall in

  2. Experimentally calculating the wavelength of an He-Ne laser by means of diffraction gratings

    Also, the distances between the central beam and the first order fringe to the left and the first order fringe to the right seem to be equal. This is the same for the second and third order fringes. However, there seems to be slightly more space between the first order

  1. Investigation of the Phi Function

    m n ?(mn) ?(m) x ?(n) Does ?(mn)= ?(m) x ?(n) 1 2 ?(2) = 1 1 x 1 =1 Yes 1 3 ?(3) = 2 1 x 2 =2 Yes 1 4 ?(4) = 2 1 x 2 =2 Yes 1 5 ?(5) = 4 1 x 4 =4 Yes 1 6 ?(6)

  2. Math Portfolio Type II - Applications of Sinusoidal Functions

    3. Use mathematical terms, such as stretch and translation, to describe how the graph of y = sin x has been transformed to obtain the graph of the sine function representing time of sunrise as a function of day number, n, for Toronto.

  1. Estimate a consumption function for the UK economy explaining the economic theory and statistical ...

    These factors are obviously of importance in predicting consumption and so they need to be incorporated into our equation. There is one small change to the equation that needs to be made before investigating the other factors that affect consumption.

  2. The open box problem

    is 1. I will now draw a graph that will also show the maximum amount for x and to verify that x is 1 and that the greatest volume possible is 16. The graph shows also shows that the maximum volume is about 16 and x is 1.

  • Over 160,000 pieces
    of student written work
  • Annotated by
    experienced teachers
  • Ideas and feedback to
    improve your own work