Strange If And Or issue

[kltinaheart]

Hey all,
getting N/A errors for some reason.

[FONT=arial][SIZE=2][COLOR=#000000]=IF(AND(OR(J255="Closed",J255="Auto-closed"),C255=1,NETWORKDAYS(D255,O255)<=17),"OnTime",[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=arial][SIZE=2][COLOR=#000000]IF(AND(OR(J255="Closed",J255="Auto-closed"),C255=2,NETWORKDAYS(D255,O255)<=17),"OnTime",[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=arial][SIZE=2][COLOR=#000000]IF(AND(OR(J255="Closed",J255="Auto-closed"),C255=3,NETWORKDAYS(D255,O255)<=10),"OnTime",[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=arial][SIZE=2][COLOR=#000000]IF(AND(OR(J255="Closed",J255="Auto-closed"),C255=4,NETWORKDAYS(D255,O255)<=5),"OnTime",[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=arial][SIZE=2][COLOR=#000000]IF(AND(OR(J255="Closed",J255="Auto-closed"),C255=5,NETWORKDAYS(D255,O255)<=1),"OnTime",[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=arial][SIZE=2][COLOR=#000000]IF(AND(C255=1,NETWORKDAYS(D255,P255)<=17),"OnTime",[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=arial][SIZE=2][COLOR=#000000]IF(AND(C255=2,NETWORKDAYS(D255,P255)<=17),"OnTime",[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=arial][SIZE=2][COLOR=#000000]IF(AND(C255=3,NETWORKDAYS(D255,P255)<=10),"OnTime",[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=arial][SIZE=2][COLOR=#000000]IF(AND(C255=4,NETWORKDAYS(D255,P255)<=5),"OnTime",[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=arial][SIZE=2][COLOR=#000000]IF(AND(C255=5,NETWORKDAYS(D255,P255)<=1),"OnTime",[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=arial][SIZE=2][COLOR=#000000]"OUT"))))))))))[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]<o:p></o:p>[/COLOR][/SIZE][/FONT]

First part of formula is - look at J for “closed” if its there do this math.
2^nd part is - Otherwise (if J doesn't have anything, or says something else) do this math.
For some reason, in the 2^nd part of formula, that Column O that im pulling from to do the math function in the first part is effecting the 2^nd part.
When I click on the empty cell in O, and simply press delete, the formula all of the sudden works.

Ideas?

 

[jtakw]

Hi,

Don't know why you're experiencing what you described, perhaps check the referenced cells in C, D, O, P that they all contain Numbers and is/are Not Text ( use =ISNUMBER(cell reference) to test).

In the meantime, I've "cleaned upped" your nested IF a little (it should be following the exact same logic as the formula you posted):

Book1
	C
1	No Match
Sheet70

Cell Formulas
Range	Formula
C1	=IF(OR(J255={"Closed","Auto-closed"}),IF(OR(AND(OR(C255={1,2}),NETWORKDAYS(D255,O255)<=17),AND(C255=3,NETWORKDAYS(D255,O255)<=10),AND(C255=4,NETWORKDAYS(D255,O255)<=5),AND(C255=5,NETWORKDAYS(D255,O255)<=1)),"OnTime",IF(OR(AND(OR(C255={1,2}),NETWORKDAYS(D255,P255)<=17),AND(C255=3,NETWORKDAYS(D255,P255)<=10),AND(C255=4,NETWORKDAYS(D255,P255)<=5),AND(C255=5,NETWORKDAYS(D255,P255)<=1)),"OnTime","OUT")),"No Match")

 

[jtakw]

Oops, just realized I have a misplaced bracket, please ignore the formula I posted above, this is the corrected version.

Also, I don't think the first part of Your formula looking at O is affecting the second part of your formula, since if J is not "closed", the rest of the first part will Not "fire", and will go directly to the second part without looking at O.

Book1
	C
1	OUT
Sheet70

Cell Formulas
Range	Formula
C1	=IF(OR(J255={"Closed","Auto-closed"}),IF(OR(AND(OR(C255={1,2}),NETWORKDAYS(D255,O255)<=17),AND(C255=3,NETWORKDAYS(D255,O255)<=10),AND(C255=4,NETWORKDAYS(D255,O255)<=5),AND(C255=5,NETWORKDAYS(D255,O255)<=1)),"OnTime","OUT"),IF(OR(AND(OR(C255={1,2}),NETWORKDAYS(D255,P255)<=17),AND(C255=3,NETWORKDAYS(D255,P255)<=10),AND(C255=4,NETWORKDAYS(D255,P255)<=5),AND(C255=5,NETWORKDAYS(D255,P255)<=1)),"OnTime","OUT"))