Have problems with a few formulas

[slats432]

In one cell I want to add a value with three different options.

If the number in B4 is greater than 449999 then I want to add 300 to the number in B8. If it is 499999 I want to add 400. If it is 5499999 or higher I want to add 500. I tried this formula but id doesn't work.


=IF(B4>449999, B8+300, IF(B4>499999, B8+400, IF(B4>549999, B8+500)))

---------------------

The next one is I would like to plug in a number and if it is between 50000 and 10000 I would like to get .01, if it is higher I want it to be .03 of greater than 100000 plus 500.

Thanks for any help.

 

[shg]

Reverse the order of tests:

= B8 + IF(B4>549999, 500, IF(B4>499999, 400, IF(B4>449999, 300)))

 

[Jonmo1]

Welcome to the board...

I'm sure someone will come after me with a more flexible lookup type of formula..
I'll just explain why yours didn't work..

Nested If formulas are read from left to right.
Once a TRUE condition is found, it returns that result, and ignores everything else after that TRUE Condition.

so if B4 is say 520000, you WANT it to return B8+400...
But, since 520000 is indeed > 449999, then the FIRST If (B4>449999) is TRUE, then it returns B8+300, and IGNORES the remaining IF's.

You have to reverse the logic...

=IF(B4>549999, B8+500,IF(B4>499999,B8+400,IF(B4>449999,B8+300)))


Hope that helps.

 

[slats432]

You are awesome...any thoughts on the 2nd one?