Today, I am following up on last week’s excel quiz where I challenged all of you to take part in a little test of your understanding of some key excel functions, **you can do it here if you have not done so**.

**Step #1-****Download this spreadsheet**

To remind you, here are the steps with the answers! This is what the spreadsheet looked like to start off:

**Step #2-Using one of the functions described, get the correct number in column “E”. It should be one formula that you can then drag down that will use the correct %. Then, using the chart at the bottom left and the same function, fill out column “F”**

=VLOOKUP(D4,$A$15:$B$17,2,FALSE)

=C4*VLOOKUP(D4,$A$14:$C$17,3,FALSE)

**Step #3-Calculate the amount to be financed (Selling price – down payment) in column G**

=C4-F4

**Step #4-Fill out the commission to be paid out to each agent**. It should be calculated as follows. They get paid only on the amount to be financed. They actually get paid:

*2.5% if that amount is over or equal to $200,000
1.5% if it is lower than $200,000*

=IF(G4>200000,F4*0.025,F4*0.015)

**Step #5-Using one of the formulas**, calculate the correct commission for each seller in cells B22-23

=SUMIF($B$4:$B$12,A22,$H$4:$H$12)

**Step #6-Using another one of the formulas**, count how many sells each agent made in C22-23

=COUNTIF($B$4:$B$12,A22)

**Step #7-The branch has an extra bonus where the bank manager will receive $10,000 for any loan that is over $250,000 with a loan term (amount to be financed) of 20 years or more AND an interest rate of 5% or more. Please enter a formula in I4 that you can drag that will give you the bonus amount for each loan.**

=IF(G4>250000,IF(AND(D4>=20,E4>=0.05),10000,0),0)

That’s it:) Here is what the answer looks like:

**You can also download the answer here:)**

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

**Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss**

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

## Adrian

In the commission file the formula is wrong.

It should read:

=IF(G4>200000,G4*0.025,G4*0.015)

Otherwise they will quit from sales.

## Joseph

Simplier IF function:

=IF(AND(G4>250000;E4>=5%;D4>=20);10000;0)

## serg

Thank’s for the exercise!!

## S

Yes the formula for commission is wrong. It should be IF(G4>=200000,2.5%*G4,1.5%*G4)

Why? Because

– They get paid only on the amount to be financed (which is column G and not F)

– 2.5% if that amount is over or equal to $200,000 (so equal to sign is required)

## fjr advisors llc

That is really attention-grabbing, You’re an overly skilled blogger. I have joined your feed and look ahead to searching for extra of your magnificent post. Additionally, I’ve shared your website

in my social networks

## ritika arora

excellent exercise!!!

## Sarah

Great exercise- best I’ve found so far! Thanks

## salahudheen

Can I have the question for this pls? I dont understand the commission calculation formula (sumif).

## Please Leave a Comment!