Forum Discussion
Cell not populating correctly based on formula
Hello, I am having an issue with an IF statement that is not working properly. Cell that says done is dependent on the other cell saying complete. But if highlighted (green) cell is changed to N/A, the Done cell turns to a date again. Image two shows this error. And image 3 shows the formula. Is it possible to change this so that N/A being present in the green cell still allows cell to show Done
Thank you
https://ibb.co/yF1JGvY
https://ibb.co/hHMM5vZ
=((IF(I15="n/a",IF(H3="","",H3+60),IF(J16="Completed","DONE",IF(NOT(J16="completed"),IF(I16<>0,"PENDING",IF(I15="","",I15+182)))))))
7 Replies
- I am still stuck with this formula. I do not know why the cell changes back to date. I am thinking that it checks the first IF statement, and if that is true it doesnt worry about whats behind it. I would like for N/A to be the rule that changes this from a date to done
=((IF(I15="n/a",IF(H3="","",H3+60),IF(J16="Completed","DONE",IF(NOT(J16="completed"),IF(I16<>0,"PENDING",IF(I15="","",I15+182)))))))Yes, it is testing I15 for "n/a" and then processing the true condition for that IF statement (which is to test H3 for blank and either return blank or H3+60).
This is your logic for your outermost IF statement. The rest of the formula (after ELSE) is only applicable when I15 is not "n/a".
=IF(I15="n/a", THEN IF(H3="","", H3+60), ELSE IF(J16="Completed", "DONE", IF(NOT(J16="completed"), IF(I16<>0, "PENDING", IF(I15="","",I15+182)))))
It's hard to tell what someone is trying to do from a formula that does not work. Perhaps explain more what you're trying to do and someone can assist with how to write the formula. Also, you might include what version of Excel you're using.
Thank you for your response. I am using Excel 2016.
I am trying to create this formula for H16:
If I15 has 'N/A', populate H16 with date from H3, and add 60 days
If I15 has a date, populate H16 with that date and add 182 days.
If I16 has a date, H16 to display 'Pending'
If J16 is populated with dropdown of 'completed', H16 to display 'Done'.
Each rule would take priority over the one above it, if that makes sense. Thank you for your help with this. I have also tried working with the formula you had presented. However it is giving me a ?Name error.
Excel page:
Michael
