Nested If And Conditions In Excel, A Real Life Example

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

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