Forum Discussion

Nicholas Horn's avatar
Nicholas Horn
Copper Contributor
Oct 17, 2018

Dont use blank cells as zero

I'm trying to do a simple calculation on a large spreadsheet e.g.        A          C        D 1     5          10      -5 2                 10      -10 3     -           10     -10   In D1 I u...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 17, 2018

Hi Nicholas,

 

Please clarify how do you receive -5 in D1? Your formula =A1-A2 gives 5. Next one will be =A2-A3 which is zero. Or you mean =A1-C1 ?

 

If the logic is to keep D empty if A is blank then

=IF(ISBLANK(A1),"",A1-C1)

 

 

  • Nicholas Horn's avatar
    Nicholas Horn
    Copper Contributor
    Oct 17, 2018

    Sorry I it's my typing error. It should read

    I'm trying to do a simple calculation on a large spreadsheet e.g.

           A          B        C

    1     5          10      -5

    2                 10      -10

    3     -           10     -10

     

    In C1 I use the formula =A1 - B1 I get -5 which is right. But, I want C2 and C3 to either return a blank cell or better still - indicating no value. Many thanks in advance.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 17, 2018

      Nicholas, when in C1 like

      =IF(ISBLANK(A1),"no value",A1-B1)

      or, perhaps, bit more universal

      =IF(ISNUMBER(A1),A1-B1,"no value")

      and drag C1 down

Resources