function - if

[Raj Shah]

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

[moondark]

Please Login or Register  to view this content.

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


Simon

[Roger Govier]

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:
> 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]

many thanks
that was fantastic!