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.