Nested If And Conditions In Excel, A Real Life 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
***************

You might be familiar with loyalty programs that are run by hotel chains. They generally rank all their users by category depending on how much the user was at the hotel in the past 12 months. How could I calculate the category of a visitor depending on his nights stayed and/or number of stays? Let’s give it a try:

There are 2 different ways that the hotel would determine categories. Either someone needs to have 40 stays AND 80 nights to get the “Platinum” category or it could be either one. Let’s start off with the case where someone would need to have both. I’ll start off by simply trying to determine if the user has enough of both:

=IF(AND(H5>B5,I5>C5),”Platinum”,”Not”)

As you can see, it’s fairly simple. Adding more categories is not more difficult, but it can be confusing. You could simply write them one by one and then replace the “Not” by that new condition. Here is an example for “Gold”:

=IF(AND(H5>B6,I5>C6),”Gold”,”Not”)

I will add this into the first formula which will give me:

=IF(AND(H5>B5,I5>C5),”Platinum”,IF(AND(H5>B6,I5>C6),”Gold”,”Not”))

I can do this for any number of categories and it will work just fine. It’s a much easier way to manage multiple levels of conditions. Here is the final formula:

=IF(AND(H5>B5,I5>C5),”Platinum”,IF(AND(H5>B6,I5>C6),”Gold”,IF(AND(H5>B7,I5>C7),”Silver”,IF(AND(H5>B8,I5>C8),”Bronze”,”Does Not Qualify”))))

And what it looks like with a few more users:

You can also download the spreadsheet here

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

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


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

3 Feedbacks on "Nested If And Conditions In Excel, A Real Life Example"

telegarger steam gratuit

Hey there! I’m at wrk surfving around your blog fom my new iphone!

Just wanted to say I loive reading throughh your blog and look foirward to all your
posts! Keep up the excellent work!



Addmefast bot no Survey

It’s actually very complicated in this busy life to listen news on TV,
therefore I only use the web for that reason, and obtain the
most recent news.



code psn gratuit avatar

I_m not that much of a online readr to be honest but yopur blogs
really nice, keep it up! I’ll go ahead and bookmark your website to come back later.
Many thanks



Comments

Please Leave a Comment!





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