Nested IF function
We use the Nested IF function in Excel when there are multiple conditions we want to satisfy. It takes the testing process a step further compared to the simple IF function. The ripple effect of this is that because there are multiple tests, we also have multiple results.
= IF(T1, R1, IF (T2, R2, IF (T3, R3, IF (T4, R4, R5) ) ) )
Ti – this refers to the logical tests for each scenario, which in the case starts from T1 to T5.
Ri – the R element refers to the five different outcomes from the five tests…starting from R1 to R5.
The formula is suitable for testing more than a condition. In this case, there are logically more than one or two actions to be taken. It is then better to choose one of the many actions resulting from the test.
Let us consider the case of grades in schools, which have different ranges of scores assigned to them.
In the image below, we have the names and scores of students. We’ve also got the grade categories on different cells.
Figure 1. Excel showing the names and scores of students
To get the grade of a student, take Betty for instance, we’d need to use the formula for the nested IF function.
The function returns the grade of Betty as “B” based in the grade categories we already specified.
Figure 2: The nested IF function returns the student’s grade.
A simple drag and drop of the Grade column, starting with “C3”, returns the grades of all the students.
Figure 3. The nested IF function returns all the students’ grades.
You can make your formulas for the nested IF function easier to engage by breaking up lines.