4. Exercise 2 - 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
Consider the following excerpt from the spreadsheet:
Reproduce this excerpt from the spreadsheet and fill in the values in columns H and I and in rows 11 and 12 using appropriate formulas and using the technique of Autofill appropriately.
The extract after filling in the values:
Indications Exercise 2 – Calculation, Formulas and Series
1 - Formula in cell H4: "=SUM(B4:G4)"
2 - Copy the formula to cell H10
3 - Formula in cell B11: "= SUM(B4:B10)"
4 - Copy the formula to cell H11
5 - In cell I4, you can use the formula "=H4/H11" and use the Percent style. But, when you do the copy to the cells at the bottom, you find errors with the mention "#DIV/0!" Which means "Error dividing by zero".
To find the source of the error, click on cell I6 for example and look at the formula displayed on the Name Box. The displayed value is "=H6/H13", while H13 does not contain any value, this is why the generated error is "Error dividing by zero".
Remember that when copying downward vertically, Excel increments whatever it finds as a row number, whereas we want to keep the H11 constant in the formulas when copying. The solution is to put a "$" sign in front of the 11 in the starting formula.
So, use in cell I4 the formula "=H4/H$11". Use the copy back to cell I11, and you will no longer have any errors.
6 - In cell B12, use the formula "=B11/$H11" use the copy up to cell H12.
Notice that I have put the sign "$" in front of the H. On the other hand in cell I4, I put the sign "$" in front of the 11. Indeed, the formula entered in B12 will be copied horizontally and Excel would increment in this case the H, if there was no "$" sign in front of it.