12. Exercise 2 - Logical functions

---

Prerequisites

You should read the Excel Functions course up to the chapter on Logical Functions before starting this exercise.

Question

We take the example from Exercise 4 - Calculation, Formulas and Series.

Normally you would fill the sheet with all the formulas and then have an input operator fill it with the values in the range B5:E19. It is advisable to lock the sheet by leaving only the range B5:E19 editable. Otherwise, the input operator could enter a value in a cell in columns F to M and the formula, which was there, would be lost.

For the technique to lock the sheet see the link: Excel How to lock cells ?

In the previous exercise we saw all the formulas for columns F to M. But there is a flaw. It is the "0" displayed as we can see in the extract below. On the other hand, the values displayed in cells G6 to I6 and k6 to M6 are wrong. They are unknown because the value of the score of the 2nd game for this player is not yet known.

The Excel function SUM ignores empty cells. However, this results in incorrect values for this example. It is therefore appropriate to display nothing when not all the values involved in a formula are filled in...

Add logical functions to enforce this.

---

Indications Exercise 2 – Logical functions

---

Columns F to I

In cell F5, use the formula :

=IF(B5="","",SUM($B5:B5))

That is, when cell B5 is empty, then F5 must also be empty.

Cell G5 must remain empty when cell B5 or cell C5 is empty. The formula is :

=IF(OR(B5="",C5=""),"",SUM($B5:C5))

In cell H5, use the formula :

=IF(OR(B5="",C5="",D5=""),"",SUM($B5:D5))

In cell I5, use the formula :

=IF(OR(B5="",C5="",D5="",E5=""),"",SUM($B5:E5))

Select the range F5:I5 and copy to the last line of the sheet.

NB. It is possible to write a single formula to cell F5 and copy it to columns F to I using the Excel function COUNTBLANK. This will be covered in a later exercise.

Columns J to M

In cell J5, use the formula :

=IF(F5="","",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.

---