Forum Discussion

Mickalopagus's avatar
Mickalopagus
Copper Contributor
Sep 28, 2022

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, t...
excel
Formulas and Functions
Mickalopagus's avatar
Mickalopagus
Copper Contributor
Sep 28, 2022
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)))))))
JMB17's avatar
JMB17
Bronze Contributor
Sep 29, 2022

Mickalopagus 

 

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.

  • Mickalopagus's avatar
    Mickalopagus
    Copper Contributor
    Sep 29, 2022

    JMB17 

     

    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:

    https://ibb.co/Ykt5B6r 

     

    Michael

     

    • JMB17's avatar
      JMB17
      Bronze Contributor
      Sep 29, 2022

      Mickalopagus It sounds like the order should be reversed. Try:

       

      =IF(J16="completed", "Done", IF(I16<>"", "Pending", IF(I15<>"", I15+182, IF(I15="N/A", H$3+60, ""))))

      • Mickalopagus's avatar
        Mickalopagus
        Copper Contributor
        Sep 30, 2022
        JMB17, thank you I think this is the correct sequence in the formula! I will begin working with this. I did notice that IF(I15="N/A", H$3+60, "")))) comes back as "#value!"

        I am trying to determine what could be causing this as the formula looks correct. Just wondering if you see anything at a glance.

        Thank you,

        Michael

Resources