Forum Discussion

Jahh2's avatar
Jahh2
Copper Contributor
Jan 21, 2021

Help with IFNA formula

I need to add a condition to this string and can not figure out how (I did not write it)

 

IFNA(IF(BX2="Y",BW2,IF(AND(BR2="Y",BS2="Y",BX2="N"),BO2,IF(AND(BR2="Y",BS2="N",BX2="N"),BO2,IF(AND(BR2="N",BS2="N",BX2="N"),BQ2,IF(AND(BR2="N",BS2="Y",BX2="N"),""))))),"")

 

I need to add 

BR2="N",BS2="Y",BX2="N" 

and for it to select the values in cell BQ2


TIA!

excel
office 365
training

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 21, 2021

    Jahh2 

    I'm lost with initial formula:

    =IFNA(
   IF(BX2="Y",BW2,
   IF(AND(BR2="Y",BS2="Y",BX2="N"),BO2,
   IF(AND(BR2="Y",BS2="N",BX2="N"),BO2,
   IF(AND(BR2="N",BS2="N",BX2="N"),BQ2,
   IF(AND(BR2="N",BS2="Y",BX2="N"),""))))),"")

    If we assume each cell could have Y or N and we have no extra formulas which could return NA result, it could be simplified to

    = IF(BX2="Y",BW2,
   IF(AND(BR2="Y"),BO2,
   IF(BS2="N",BQ2,"")))

    If we would like to handle other than Y and N values it'll be better to expand the formula returning something more than FALSE informative.

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Jan 21, 2021
    So, you want to return BQ2 if BR2="N" and BX2="N" and BS2 "Y" or "N"?

    Try:
    IFNA(IF(BX2="Y",BW2,IF(AND(BR2="Y",BS2="Y",BX2="N"),BO2,IF(AND(BR2="Y",BS2="N",BX2="N"),BO2,IF(AND(BR2="N",BX2="N",OR(BS2="N",BS2="Y")),BQ2,IF(AND(BR2="N",BS2="Y",BX2="N"),""))))),"")

Resources