Excel Related Exercise/Test (Vlookup, Sumif, Countif function), The Answer

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

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