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

In my previous post, I started off from one column and used different functions such as left, right and mid to separate my data into columns that will be easier to work with. when that is done, you will be able to look through all of the data and see how things look, what kind of exceptions came in, etc.

One data that happened in my case is that I could have a few cities where the data had a neighborhood that was specified. Just take a look:

y

This might be something that happens all over my spreadsheet and could cause different issues. I only want the exact city name to appear because that is what I’d use for stats, to send out mail, etc. How can I get rid of it? There are 2 main options:

#1-Adjusting the initial formula (depending on what the type of error is, this could certainly work)

#2-Adding Exception Management to that formula: This is the road I will take here and while it might *look messy, it will be done fairly easily. So as you know, my current formula is:

=MID(A2,B2+3,C2-B2-3)

I first want to know if there is a “(” in that formula. I will use the “find” function here:

=FIND(“(“,MID(A2,B2+3,C2-B2-3))

Here, I either get a number or an error. I will then simply manage that error by adding an “if” function:

=IF(ISERROR(FIND(“(“,MID(A2,B2+3,C2-B2-3))),0,1)

Basically, if there is an error, it means no adjustment is needed so I will replace the 0 in that formula by what I had.

However, if I do not get an error, I will adjust that answer by only capturing what is left of that “(“:

So I would get the city only:

=IF(ISERROR(FIND(“(“,MID(A2,B2+3,C2-B2-3))),MID(A2,B2+3,C2-B2-3),LEFT(MID(A2,B2+3,C2-B2-3),FIND(“(“,MID(A2,B2+3,C2-B2-3))-1))

I know it looks messy but take the time to look at the formula. It resolves my issue and would “clean” up my data for all such cases.

Here is the new result:

You can also download the spreadsheet

On this website, we use many different types of functions but it’s clearly important to also use real life examples when trying to use functions. Recently, I was asked by a reader to help out with a task that I assume many others are trying to do. When you have a type of data such as:

And you need to reclassify this into different columns, it can be a challenge. I could not use the addresses from that person’s file (obviously) so I got a few of my own and decided to play around with these.

Suppose I had that list and needed to get the address into different columns (there could be many different reasons for doing this). How would I do it? Imagine that I had 1000 or 10000 such lines? I’ll give an example that will hopefully give you an idea how.

Step #1 – The most critical is step (by far) is understanding how the data is presented.

In this case I have the street address followed by ” · “, then the city, followed by “,”, the postcal code and then a space and the country. I can work with this. In some cases, the data will not be as uniform which provides more challenges but can still be done.

Step #2 – Find those “markers”. I’ll start by looking for ” · “. How? The find function:

=FIND(” · “,A2)

Then, I’ll find the next one:

=FIND(“,”,A2,B2)

From there I should be able to proceed:

Step #3 –

Street address would be:

=LEFT(A2,B2-1)

I would then use the “mid” for the city:

=MID(A2,B2+3,C2-B2-3)

and the same for the postal code:

=MID(A2,C2+2,7)

Finally, I could use that same function for the country by assuming the postal code always has 7 characters:

=MID(A2,C2+10,20)

The result?

You can download the spreadsheet here.