Sales Payout

[Legacy 192487]

We offer a Sales Incentive Payout based on minimums:

2% in Dollars for a minimum of $2,500 and not more than $8,000.
3% in Dollars for a minimum of $8,000 and not more than $12,000.
5% in Dollars for $12,000 an higher.

Here is the current formula that isn't working:

=IF(AND(B16<2500,B16*0),B16*0.03),IF(AND(B16>=2500,B16<8000),B16*0.02),IF(AND(B16>=8000,B16<12000),B16*0.03),IF(AND(B16>=12000),B16*0.05)

Any help would be appreciated!

 

[pclplante]

Your orginal formula you had each if statement closed off and started a new one you need three parts of a if statement (the logical test, if that test is true, if that test is false) you left out the if false within each if statement which returned an error. You had a logical test and if true but no if false so excel was expecting a value for if false and returned an error. So I just ilimated the parentises after each if true area here is the corrected formula.

=IF(AND(B16<2500,B16*0),B16*0.03,IF(AND(B16>=2500,B16<8000),B16*0.02,IF(AND(B16>=8000,B16<12000),B16*0.03,IF(AND(B16>=12000),B16*0.05,))))

Here is the old formula with what I deleted.

=IF(AND(B16<2500,B16*0),B16*0.03),IF(AND(B16>=2500,B16<8000),B16*0.02),IF(AND(B16>=8000,B16<12000),B16*0.03),IF(AND(B16>=12000),B16*0.05)

 

[pclplante]

I forgot to hit spell check so the sectets out I can't spell without spell/grammar check.

 

[Legacy 192487]

Wow...that was quick!

Thank you, and thank you more for the explanation should I run into something like it again.

Best wishes to you...

 

[Marcelo Branco]

Hi,

Another solution you can find at

http://www.mcgimpsey.com/excel/variablerate.html

M.

 

[barry houdini]

Looks to me as if you just want those percentages to apply to the whole amount, so if you have $10,000 in B16 then the result of that should be $300 (and that's what pclplante's suggestion will give).

Marcelo's link assumes that you pay different percentages within that $10,000, e.g. the first $2,500 pays at 0%, the next $5,500 (up to the $8,000 threshold) pays at 2% and the last $2,000 pays at 3%, giving a total of $170.

In your formula

=IF(AND(B16<2500,B16*0),B16*0.03),IF(AND(B16>=2500,B16<8000),B16*0.02),IF(AND(B16>=8000,B16<12000),B16*0.03),IF(AND(B16>=12000),B16*0.05)

the red highlighted AND function can never return TRUE, whatever the value of B16 because B16*0 will always give 0 and, if zero is returned with an AND, the AND is always FALSE, what are you trying to do with that part?

The blue highlighted AND doesn't need AND because you have only one condition, simply B16>=12000 will suffice.

Typically, if you order the IFs correctly you don't need to use AND at all, this formula should do the same as pclplante's

=IF(B16>=12000,5%,IF(B16>=8000,3%,IF(B16>=2500,2%)))*B16

another alternative is to use LOOKUP, whereby you construct a table showing the lower bound of each percentage range in one column....and the applicable percentage in the next, e.g. in Y2:Z5

and then you can use a simple LOOKUP, i.e.

=LOOKUP(B16,Y$2:Z$5)*B16

 

[Legacy 192487]

Thanks all. I figured there had to be an easier formula that what I was trying to do. barry houdini, you're right, both yours and pclplante works! Thanks again all for the help.