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

Answer

Mehedad 22Posts Thursday April 19, 2012Registration date April 16, 2013Last seen May 1, 2012 11:45PM

What are the criteria for the commission? How do you calculate it?

Add comment

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

rizvisa1 4247Posts Thursday January 28, 2010Registration date ContributorStatus June 16, 2013Last seen - May 2, 2012 06:07PM

What version of office you are using ?

John - May 9, 2012 06:12PM

2010

rizvisa1 4247Posts Thursday January 28, 2010Registration date ContributorStatus June 16, 2013Last seen - May 10, 2012 08:43PM

try some thing like this

=IF(d13 = "", "",(if (d13 <0, 0, vlookup(d13, a:a,3))) * d13)

Reply to this topic

Ask a question

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.

Commission Comparison table

Not a member yet? sign-up, it takes less than a minute and it's free!

Not a member yet?

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