< Go Back

Nested IF function example

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.

Formula

= IF(T1, R1, IF (T2, R2, IF (T3, R3, IF (T4, R4, R5) ) ) )

Arguments

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.

Explanation

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.

Example

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

Step 2

To get the grade of a student, take Betty for instance, we’d need to use the formula for the nested IF function.

= IF(B3<64,"F",IF(B3<73,"D",IF(B3<85,"C",IF(B3<95,"B","A"))))

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.

Step 3

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.

Notes

You can make your formulas for the nested IF function easier to engage by breaking up lines.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar