6. Exercise 4 - Calculation, Formulas and Series
Prerequisites
It is advisable to read the Excel 2016 Course up to the 6th chapter Use of calculations before starting this exercise.
Question
The following sheet should be used to record the points won by players in each of the four games.
In columns F to I the scores achieved at the end of each game should be calculated. For example in cell H5 it is the sum of the values of cells B5, C5 and D5.
In columns J to M, the percentages achieved in relation to the final score recorded in cell B2 should be calculated.
You are asked for the appropriate formulas to calculate the values in columns F to M.
Filled in sheet :
Indications Exercise 4 – Calculation, Formulas and Series
1 - Columns F to I
In cell F5 is the value in B5, the formula is :
= B5
In cell G5 it is the sum of the values in cells B5 and C5. We can write the formula :
= B5+C5
Or
= SUM(B5:C5)
In cell H5 it is the sum of the values in cells B5, C5 and D5. We can write the formula :
= B5+C5+D5
Or
= SUM(B5:D5)
In cell I5 it is the sum of the values in cells B5 to E5. We can write the formula :
= B5+C5+D5+E5
Or
= SUM(B5:E5)
Then select the cell range F5:I5 and copy to the last row of your sheet.
So we have written 4 formulas, but we can write one formula and copy it to columns F to I. The formula in F5 is :
=SUM($B5:B5)
In F2, it is the sum of the range B5:B5 containing a single cell, so the SUM function returns the value of that cell.
Then I put the $ in front of the "1st B" so that it remains constant when copying the formula and I didn't put a $ sign in front of the "2nd B" so that it increments when copying.
Copy this formula to cell I5 and release the mouse pointer. Keep the cell range F5:I5 selected and copy to the last row of your sheet.
2 - Columns J to M
Write in cell J5 the formula :
=F5/$B$1
Copy the formula to cell M5 and release the mouse pointer. Keep the cell range J5:M5 selected and copy to the last row of your sheet.
NB. The resulting sheet has some imperfections which we will deal with in later exercises.