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

[jazzii]

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.

 

[Pete_UK]

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