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$54000000,"5",IF($B$55000000,"7",IF($B$560 00000,"9",IF($B$57000000,"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$57000000,"10",IF($B$56000000,"9",IF($B$55 000000,"7",IF($B$54000000,"5")))))

--
If this post helps click Yes
---------------
Jacob Skaria


"steve12173" wrote:

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$54000000,"5",IF($B$55000000,"7",IF($B$560 00000,"9",IF($B$57000000,"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?

That is because once the IF statement is happy with $B$54000000, then rest
of the IF is never even checked.
--
Gary''s Student - gsnu200857


"steve12173" wrote:

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$54000000,"5",IF($B$55000000,"7",IF($B$560 00000,"9",IF($B$57000000,"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?

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
---------------
Jacob Skaria


"steve12173" wrote:

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$54000000,"5",IF($B$55000000,"7",IF($B$560 00000,"9",IF($B$57000000,"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?

Hi,
You need to use
=IF(AND($B$54000000,$B$5<5000000),"5",IF(AND($B$5 5000000,$B$5<6000000),"7",IF(AND($B$56000000,$B$ 5<7000000),"9",IF($B$57000000,"10",""))))

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


"steve12173" wrote:

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$54000000,"5",IF($B$55000000,"7",IF($B$560 00000,"9",IF($B$57000000,"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?

One way...

Start from the upper boundary and work down:

=IF($B$57000000,10,IF($B$56000000,9,IF($B$55000 000,7,IF($B$54000000,5,IF($B$5<=4000000,0,"")))))

--
Biff
Microsoft Excel MVP


"steve12173" wrote in message
...
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$54000000,"5",IF($B$55000000,"7",IF($B$560 00000,"9",IF($B$57000000,"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?

That worked perfectly, thanks. I never thought of putting the info in
backwards.

"Jacob Skaria" wrote:

4000000 returns true for any number above that and hence returns 5 always./
Try this...

=IF($B$5<4000000, "0",
IF($B$57000000,"10",IF($B$56000000,"9",IF($B$55 000000,"7",IF($B$54000000,"5")))))

--
If this post helps click Yes
---------------
Jacob Skaria


"steve12173" wrote:

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$54000000,"5",IF($B$55000000,"7",IF($B$560 00000,"9",IF($B$57000000,"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?

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$54000000,"5",IF($B$55000000,"7",IF($B$560 00000,"9",IF($B$5700
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.

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

This variation might work better:
=IF($B$57000000,"10",IF($B$56000000,"9",IF($B$5 5000000,"7",
IF($B$54000000,"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.