Today, after helping out a reader, it got me thinking about a new feature that could be added to this blog, excel exercise or tests. I will be giving one such exercise today and then publishing the answer next week. Obviously, readers that come here are at all kinds of different levels so it would be a challenge to have an exercise that would work for everyone. This is an exercise that tests your knowledge of the **vlookup**, **sumif** and **countif functions** as well as a special question that will require you to use a **nested if & and function**.

I would of course invite you to write comments and questions in the comments section of this post. However, please refrain from posting the entire solution:) If you do have it, simply say so, I will communicate with you to get the file by email:)

Ready for the challenge? If so, start here:

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

**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”**

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

**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

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

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

**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.**

That’s it:) Best of luck:) The answer will be published one week from now:)

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

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

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

## yasser

Hi can i have the answers for this test please to ensure that I have done it correctly

thank you

## manish bharti

please send the answer of above question

## buggiesandprams.com

Simply want to say your articdle is ass surprising.

The clarity inn your post is simply nice and i could assume

you are an expert on this subject. Well with your permission allow me tto grab your feed to keep up to date with forthcoming post.

Thanks a milllion and please keep up the rewarding work.

## Anita

Please send me the solution. Thanks!

## Emma

Please send me the answer to this bank spreadsheet. Thanks!

## Buckhead

2.

Part 1

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

Part 2

=VLOOKUP(A4,$A$4:$D$12,3,FALSE)*(VLOOKUP(D4,$A$15:$C$17,3,FALSE))

3.

=C4-F4

4.

=IF(G4>=200000,G4*0.025,IF(G4250000,E4>=0.05)),10000,0)

## Buckhead

I posted the answers and it wouldn’t show all of the answers. The answer you see for 4. is actually for is actually for the final question with some parts omitted. Oh well I tried

## no wei

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

HOW??

## Talal

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

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

## uday

please post ans

## uday

pls post ans

## basu

Please send me the answer to this bank spreadsheet. Thanks!

## Rory Bellow

Please send the answers! Thanks!

## Anonymous Bob

STEP 6

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

STEP 7

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

## Rashida Islam

Hey, could you please post an answer to the final question? Thank you!

## Sandilya

Hey Could you please let me know how to do the step 7

## Miss Naw Poe Lu

Now I have the test file with the answers myself.

## Richard White

Can I have the answers?

Can I have the answers?

Can I have the answers?

Jeez, can’t you flock of stupid sheep find anything else to say?

## Rahul Gupta

Step # 2 vlookup(D4,$A$14:$C$17,2,FALSE)

Step # 2 vlookup(D4,$A$14:$C$17,3,FALSE)

Step # 3 C4-F4*C4

Step # 4 if(G4250000,E4>=5%,D4>=20),10000,””)

## Rahul Gupta

Step # 5 Sumif($B$4:$B$12,A22,$H$4:$H$12)

Step # 6 Sumif($B$4:$B$12,A22)

Step # 7 if(And(G4>250000,E4>=5%,D4>=20),10000,””)

## Rahul Gupta

Step # 4 if(G4<200000,1.5%*G4,2.5%*G4)

Corrected

## Rahul Sharma

Bank Example

Customer Seller Agent Selling Price Loan Term Interest Rate Down Payment Amount to be Financed Commission Bonus

Sterfield Allan $2,58,900 30 5.25% 25.00% $1,94,175 $2,913 No Bonus

Sceria John $3,28,950 15 4.50% 15.00% $2,79,608 $6,990 No Bonus

Subensklee Allan $1,98,000 15 4.50% 15.00% $1,68,300 $2,525 No Bonus

Sritzen John $1,78,350 30 5.25% 25.00% $1,33,763 $2,006 No Bonus

Sango Allan $3,33,000 30 5.25% 25.00% $2,49,750 $6,244 No Bonus

Satt John $7,68,650 20 5.00% 20.00% $6,14,920 $15,373 $10,000

Sro Allan $3,58,000 15 4.50% 15.00% $3,04,300 $7,608 No Bonus

Sweitzer John $4,58,000 20 5.00% 20.00% $3,66,400 $9,160 $10,000

Ster Allan $1,68,900 30 5.25% 25.00% $1,26,675 $1,900 No Bonus

$54,718

Loan Term Interest Rate % Required Down Pmt

15 4.50% 15%

20 5.00% 20%

30 5.25% 25%

Sellers Commission $ #

John $33,529.63 4

Allan $21,188.50 5

$54,718.13