Nested If Statement

[Nichole09]

I am certain this should be an easy fix however I am having a terrible time trying to figure out why my formula is not working as it should. The below formula worksif conditions are met, but not if they have not been met. For example each cellreferenced has a date for its value. I want my formula to put the value of thatcell if a date is present and if not the value should say “NOW”. What's odd isthis formula works if conditions are met but is just blank when the formula isnot met instead of the value being “NOW”. Can anyone point me in the rightdirection as to why this is happening?

Example: If there is a value in AB71 (each cell value below will always be a date) then the date of AB71 should appear if no cells have a date "NOW" should be the value, but this is not happening.


=IF(AB71>0,AB71,IF(S65>0,S65,IF(D68>0,D68,IF(S54>0,S54,IF(AC39>0,AC39,IF(AC33>0,AC33,IF(AC31>0,AC31,IF(AC29>0,AC29,IF(Y33>0,Y33,IF(Y31>0,Y31,IF(Y29>0,Y29,IF(V43>0,V43,IF(A1>0,A1,"NOW")))))))))))))


I appreciate any help!

 

[mrshl9898]

I'd say one of the cells aren't blank, I get NOW when I paste this on a blank sheet, and I get any value or string if I type that in any cell.

I'd go to all the cells and double click in the formula bar to check for spaces, or use =ISBLANK() to check for blanks

 

[Nichole09]

Hi Mrshl9898. Now that you mention it, cells A1 and V43 become a date based on a formula. Is that why my formula is not working? How can I correct it to only look at the value of the cell not the formula it self? Hopefully that makes sense.

 

[mrshl9898]

I wouldn't imagine that would cause an issue. Did you check the cells that look blank?

 

[mrshl9898]

*If one of your formula cells is returning a "" that will cause the problem, it's not truly blank.

You could replace your AB71>0 searches for LEN(AB71)>0 to get rid of that problem.

 

[Nichole09]

*If one of your formula cells is returning a "" that will cause the problem, it's not truly blank.

You could replace your AB71>0 searches for LEN(AB71)>0 to get rid of that problem.

So I made sure to put LEN on the cells that contain a formula and went through all the other cells and hit delete to ensure there were no value that I might have missed. My formula should have returned a "0" instead no value is showing up at all. Here is my formula with corrections.


=IF(LEN(AB71)>0,AB71,IF(LEN(S65)>0,S65,IF(LEN(D68)>0,D68,IF(LEN(S54)>0,S54,IF(AC39>0,AC39,IF(AC33>0,AC33,IF(AC31>0,AC31,IF(AC29>0,AC29,IF(Y33>0,Y33,IF(Y31>0,Y31,IF(Y29>0,Y29,IF(V43>0,V43,IF(LEN(A1)>0,A1,0)))))))))))))

 

[mrshl9898]

Can you upload a copy to dropbox or similar? It's hard to debug without knowing what's in all those cells.

Alternatively, try typing a word in all cells starting with AB71 and working through the list until you get the word appearing in the cell containing the formula. Then share what's in that cell.

 

[Nichole09]

Can you upload a copy to dropbox or similar? It's hard to debug without knowing what's in all those cells.

Alternatively, try typing a word in all cells starting with AB71 and working through the list until you get the word appearing in the cell containing the formula. Then share what's in that cell.

mrshl988 thank you so much For your help!!! I tried typing words in my formulas to seewhich ones are coming up and realized what you were trying to explain before. Idid not have true blank spaces. My formulas included “” when conditions werenot met. I corrected my formula below and this works as intended!!! Thankyou again!!!

=IF(LEN(AB71)<>"",AB71,IF(LEN(S65)<>"",S65,IF(LEN(D68)<>"",D68,IF(LEN(S54)<>"",S54,IF(AC39<>"",AC39,IF(AC33<>"",AC33,IF(AC31<>"",AC31,IF(AC29<>"",AC29,IF(Y33<>"",Y33,IF(Y31<>"",Y31,IF(Y29<>"",Y29,IF(V43<>"",V43,IF(LEN(A1)<>"",A1,0)))))))))))))

 

[mrshl9898]

Glad it's sorted. Just drop those LENs as LEN will always <>"".