Conditional Formula || IF AND HELP!!!

investr

New Member
Joined
Apr 30, 2002
Messages
10
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...
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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
 
Upvote 0
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

:wink:
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Be careful with these if statements. You can only have 6 nested if's before you break Excel.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top