Use nested IF function to evaluate multiple condition

image_pdf

The IF function is very useful in excel to check whether certain condition is true or false in a given spreadsheet.

Syntax:

=IF ( logical_test, value_if_true, value_if_false )

Where,

logical_test – A value or expression which is tested to see whether it is true or false.

value_if_true – The value which is displayed if logical_test is true.

value_if_false – The value which is displayed if logical_test is false.

Example Using Excel's IF Function:

You can use IF function to get the results based on condition. If you want to display text string in results than write in double quotation (""). For example we can check the if the value in cell A1 is greater than 5 or not, accordingly it will show the result.

=IF (A1 > 5, "Greater than Five", "Less than Five")

If function

Some of the other operators which can be used are as follows:

<      Less Than

>=    Greater than Or Equal To

<=    Less than Or Equal To

<>    Not Equal To

You can also use nested IF to get results with different criteria.

Example: Consider we have the following data:

multiple if example

We want to display the following grades as per the Average of each student. Try to make algorithm as shown below to easily use nested IF. Simply use IF formula again and again in the "Value if False" part of IF formula.

A -If the student scores 80 or above

Condition: =IF (B7>=80, "A",… ) 

B -If the student scores 60 to 79

Condition: =IF (B7>=80, "A", IF (B7>=60, "B"…). We can eliminate the <=79 part as the first condition (B7>=80) will overwrite it.

If the student scores 45 to 59

Condition: =IF (B7>=80, "A", IF (B7>=60, "B", IF (B7>=45, "C",…)

D -If the student scores 30 to 44

Condition: =IF (B7>=80, "A", IF (B7>=60, "B", IF (B7>=45, "C", IF (B7 >=30, "D",…)

FAIL- If the student scores below 30. As this is the last condition, we don't need to use IF function further.

=IF (B7>=80, "A", IF (B7>=60, "B", IF(B7>=45, "C", IF(B7 >=30, "D", "Fail" ) ) ) )

Note: Make sure that you end the formula with each bracket closed. It may show an error if you forgotten it.

Write this formula in cell B16 and then drag the formula in C7:E7.

=IF (B7>=80, "A", IF (B7>=60, "B", IF(B7>=45, "C", IF(B7 >=30, "D", "Fail" ) ) ) )

Complex If formula

Result: Nested IF evaluate each IF formula and gives the result value based on condition.

If you find this useful, you can stay connected with us on facebook to get more amazing tips and tricks of Microsoft excel.

3,885 total views, 1 views today

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>