function - if
 

hi,
i m trying to do a commission statement for an agent and following are
the criterias based on profit margin:
Profit Margin upto 20% - 1% commission
Profit Margin from 21% to 30$ - 2% commission
Profit Margin from 31% to 35% - 3% commission
Profit Margin from 36% to 60%- 6% commission
Profit Margin above 61% - Error

Pls help me with the formula for this

Thanks
Raj


--
Raj Shah
------------------------------------------------------------------------
Raj Shah's Profile: http://www.excelforum.com/member.php...o&userid=27505
View this thread: http://www.excelforum.com/showthread...hreadid=470193

Code:
--------------------
=IF(profit <=20;"1%";IF(profit<=30;"2%";IF(profit<=35;"3%";IF (profit<=60;"6%";"Error"))))
--------------------


you have to replace profit by the specific Cell e.g. A2


Simon


--
moondark
------------------------------------------------------------------------
moondark's Profile: http://www.excelforum.com/member.php...o&userid=27390
View this thread: http://www.excelforum.com/showthread...hreadid=470193

Hi Raj

Assuming your profit margin is in C1 then
=1%+(C120%)*1%+(C130%)*1%+(C135%)*3%+(C161%)*-6%

Will give your result, but rather than Error, it will show 0% for any value
in excess of 61%
Copy down column having changed range to suit

Regards

Roger Govier


Raj Shah wrote:
hi,
i m trying to do a commission statement for an agent and following are
the criterias based on profit margin:
Profit Margin upto 20% - 1% commission
Profit Margin from 21% to 30$ - 2% commission
Profit Margin from 31% to 35% - 3% commission
Profit Margin from 36% to 60%- 6% commission
Profit Margin above 61% - Error

Pls help me with the formula for this

Thanks
Raj

many thanks
that was fantastic!


--
Raj Shah
------------------------------------------------------------------------
Raj Shah's Profile: http://www.excelforum.com/member.php...o&userid=27505
View this thread: http://www.excelforum.com/showthread...hreadid=470193