Nested IF not reading the nested IF's

[NICKRENN]

Hi All

I've tried to find a solution to this but i just can't see it. using a really basic nested IF

=IF(D4=1,600000,IF(D4=2,525000,IF(D4=3,375000,IF(D4=4,225000,IF(D4=5,100000,IF(D4=6,25000,0))))))

I am only getting a return of 25000 or 0. I have this replicated in D4 to D7 with the scores as follows

[TABLE="width: 250"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]6[/TD]
[TD]25,000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]#DIV/0![/TD]
[TD]#DIV/0!
[/TD]
[/TR]
</tbody>[/TABLE]

i don't follow why its not reading the 4 or 5?

Any ideas?

Thanks

 

[admiral100]

Hi,

Maybe D4 is not a number , check with value(D4) or =ISNUMBER(D4), what you get ?

and you can try this =CHOOSE(D4,600000,525000,375000,225000,100000,25000)

 

[mole999]

text number,, if you looked at the cell with =ISNUMBER(D4) it should return true

 

[jorismoerings]

As previously mentioned a number formatted as text can be the issue.

Can be easily sorted by using CHOOSE instead of a set of nested IF.

Take a look at this:

Book1
	A	B	C	D
4	6	25000		25000
5	5	100000		100000
6	4	0		225000
7	9	0		0
Sheet2

Cell Formulas
Range	Formula
B4	=IF(A4=1,600000,IF(A4=2,525000,IF(A4=3,375000,IF(A4=4,225000,IF(A4=5,100000,IF(A4=6,25000,0))))))
D4	=IFERROR(CHOOSE(A4,600000,525000,375000,225000,100000,25000,),0)

 

[mole999]

you might even force D4 to a number with a double minus sign

=IF(--D4=1,600000,IF(--D4=2,525000,

the other thing is if you left a space around the number in which case TRIM is required

 

[jorismoerings]

the other thing is if you left a space around the number in which case TRIM is required

So right, that's exactly why i suggested to use CHOOSE because function will actually force the field to a number if it looks like a number.
Check this:

Book1
	A	B	C	D
4	6	25000		25000
5	5	0		100000
6	03	0		375000
7	9	0		0
Sheet2

Cell Formulas
Range	Formula
A6	=TEXT(3,"00")
B6	=IF(A6=1,600000,IF(A6=2,525000,IF(A6=3,375000,IF(A6=4,225000,IF(A6=5,100000,IF(A6=6,25000,0))))))
D6	=IFERROR(CHOOSE(A6,600000,525000,375000,225000,100000,25000,),0)