Multiple nested IF Statements

[MMarks]

I am trying to write the following IF statement
=IF(AW8>E8, "+", IF(AW8<E8, "-", IF(AW8=E8, "Y")))

However AW8 also contains a nested IF statement (See below), and therefore the arguments above are not showing correctly.
=IF(AV8<=19.99,"0",IF(AV8<=34. 5,"1",IF(AV8<49,"2",IF(AV8<63. 5,"3",IF(AV8<77,"4",IF(AV8>78, "5"))))))

Is there a way to resolve this?

Thanks

 

[arunsjain]

Did you use following formula in AW8?


=IF(AV8<=19.99,0,IF(AV8<=34.5,1,IF(AV8<49,2,IF(AV8<63.5,3,IF(AV8<77,4,5)))))


Because with your formula (=IF(AV8<=19.99,0,IF(AV8<=34. 5,1,IF(AV8<49,2,IF(AV8<63. 5,3,IF(AV8<77,4,IF(AV8>78, 5)))))), you will get + always because resulting value between 77 and 78 is not defined. If you keep cell value 78 then resulting value will be "FALSE" with this formula.

 

[MMarks]

Statement in cell AW8. =IF(AV8<=19.99, "0"...... etc seems to be working fine.
Statement in cell AX8 =IF(AW8>E8, "+",.... etc is not returning the correct values.
For example
Cell AW8 shows 2 as result of =IF(AV8 <=19.99, "0"....
Cell AX8 shows + even though E8 is 3 which means it should show -
When I replace formula in AW8 with number 2 AX8 displays - which is correct.

 

[Aladin Akyurek]

Statement in cell AW8. =IF(AV8<=19.99, "0"...... etc seems to be working fine.
Statement in cell AX8 =IF(AW8>E8, "+",.... etc is not returning the correct values.
For example
Cell AW8 shows 2 as result of =IF(AV8 <=19.99, "0"....
Cell AX8 shows + even though E8 is 3 which means it should show -
When I replace formula in AW8 with number 2 AX8 displays - which is correct.

Try to post a series of input values along with corresponding return values.

 

[arunsjain]

Hi Mark,

I have used =IF(AV8<=19.99,0,IF(AV8<=34.5,1,IF(AV8<49,2,IF(AV8<63.5,3,IF(AV8<77,4,5))))).

Result Table

E	AV	AW	AX
		Formula	Formula
1.00	19.99	0.00	-
0.00	34.50	1.00	+
2.00	48.00	2.00	Y
4.00	62.00	3.00	-
3.00	76.00	4.00	+
5.00	78.00	5.00	Y

<tbody>
</tbody><colgroup><col span="2"><col span="2"></colgroup>

E	AV	AW	AX
		Formula	Formula
1	19.99	=IF(AV8<=19.99,0,IF(AV8<=34.5,1,IF(AV8<49,2,IF(AV8<63.5,3,IF(AV8<77,4,5)))))	=IF(AW8>E8, "+", IF(AW8<E8, "-", IF(AW8=E8, "Y")))
0	34.5	=IF(AV9<=19.99,0,IF(AV9<=34.5,1,IF(AV9<49,2,IF(AV9<63.5,3,IF(AV9<77,4,5)))))	=IF(AW9>E9, "+", IF(AW9<E9, "-", IF(AW9=E9, "Y")))
2	48	=IF(AV10<=19.99,0,IF(AV10<=34.5,1,IF(AV10<49,2,IF(AV10<63.5,3,IF(AV10<77,4,5)))))	=IF(AW10>E10, "+", IF(AW10<E10, "-", IF(AW10=E10, "Y")))
4	62	=IF(AV11<=19.99,0,IF(AV11<=34.5,1,IF(AV11<49,2,IF(AV11<63.5,3,IF(AV11<77,4,5)))))	=IF(AW11>E11, "+", IF(AW11<E11, "-", IF(AW11=E11, "Y")))
3	76	=IF(AV12<=19.99,0,IF(AV12<=34.5,1,IF(AV12<49,2,IF(AV12<63.5,3,IF(AV12<77,4,5)))))	=IF(AW12>E12, "+", IF(AW12<E12, "-", IF(AW12=E12, "Y")))
5	78	=IF(AV13<=19.99,0,IF(AV13<=34.5,1,IF(AV13<49,2,IF(AV13<63.5,3,IF(AV13<77,4,5)))))	=IF(AW13>E13, "+", IF(AW13<E13, "-", IF(AW13=E13, "Y")))

<tbody>
</tbody><colgroup><col span="2"><col><col></colgroup>

 

[MMarks]

Thanks, that seems to have fixed it!
Looks like the issue was the use of "" in my version of the nested IF in AW column...

Thanks again