Excel Related Exercise/Test (Vlookup, Sumif, Countif function), Are You Ready?

Excel Test

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

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


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

67 Feedbacks on "Excel Related Exercise/Test (Vlookup, Sumif, Countif function), Are You Ready?"

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.



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.