Ask a question Report

Commission Comparison table

John - Latest answer on May 10, 2012 08:43PM
Hello,
I have a commission table that is configured as such:
A B C
Monthly Hardware Revenue Performance Bonus Range Commission
0.0 99.9 0.00%
28 100.0 124.9 2.00%
29 125.0 149.9 2.10%
30 150.0 174.9 2.20%
31 175.0 199.9 2.30%
32 200.0 200.0+ 2.50%
How do I write a formula that will compare a calculated performance (Cell H21) to the chart above? I want it to return the proper commission level from column C?
Thanks!
John
Read more 
Answer
+0
moins plus
What are the criteria for the commission? How do you calculate it?
John- May 2, 2012 08:29AM
The volume of business is totaled automatically into cell D13. Then I would like to compare that to the chart I posted. Unfortunately the format did not stay clear when I posted it so let me explain it.

The chart has three columns, A,B, & C. A is the low value to check, B is the high value to check, C is the percent of commission due.

Rows 27 - 32 indicate the ranges, with row 27 being from 0 to 99.9%, which pays 0% Bonus. Row 28 being 100.0 to 124.9%, paying 2.00%, and so on.

I then want to calculate in cell H21 the value from D13* the proper value.

Thanks for your help.

JK
Reply
rizvisa1 4302Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014Last seen - May 2, 2012 06:07PM
What version of office you are using ?
Reply
rizvisa1 4302Posts Thursday January 28, 2010Registration date ContributorStatus December 6, 2014Last seen - May 10, 2012 08:43PM
try some thing like this

=IF(d13 = "", "",(if (d13 <0, 0, vlookup(d13, a:a,3))) * d13)
Reply
Add comment
This document entitled « Commission Comparison table » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.