Nested If Statement

Nichole09

Board Regular
Joined
Aug 27, 2016
Messages
132
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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
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.
 
Upvote 0
*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.
 
Upvote 0
*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)))))))))))))
 
Upvote 0
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.
 
Upvote 0
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)))))))))))))
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top