I am trying to calculate a graduated bonus using IF statements. My problem
is that it only recognizes the criteria for the last IF statement.

=IF((B25-D25)<12000,0,IF((B25-D25)12000<15000,((D25-J25)-12000)*0.25,IF((B25-D25)15000<17000,((B25-D25)-15000)*0.3+(3000*0.25),IF((B25-D25)17000,((B25-D25)-17000)*0.35+(3000*0.25)+2000*0.3))))

If production - sales < $12000 then $0 bonus
If production - sales $12000< $15000 then 25% over $12000
If production - sales $15000 < $17000 then 30% over $15000
If production - sales $17000 then 35%

How do I get the formula to stop at the lower limits of the criteria. Now
it just gives me a FALSE answer for anything under $17000.

Thanks for your help!!

NancyB

See this:

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

--
Biff
Microsoft Excel MVP


"NancyB" wrote in message
...
I am trying to calculate a graduated bonus using IF statements. My problem
is that it only recognizes the criteria for the last IF statement.

=IF((B25-D25)<12000,0,IF((B25-D25)12000<15000,((D25-J25)-12000)*0.25,IF((B25-D25)15000<17000,((B25-D25)-15000)*0.3+(3000*0.25),IF((B25-D25)17000,((B25-D25)-17000)*0.35+(3000*0.25)+2000*0.3))))

If production - sales < $12000 then $0 bonus
If production - sales $12000< $15000 then 25% over $12000
If production - sales $15000 < $17000 then 30% over $15000
If production - sales $17000 then 35%

How do I get the formula to stop at the lower limits of the criteria. Now
it just gives me a FALSE answer for anything under $17000.

Thanks for your help!!

NancyB

This part does not work
((B25-D25)12000<15000
you need an and to do something like that
and(B25-D2512000, B25-D25<15000)

That being said you can simplify something like this
=IF(B25-D25 < 12000, 0, IF(B25-D25 < 15000, "12 - 15", IF(B25-D25 < 17000,
"15 - 17", "Over 17")))
--
HTH...

Jim Thomlinson


"NancyB" wrote:

I am trying to calculate a graduated bonus using IF statements. My problem
is that it only recognizes the criteria for the last IF statement.

=IF((B25-D25)<12000,0,IF((B25-D25)12000<15000,((D25-J25)-12000)*0.25,IF((B25-D25)15000<17000,((B25-D25)-15000)*0.3+(3000*0.25),IF((B25-D25)17000,((B25-D25)-17000)*0.35+(3000*0.25)+2000*0.3))))

If production - sales < $12000 then $0 bonus
If production - sales $12000< $15000 then 25% over $12000
If production - sales $15000 < $17000 then 30% over $15000
If production - sales $17000 then 35%

How do I get the formula to stop at the lower limits of the criteria. Now
it just gives me a FALSE answer for anything under $17000.

Thanks for your help!!

NancyB

Somwhere there is a J25 mentioned...

You need to replace IF((B25-D25)12000<15000 with And(). ..if you write
the formula in an order you dont need to mention both the conditions. If
value is more than 12000 then only it goes into the next IF statement...

=If((B25-D25)<12000,TRUE,IF((B25-D25)<15000,TRUE,IF((B25-D25)<17000,TRUE)




If this post helps click Yes
---------------
Jacob Skaria


"NancyB" wrote:

I am trying to calculate a graduated bonus using IF statements. My problem
is that it only recognizes the criteria for the last IF statement.

=IF((B25-D25)<12000,0,IF((B25-D25)12000<15000,((D25-J25)-12000)*0.25,IF((B25-D25)15000<17000,((B25-D25)-15000)*0.3+(3000*0.25),IF((B25-D25)17000,((B25-D25)-17000)*0.35+(3000*0.25)+2000*0.3))))

If production - sales < $12000 then $0 bonus
If production - sales $12000< $15000 then 25% over $12000
If production - sales $15000 < $17000 then 30% over $15000
If production - sales $17000 then 35%

How do I get the formula to stop at the lower limits of the criteria. Now
it just gives me a FALSE answer for anything under $17000.

Thanks for your help!!

NancyB