Formula Won't Work

G

George

Hello, can someone please help me with the following formula
=IF(F5<=2999.99,"25%",IF(F5>=3000,"50%",IF(F5>=5000,"75%",IF(F5>=7000,"100%"))))
For some reason it calculates the 25% and 50% correctly, but it won't
calculate the 75% or 100% at all, it just stays at 50%
 
P

Pecoflyer

George;228856 said:
Hello, can someone please help me with the following formula;
=IF(F5<=2999.99,"25%",IF(F5>=3000,"50%",IF(F5>=5000,"75%",IF(F5>=7000,"100%"))))
For some reason it calculates the 25% and 50% correctly, but it won't
calculate the 75% or 100% at all, it just stays at 50%
Click to expand...

That is because you cover everything with the first two conditions

F5<=2999.99 and F5>3000
There is nothing else to test, any number is smaller than or large
than 300

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste
 
C

CurlyDave

Hello, can someone please help me with the following formula;
=IF(F5<=2999.99,"25%",IF(F5>=3000,"50%",IF(F5>=5000,"75%",IF(F5>=7000,"100%"))))
For some reason it calculates the 25% and 50% correctly, but it won't
calculate the 75% or 100% at all, it just stays at 50%
Click to expand...

See the other same question
 
R

Ron Rosenfeld

Hello, can someone please help me with the following formula;
=IF(F5<=2999.99,"25%",IF(F5>=3000,"50%",IF(F5>=5000,"75%",IF(F5>=7000,"100%"))))
For some reason it calculates the 25% and 50% correctly, but it won't
calculate the 75% or 100% at all, it just stays at 50%
Click to expand...

=IF(F5<=2999.99,"25%",IF(F5>=3000,"50%",

If F5>=3000, your second IF conditional evaluates to TRUE and the 50% statement
gets executed. Your function will never go past that.

You need to ensure that your conditionals are mutually exclusive, otherwise the
first one will get executed.

Write your IF statements in descending order and you will avoid that problem:

=if(f5>=7000,100%,if(f5>=5000,75%,if(f5>=3000,50%,25%)))

Also, if you want to use the result of this formula in a math function, don't
use the quotation marks, as they will return the result as TEXT (which will not
be recognized as a number by some Functions).
--ron
 
B

Bob I

Because in an IF statement, once the the test is TRUE, the Else is not
evaluated. Reorder your test with that in mind.
 
D

Dave Peterson

Check in descending order:

=IF(F5>=7000,100%,IF(F5>=5000,75%,IF(F5>=3000,50%,25%)))

And I would think that you would want a real number returned--not text.

Make sure you format the cell as a percentage, too.
 
M

MartinW

Hi George,

It's because once you have any number in F5 greater than 3000, the second If
statement is fulfilled and the formula never gets to the later stages.

You could readjust your less than and greater than arguments but here is
another way to do it.

=LOOKUP(F5,{0,3000,5000,7000},{"25%","50%","75%","100%"})

HTH
Martin
 
Top