Formual 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%, but it will not calculate the
75% or 100%, it just stays at 50%
 
N

Niek Otten

That is because 500 and 700 are >= 3000, so it doesn't look any further
Change the order of testing the threshold values
 
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%, but it will not calculate the
75% or 100%, it just stays at 50%
Click to expand...

This should work

=IF(F5<3000,"25%",IF(AND(F5>=3000,F5<5000),"50%",IF(AND
(F5>=5000,F5<7000),"75%",IF(F5>=7000,"100%"))))
 
B

BT

Hi George

Not pretty, but it works. Have you considered a lookup?

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

Regards

BT
 
B

Brad

Another solution
=if(f5<=2999.99,"25%",if(f5<=4999.99,"50%",if(f5<=6999.99,"75%","100%")))
 
D

David Biddulph

Defintely not pretty, and unnecessarily complicated. You don't need the
various AND functions, because, for example, you have already tested for
F5<3000 and hence you don't need to test for F5>=3000.

=IF(F5<3000,25%,IF(AND(F5>=3000,F5<5000),50%,IF(AND(F5>=5000,F5<7000),75%,100%)))
can be shortened to
=IF(F5<3000,25%,IF(F5<5000,50%,IF(F5<7000,75%,100%)))
 
D

David Biddulph

Yes, but why do the unncessary ANDs, Dave? Why test for F5>=3000, as you've
already tested for F5<3000?
And let's guess that the OP may have wanted his percentages as numbers, not
as text strings.

=IF(F5<3000,"25%",IF(AND(F5>=3000,F5<5000),"50%",IF(AND(F5>=5000,F5<7000),"75%",IF(F5>=7000,"100%"))))
can be shortened to
=IF(F5<3000,25%,IF(F5<5000,50%,IF(F5<7000,75%,100%)))
 
G

George

Thanks to all.

David Biddulph said:
Defintely not pretty, and unnecessarily complicated. You don't need the
various AND functions, because, for example, you have already tested for
F5<3000 and hence you don't need to test for F5>=3000.

=IF(F5<3000,25%,IF(AND(F5>=3000,F5<5000),50%,IF(AND(F5>=5000,F5<7000),75%,100%)))
can be shortened to
=IF(F5<3000,25%,IF(F5<5000,50%,IF(F5<7000,75%,100%)))
Click to expand...
 
R

Rasoul Khoshravan

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%, but it will not calculate the
75% or 100%, it just stays at 50%
Click to expand...

Try this one
=IF(F5<3000,"25%",IF(F5<5000,"50%",IF(F5<7000000,"75%","100%")))
In your formula. if a number is bigger than 3000, then obviously it
will be greater than 5000 and 7000 therefore it will never pass this
step.
HTH
 
Top