Category Archive 'Excel function tutorials'

Calculate Commission Structure With Nested If Conditions

Excel function tutorials

Today I received a question from a reader who had purchased our Excel ebook, it’s a question that comes back fairly frequently so I thought I’d give a public answer. I’ve done similar examples but it’s always helpful to do it another time. The problem is fairly easy:

****************************
I am trying to figure out how to calculate a commission structure based on a range of dollar amts.

Say my gross profit is $10000

My range looks like this:

1-2500 5%
2501-5000 10%
5001-25000 15%
25000+ 20%

I’m guessing I can do this with IF function, but I can’t seem to nail down the syntax right.
****************************

There are a hundred different ways to do this but here is what I will do. I’ll start the opposite way that you would typically expect. First, I want to know the commission on sales of over $25,000. Basically, the vendor would earn:

5% on $2500 = $125
10% on $2500 ($5000-$2500) = $250
15% on $20,000 ($25,000-$5000) = $3000

So the vendor would earn $125+$250+$3000+20% (amount – $25,000) or $3375+ 20%

if the sales is in cell A1, then I’d get:

=IF(A1>25000,3375+0.2*(A1-25000),0)

Makes sense? That was one of the 4 scenarios. Now, I’ll move to a vendor that would sell between $5001 and $25000. He would generate a commission of:

5% on $2500 = $125
10% on $2500 ($5000-$2500) = $250

So the vendor would earn $125+$250+15% (amount – $5,000) or $375+ 15%

=IF(A1>5000,375+0.15*(A1-5000),0)

If I combine both together, I get:

=IF(A1>5000,375+0.15*(A1-5000),IF(A1>25000,3375+0.2*(A1-25000),0))

Now, I’ll move to a vendor that would sell between $2501 and $5000. He would generate a commission of:

5% on $2500 = $125

So the vendor would earn $125+10% (amount – $2500)

=IF(A1>2500,125+0.1*(A1-2500),0)

If I combine everything together, I get:

=IF(A1>5000,375+0.15*(A1-5000),IF(A1>25000,3375+0.2*(A1-25000),IF(A1>2500,125+0.1*(A1-2500),0)))

Then, I can simply replace the 0 (which is for cases where the sales are less than $2500) by 0.05*sales

=IF(A1>5000,375+0.15*(A1-5000),IF(A1>25000,3375+0.2*(A1-25000),IF(A1>2500,125+0.1*(A1-2500),A1*0.05)))

I dragged the formula and got a few examples:

You can also download the spreadsheet here

Vlookup+IsError Compbination For Baby Names

Excel function tutorials

One of the very real examples that I’ve had in the past few months is trying to find baby names which as you can imagine means looking at a bunch of lists, discussing, etc. My wife and I have a few different “criterias” and one of them is having a name is would also be “fairly common” in both the UK and Canada where we might end up living at some point.

So I downloaded the list of the top names used in 2013 in all 3 countries as you can see here. I’ll start by looking at all of the US names to see if they are part of the top 100 names in both Canada and the US. How?

Let’s start with the first name, I’ll simply add next to it:

=VLOOKUP(A2,E:F,2,FALSE)

Now I’d like to replace the 0 by 1 and the #N/A by 0… so I’ll do:

=IF(ISERROR(VLOOKUP(A2,E:F,2,FALSE)),0,1)

Then I’ll simply add the reference to Canada as well:

=IF(ISERROR(VLOOKUP(A2,E:F,2,FALSE)),0,1)+IF(ISERROR(VLOOKUP(A2,G:H,2,FALSE)),0,1)

To make it more clear, I’ll then add a condition:

=IF(IF(ISERROR(VLOOKUP(A2,E:F,2,FALSE)),0,1)+IF(ISERROR(VLOOKUP(A2,G:H,2,FALSE)),0,1)=2,”Yes”,””)

Then I could add a filter to get the list of names that fit the critera:

How To Generate A Random Number In Excel

Excel function tutorials

It’s a rather simple task but one that we often need to perform; being able to generate a random number. There are 2 different ways to get this done in excel.

#1-For a simple request such as: “Give me a number between 1 and 10”?

You can simply use the function randbetween:

=RANDBETWEEN(1,10)

Then, anytime you want to generate a new number, you’d press “F9” on your keyboard (refresh)

#2-If you’re looking for something a bit more sophisticated, you could use the function “rand” which would give you a number between 0 and 1 with (decimals)

So if i was looking at a number between 1 and 10 but wanted to include decimals I could do:

=10*rand()

Simple enough?

IS Functions

Excel function tutorials

One of the functions that I’ve used several times on this blog is the “IsError” function where I will test to see if a specific cell has an “error”. Why? For example if I use the vlookup function and I cannot find the “value”, then I’d prefer seeing some other message rather than a #Value

There are many other types of “IsFunctions” that will either return “True” or “False” which can be very useful. Here are a few:

ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)

For example, if I have a database of hundreds of lines and want to be sure that a specific field is not blank, this would be a good way to do it. For example:

Then, I could transform that data into a number by doing the following:

=IF(ISBLANK(B2),1,0)

As you can see, these type of functions are great to add control tests

Using The Excel Count Function

Excel function tutorials

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