Dont use blank cells as zero

[Nicholas Horn]

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 use the formula =A1 - A2 I get -5 which is right. But, I want D2 and D3 to either return a blank cell or better still - indicating no value. Many thanks in advance.

[#2]

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)

[#3]

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.

[#4]

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