Category Archive 'Excel function tutorials'

AverageIf Function Using Excel

Excel 2013, Excel function tutorials

I’ve heard from several of you that use Excel to manage large sets of data nad I know hata few of you must then get different metrics from that data depending on specific conditions. I’ve already covered Sumif quite a bit so today I thought I’d get into “averageif”. Let’s start with a large set of data:

This is an example of the top rankings in the WTA tennis tour. One typical thing that you could end up wanting to do is find information such as:

-What is the average # of tournaments that the top Russians have played compared with the top Americans. Here is How I would do it:

I’m certain that many of you can now imagine how this could be used.

Reader Question: Complex Nested If/And/IfNot Functions

Excel function tutorials

I received a question along with the following table:

The question is as follows:

“IF the Transaction is a ‘delete’, AND the Rate Category is selected, THEN match the delete rate from the corresponding Rate Category List into the Rate cell, IF the Transaction is an ‘add’, AND the Rate Category is selected, THEN match the add rate from the corresponding Rate Category List into the Rate cell.”

Again, it’s about trying to go step by step. the first case here is:

IF the Transaction is a ‘delete’, AND the Rate Category is selected, THEN match the delete rate from the corresponding Rate Category List into the Rate cell

So here, I’d need an “if and” condition that will check the value of the first column, look if cell 2 is empty and if both conditions are met, do a vlookup to find the correct rate. Simple? Here is what I came up with:

=IF(AND(A13=”Deletion”,B13<>“”),VLOOKUP(B13,RSUI_Deletes,2,FALSE),”")

The second part is:

IF the Transaction is an ‘add’, AND the Rate Category is selected, THEN match the add rate from the corresponding Rate Category List into the Rate cell.

Here I will use a similar function of course:

=IF(AND(A14=”Addition”,B14<>“”),VLOOKUP(B14,RSUI_Adds,2,FALSE),”")

I can then combine them together as follows:

=IF(AND(A13=”Deletion”,B13<>“”),VLOOKUP(B13,RSUI_Deletes,2,FALSE),IF(AND(A13=”Addition”,B13<>“”),VLOOKUP(B13,RSUI_Adds,2,FALSE),”"))

One last change I will make is to add an “Unknown” if the first column is filled but not the second by changing the formula to:

=IF(AND(A13=”Deletion”,B13<>“”),VLOOKUP(B13,RSUI_Deletes,2,FALSE),IF(AND(A13=”Addition”,B13<>“”),VLOOKUP(B13,RSUI_Adds,2,FALSE),”Unknown”))

The end result is:

As you can see what looked very complex ended up being rather simple:)

Reader Question: Nested If Condition

Excel function tutorials

One of the more recent questions that I got on the blog was the following:

I need some help.
I want to use an if statement for the following:
Quantity (d1)
Pk Qty (E1)
Total (F1)
UOM (AA1)

Formala Help to be entered in F1:
If pk qty = 0, then enter the value for d1 in F1, If Pk quantity is>0, then return D1*E1 unless UOM =FT, then enter D1

Let’s try ot resolve this one step at a time

First off, because I’m visual, I’ll put this into a spreadsheet:

Then, step by step:

“If pk qty = 0, then enter the value for d1 in F1″

=IF(E2=0,D2,”")

For the second part:

If Pk quantity is>0, then return D1*E1 unless UOM =FT, then enter D1

=IF(AA2=”FT”,D2,D2*E2)

Then, I can simply enter the 2nd part into the first formula:

The end result?

=IF(E2=0,D2,IF(AA2=”FT”,D2,D2*E2))

Once again, the key is doing this one step at a time, it’s that easy:)

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: