IF Statement not working

  • Thread starter Thread starter steve12173
  • Start date Start date
S

steve12173

I have an IF Statement to give me a number based on sum of another cell. It
looks like this: =IF($B$5<4000000, "0",
IF($B$5>4000000,"5",IF($B$5>5000000,"7",IF($B$5>6000000,"9",IF($B$5>7000000,"10")))))

If my number is less than 4000000 it gives me the "0" and if it is greater
than 4000000 it gives me "5" but, if the number is greater than 5000000 it
still gives me "5" rather than the correct number. Anyone see what I am
doing wrong?
 
4000000 returns true for any number above that and hence returns 5 always./
Try this...

=IF($B$5<4000000, "0",
IF($B$5>7000000,"10",IF($B$5>6000000,"9",IF($B$5>5000000,"7",IF($B$5>4000000,"5")))))
 
That is because once the IF statement is happy with $B$5>4000000, then rest
of the IF is never even checked.
 
Alternative (which is easy to edit..)

=LOOKUP(B5+0,{0,4000001,5000001,6000001,7000001},{0,5,7,9,10})

If this post helps click Yes
 
Hi,
You need to use
=IF(AND($B$5>4000000,$B$5<5000000),"5",IF(AND($B$5>5000000,$B$5<6000000),"7",IF(AND($B$5>6000000,$B$5<7000000),"9",IF($B$5>7000000,"10",""))))

I added a condition to leave it blank if it is less than 4000000, you can
take it just delete ,"" after 10
 
One way...

Start from the upper boundary and work down:

=IF($B$5>7000000,10,IF($B$5>6000000,9,IF($B$5>5000000,7,IF($B$5>4000000,5,IF($B$5<=4000000,0,"")))))
 
I have an IF Statement to give me a number based on sum of another
cell. It looks like this: =IF($B$5<4000000, "0",
IF($B$5>4000000,"5",IF($B$5>5000000,"7",IF($B$5>6000000,"9",IF($B$5>700
0000,"10")))))

If my number is less than 4000000 it gives me the "0" and if it is
greater than 4000000 it gives me "5" but, if the number is greater
than 5000000 it still gives me "5" rather than the correct number.
Click to expand...

For example, suppose B5 contains 6000000. Evaluating the IFs in order,
notice that once we get to $B$5>4000000 we get the result "5" and never get
to test the remaining IFs.

This variation might work better:
=IF($B$5>7000000,"10",IF($B$5>6000000,"9",IF($B$5>5000000,"7",
IF($B$5>4000000,"5","0"))))
From the post it isn't clear what's intended for 4000000 exactly, so think
about where to use > versus >=

A different approach would be to use a function in the LOOKUP family, as
described in Excel's built-in Help.

Excel 2003 has a tool useful for diagnosing problems like this. Select the
cell with the troublesome formula and use:
Tools > Formula auditing > Evaluate formula
When the dialog box appears, click "Evaluate". Each click shows one step of
Excel's calculation.
 
Back
Top