Forum Discussion

Axsom's avatar
Axsom
Copper Contributor
Nov 30, 2019

I don't know if this can be done

Ok, I hope I can explain what I'm trying to do clear enough to get an answer.

The cell A1 can have one of 4 entries, <blank>, 2, 1, 0. I need to nest an IF statement so that if the entry is either left blank the reply will be blank, if it is 2, the reply would be 1, if it is 1 or 0 the reply would be 0. 

I know that =IF(A1=2,1,0) replies 1 if the entry is 2, and 0 for anything else (including blank) and that =IF(A1 <> "",A1,"") will reply blank if the cell is blank or the original value in the cell regardless of what it is. I have tried to nest these together but all I get is the original value put into the cell.

I have tried:

=IF(A1 <> "", A1, if(A1=2,1,0))

=IF(A1 <> "", A1,(IF(A1=2,1,0)))

Am I even close to being on the right track or do I need to do something completely different?

excel
Formulas and Functions

5 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Nov 30, 2019
    Try this:
    =IF(A1=“”,””,
    0+(A1=2))
    Note that the foregoing formula returns an empty text (“”), when A1 is either Blank or is Not Blank but contains empty text (“”), which may be the result of a formula therein.
    • Axsom's avatar
      Axsom
      Copper Contributor
      Nov 30, 2019

      SergeiBaklan 

       

      your's came close - I figured it out about 10mins before you replied
      =IFS(ISBLANK(A1)=TRUE,"",A1=2,1,A1=1,"",A1=0,0)

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 30, 2019

        Axsom 

        In addition to Twifoo  comments.

        You don't need to compare ISBLANK() with logical value, function itself returns it - TRUE or FALSE. You may use another but similar logic within IFS

        =IFS( LEN(A1)=0,"", A1=2,1, A1=1,0, A1=0,0, TRUE, "")

        It's unnecessary long formula, but better illustrates the logic.