11. Exercise 1 - Logical functions

---

Prerequisites

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

Question

Let's look at the spreadsheet extract. In column B the start date of a certain process is recorded. In column C, the completion date of the 1^st operation is recorded and in column D the completion date of the 2^nd operation of the process is recorded. I assume that each of the 2 operations must take less than 20 days.

Write the appropriate formulas in columns E and F to generate the scores as follows:

• In E, the score is Bad if the 2 operations lasted more than 20 days. Otherwise, the score is Good.
• In F, the score is Bad if the 2 operations lasted more than 20 days. The score is Medium if one of the two operations lasted more than 20 days. Otherwise, the score is Good.

---

Indications Exercise 1 – Logical functions

---

1 - In E, the score is Bad if the 2 operations lasted more than 20 days. Otherwise, the score is Good.

Solution :

Formula in E2 :

=IF(AND(DAYS(C2,B2)>20,DAYS(D2,C2)>20),"Bad","Good")

Or :

=IF(AND(C2-B2>20,D2-C2>20),"Bad","Good")

Explanation :

The DAYS function is used to get the number of days between 2 dates. But, we can also use the difference between the dates, since in Excel, dates are numerical values called Serial Numbers and the difference between 2 successive days is 1. The formula :

DAYS(C2,B2)

is equivalent to :

C2-B2

The AND function returns the logical value TRUE, if the 2 conditions given as arguments are true. That is, when the 2 operations of the process have lasted more than 20 days.

The function IF has as its first argument the result of the AND function. When this result is true, then the function IF returns the text "Bad". Otherwise, the IF function returns the text "Good".

2 - In F, the score is Bad if both operations lasted more than 20 days. The score is Medium if one of the two operations lasted more than 20 days. Otherwise, the score is Good.

Solution :

Formula in F2 :

=IF(OR(C2-B2>20,D2-C2>20),IF(AND(C2-B2>20,D2-C2>20),"Bad","Medium"),"Good")

Or :

=IF(OR(C2-B2>20, D2-C2>20), IF(AND(C2-B2>20,D2-C2>20),"Bad","Medium"),"Good")

Explanation :

The 1^st argument of the IF function is :

OR(C2-B2>20,D2-C2>20)

• It will be evaluated to the logical value FALSE, if none of the 2 operations of the process lasted more than 20 days. In this case, the global result is the value in the 3^rd argument, i.e. the text "Good".
• It will be evaluated to the logical value TRUE, if one or both operations of the process lasted more than 20 days. In this case, the global result is the evaluation of the expression in 2^nd argument which is :

IF(AND(C2-B2>20,D2-C2>20),"Bad","Medium")

The value of this expression is :

• "Bad" when the 2 process operations lasted more than 20 days
• "Medium" when only one process operation lasted more than 20 days

---