Conditional Formula || IF AND HELP!!!

[investr]

If net margin is equal to or greater than 1.5% and less than 1.74% then commission is 6.0%, if margin is greater than or equal to 1.75% and less than 1.99% then commission is 6.5% and so on...

[Asala42]

Hiya,

A couple questions:

What if Net Margin is <1.5% (commission = 0%?)

How far does "and so on" go? Does it follow the same trend of for +0.5% for every +0.25% Net Margin to infinity?

Adam

[RET79]

On 2002-05-01 14:10, investr wrote:
If net margin is equal to or greater than 1.5% and less than 1.74% then commission is 6.0%, if margin is greater than or equal to 1.75% and less than 1.99% then commission is 6.5% and so on...

Vlookup perhaps...

RET79

[investr]

Yes, less than 1.5 is 0. You don't get paid! And the "so on" part is as follows

Net Margin 1.50-1.74% = 6% Commission
Net Margin 1.75-1.99% = 6.5% Commission
Net Margin 2.00-2.49% = 7.0% Commission

Up to 11% commission

[Asala42]

Hiya,

I'm guessing that was a typo in the last net margin entry:

Net Margin 1.50-1.74% = 6% Commission
Net Margin 1.75-1.99% = 6.5% Commission
Net Margin 2.00-2.49% = 7.0% Commission

If I get the specifics correctly, a Net margin >= 3.75% equates to the max Commission of 11%

If this is the case and A2 holds the Net Margin:

=IF(A2<1.5%,0,MIN(6%+2*(FLOOR(A2,0.0025)-1.5%),11%))

Hope that helps,
Adam

[investr]

YOUR AMAZING!

Thanks Adam , You rock!

[investr]

As long as I am here...

Goal 95% Paid 100%
Goal 94% Paid 94%
Goal 93% Paid 93%

All the way down. so 95% > = 100 Paid

[Asala42]

Hey again,

Could you specify with a few more details onn that last post? I'm not catching the correlation beween Goal and Paid as well as what happens at the extremes.

(I apologize if it's an obvious connection - it's 6PM in Florida and my brain is beginning to short circuit).

Adam

[zacemmel]

Be careful with these if statements. You can only have 6 nested if's before you break Excel.

[investr]

Ok, so I want to have a data cell open where I can plug in..lets say 95% under the header of "GOAL", I would like the column next to it to represent a payout based on that variable. So if my Goal is equated by being higher than 95% I would get a 100% payout, and Goal of 94% would get a 94% payout, Goal of 93% would get 93%. As you can see, anything less than 94% gets EVEN payout.

Basically I just need to learn a formula than can have 5 sets or so of criteria.

IF a2>= 95% then payout = 100% and if a2=94% then payout = 94%...etc

I just need to learn how to script what I am thinking in my head LOL

Thanks adam.