Formual Won't Work

  • Thread starter Thread starter George
  • Start date Start date
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%
 
That is because 500 and 700 are >= 3000, so it doesn't look any further
Change the order of testing the threshold values
 
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%"))))
 
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
 
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%)))
 
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%)))
 
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...
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula Won't Work 5
Formula won't work 3
Formula Won't Work 1
How do I express the following calculation. 3
IF Statement troubles 3
In Excel if is more than 25% from start date then make cell red 3
Highest, Second Highest , Third Highest and so on 3
I'm new to Excel, 8

Back
Top