 Highlighted

# 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 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.

3 Replies
Highlighted

# Re: Dont use blank cells as zero

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)`

Highlighted

# Re: Dont use blank cells as zero

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.

Highlighted

# Re: Dont use blank cells as zero

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