Find value within a range and extrapolating....(tiered commission)
 

Hi, I have the following problem.

Data :

Profit Commission
<100 50
101-200 150
201-250 300

However, the commision is not a flat rate but should be given
proportionately within the profit range. E.g. if Net profit is 240, then
commission should be ((300-150)/(250-200))*(240-200)+150 = 270

The formula must be dynamic such that the net profit is variable and once it
changes, the formula has to look up the table to compute the proportionate
commission.

Find value within a range and extrapolating....(tieredcommission)
 

Have a look at this site:

http://www.mcgimpsey.com/excel/variablerate.html

for a couple of examples of what you want to achieve.

Hope this helps.

Pete

On Sep 18, 11:58*am, jazzii wrote:
Hi, I have the following problem.

Data :

Profit * * Commission
<100 * * * 50
101-200 *150
201-250 *300

However, the commision is not a flat rate but should be given
proportionately within the profit range. E.g. if Net profit is 240, then
commission should be ((300-150)/(250-200))*(240-200)+150 = 270

The formula must be dynamic such that the net profit is variable and once it
changes, the formula has to look up the table to compute the proportionate
commission.