One of the more simple but recurring tasks in excel is trying to determine how many cells in a given range result in a given answer, which could be a number, or any other symbol. There are many different ways to get that result in excel but I personally think the “countif” function is the most efficient way of getting it done.

Suppose that you have a spreadsheet with a number of different columns and you’re trying to determine how many of the stocks in this spreadsheet in column “D” have “US” as their country of incorporation, I could do the following:

You would see that 473 of the 500 names

Simple enough? I would hope so! As always you can download the spreadsheet here

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.

I’ve already discussed the issues associated with working with dates in Excel. It’s much harder than it should be. Unfortunately, that is also true about working with time. In a way, I can understand that excel can’t always guess what we’re trying to do. It should be a bit better at it though. Yesterday, I got a question from a reader that a simple question. He needed to buy 1000 shares of Microsoft done evenly throughout the day. It’s more commonly known as a TWAP (time weighted average price). He was trying to easily see if the program had bought enough or perhaps too much at multiple points during the day.

I told him I’d build a simple spreadsheet but it proved a bit more difficult than expected. Why? It’s mostly about cell formatting. Here is what I built initially:

The first part is easy. My first task is finding the number of minutes between the start and end. I need that to determine what quantity needs to be executed depending on the time. I will use 2 functions to achieve this task. Basically, I need to translate the 6:30 into minutes.

How? I will add the number of hours x 60 to the number of minutes. Unfortunately, here is the result:

However, I then simply changed the format of that cell to numbers and got this:

So every minute I must execute the total qty/mins. In this case, it is 1000/390.

Then, I needed to determine for each time on the left how much time had lapsed since the start time. How? It’s a similar principle. For C5 I will use:

=MINUTE(B5)+HOUR(B5)*60-MINUTE($J$4)-HOUR($J$4)*60

However as you can see the result isn’t good:

Why? Because the time is not being treated correctly. So I will select the entire B column and right click for cell format, I then used this format:

I now have the number of minutes between that time and the start. I can now simply multiply that by the qty/minute and I’ll know how much I need to have executed:

As you can see, I also added the use of “Max” and “round”. The round is simply to avoid having decimals while I use the max because if I change my start time to 10:00 AM, I want to avoid having negative quantities.

You can of course download the spreadsheet here.