• 470K Members
• 4,490 Online
• 568K Conversations

New Contributor

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

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

# 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies