Sponsored Fun Run

Spreadsheet Assignment

You will create a spreadsheet to display the money raised in sponsorship by competitors in a fun run.

Part One – Creating the spreadsheet

Start Excel and, if necessary, create a new blank worksheet. Enter text in the relevant cells to lay out your sheet like this:

Runner / Miles Completed / Sponsorship Per Mile / Total Earned
Mr Adamson / 9
Mrs Barnes / 5
Miss Carr / 16
Miss Dunst / 15
Mr Everlong / 13
Sir Frederick / 9
Average
Total

Part Two – Formatting the spreadsheet

Select the top row (the titles “Runner” etc.) and make it bold.

Select the “Average” row and change it to a Number with 2 decimal places.

Select the third and fourth columns and change them to Currency with the £ symbol and 2 decimal places.

Finally enter the “Sponsorship Per Mile” values as indicated below:

Runner / Miles Completed / Sponsorship Per Mile / Total Earned
Mr. Adamson / 9 / £2.00
Mrs. Barnes / 5 / £3.00
Miss. Carr / 16 / £4.00
Miss. Dunst / 15 / £3.50
Mr. Everlong / 13 / £2.00
Sir. Frederick / 9 / £5.00
Average
Total

Part Three – Creating the formulas

For the “Total Earned” column you must multiple the “Miles Completed” cell by the “Sponsorship Per Mile” cell. For example the first formula would be =B2*C2

In the correct cells enter the formulas to average and total the three columns.

To calculate the average you must enter the formula =AVERAGE(range) where “range” is the cell range you wish to use as the source figures. For example =AVERAGE(B2:B7)

To calculate the total you must enter the formula =SUM(range) where “range” is the cell range you wish to use as the source figures. For example =SUM(B2:B7)

Your results will be:

Average / 11.17 / £3.25 / £36.75
Total / 67 / £19.50 / £220.50

Part Four – Extension exercises

  1. It seems that there has been an error when entering the information and Mrs. Barnes actually completed 15 miles. Update this on your spreadsheet.
  2. The total money raised is to be split between two charities, one getting 40% of the total and the other getting the remaining 60%. Create two new cells that calculate these two new values.

Hint: You can calculate a percentage by simply multiplying a cell by the percentage – just make sure you put in the symbol! (e.g. B4*40%)

  1. The organisers of the fun run are wishing to see your spreadsheet but you feel it is too plain to show them as it is. Change the colours, borders or anything else you can find to make the spreadsheet more attractive.