Board index » Excel » function - if

Excel22
hi,

i m trying to do a commission statement for an agent and following ar

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

Ra



--

Raj Sha

-----------------------------------------------------------------------

Raj Shah's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2750

View this thread: http://www.excelforum.com/showthread.php?threadid=47019


-

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





Simo



--

moondar

-----------------------------------------------------------------------

moondark's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2739

View this thread: http://www.excelforum.com/showthread.php?threadid=47019



-

Hi Raj



Assuming your profit margin is in C1 then

=1%+(C1>20%)*1%+(C1>30%)*1%+(C1>35%)*3%+(C1>61%)*-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:

many thanks

that was fantastic!





--

Raj Shah

------------------------------------------------------------------------

Raj Shah's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27505

View this thread: http://www.excelforum.com/showthread.php?threadid=470193



-