3. Exercise 1 - 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
Let's go back to the example of Exercise 2 - Formatting. The values in column F are all inferred from other values on the sheet.
Write the formulas allowing to calculate the values of the column F while using of course the technique of Autofill when it is convenient:
Indications Exercise 1 – Calculation, Formulas and Series
1 - Formula in cell F5 : « =D5*E5 »
2 - The formula must be copied up to cell F20. But because of the cell fusions at the A12:F12 and A17:F17 ranges, you cannot do this by moving the mouse pointer over to the F20 cell. Proceed as follows:
- Click in cell F5 and click on the Copy button
- Click in cell F13 and click on the Paste button
- Click in cell F18 and click on the Paste button
- Copy the formula by moving the mouse pointer from cell F5 to cell F11
- Copy the formula by moving the mouse pointer from cell F13 to cell F16
- Copy the formula by moving the mouse pointer from cell F18 to cell F20
3 - The formula in cell F21 is: "=SUM(F5:F11,F13:F16,F18:F20)". Here's how to get this formula:
- Enter "=" using the keyboard
- Select the SUM function at the Name Box. The Function Arguments dialog box is open
- Select the range F5:F11
- Click in the Number2 field of the dialog box and select the range F13: F16
- Click in the Number3 field of the dialog box and select the range F18: F20
4 - The formula in cell F22 is "=F21*20%"
5 - The formula in cell F23 is "=F21+F22"