Nested If Condition – New Example

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
***************

Working with excel functions is not easy to learn and usually requires a decent amount of practice. Every once in a while, I try to publish some examples sent by some of you and here is the latest one that I got:

I want correct formula for following:

Following cells contains:
A1-1201 and above
A2-976 to 1200
A3-751 to 975
A4-601 to 750
A5-600 and above

I want a result in cell c3 of following
1. if b2 is >= 1200 = “A+”
2. if b2 is >= 976 = “A”
3. if b2 is >= 751 = “B”
4. if b2 is >=601 = “C”
5. if b2 is <600 = "D"

As is often the case, the trick here is not overcomplicating things. You can do one step at a time and get to the answer.

Step #1-Determine if the grade is A+

=if(b2>=1200,”A+”,”zzz”)

It’s important to understand this part. I’m simply looking to see if the grade deserves an A+. If not, I replace it with zzz until I can work out the other conditions.

Step #2-Determine if the grade is A. I start off with the previous formula:

=if(b2>=1200,”A+”,”zzz”)

And I replace “zzz” with a condition that will verify if the grade should be “A”

=if(b2>=1200,”A+”,”zzz“)
=if(b2>=1200,”A+”,if(b2>=976,”A”,”zzz”))

Can you see what I did? I replaced the bold part of the first formula by the bold part of the second. Thus, I can already see if the grade should be A+ or A. I will continue

=if(b2>=1200,”A+”,if(b2>=976,”A”,”zzz“))
=if(b2>=1200,”A+”,if(b2>=976,”A”,”if(b2>=751,”B”,”zzz”)“))

Then one more step:

=if(b2>=1200,”A+”,if(b2>=976,”A”,”if(b2>=751,”B”,“zzz”)”))
=IF(B2>=1200,”A+”,IF(B2>=976,”A”,IF(B2>=751,”B”,IF(B2>=601,”C”,”D”))))

That’s it:) Simple enough?

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

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.