Excel Sample of Multiple Nested If And Or

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

Today I wanted to show a quick example of a question that I received that is fairly easy to answer provided that we do it one step at a time. Here is the comment that was posted:

“Please Help me w/ this PROBLEM..
it gave me a hard time making a formula of this…

If Age is greater than 100 and less than 5, you are going to display “Age is out of range”. If Age is greater or equal to 5 but not greater than 12, you are going to display “You are a pupil”. If Age is greater or equal to 13 but not greater than 16, you are going to display “You are a highschool”. If Age is greater or equal to 17 but not greater than 22, you are going to display “You are a bachelor”. Lastly if the age is greater than 22 and less than 100, you are going to display “You may marry if you already have a job.”

The trick here is simply to go one step at a time. Let’s take the conditions one by one. The most difficult one is the first.

If Age is greater than 100 and less than 5, you are going to display “Age is out of range”

This is a simple example of a nested “if or” function (see the tutorial here)

=IF(OR(B2<5,B2>100),”Age is out of display”,””)

Then, it’sa simple a matter of replacing the “” by the appropriate condition, one step at a time.

“If Age is greater or equal to 5 but not greater than 12, you are going to display “You are a pupil”.

=IF(OR(B3<5,B3>100),”Age is out of display”,IF(B3<=12,"You are a pupil",""))

If Age is greater or equal to 13 but not greater than 16, you are going to display “You are a highschool”.

=IF(OR(B4<5,B4>100),”Age is out of display”,IF(B4<=12,"You are a pupil",IF(B4<=16,"You are a highschool","")))

If Age is greater or equal to 17 but not greater than 22, you are going to display “You are a bachelor”.

=IF(OR(B5<5,B5>100),”Age is out of display”,IF(B5<=12,"You are a pupil",IF(B5<=16,"You are a highschool",IF(B5<=22,"You are a bachelor",""))))

Lastly if the age is greater than 22 and less than 100, you are going to display “You may marry if you already have a job.”

=IF(OR(B6<5,B6>100),”Age is out of display”,IF(B6<=12,"You are a pupil",IF(B6<=16,"You are a highschool",IF(B6<=22,"You are a bachelor","You may marry if you already have a job"))))

Here is the final result!

As you can see, writing this from zero would have been rather difficult but by adding one condition at a time, it becomes much easier. You can download the spreadsheet here.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.