Sheets is treating "0" like a blank cell

Question

I need help with what I'm sure is a simple formula that I just don't understand.

I have three columns. The first two hold my values and the third is meant to subtract them. However, when the first column is blank, I would like the third to also be blank. I have it close right now but when I type 0 in the first column, it treats that as a blank cell instead of using the 0 to give the sum.

Ex. of what I would like

expected result:
SCREEN SHOT OF WHAT I'M HOPING TO HAVE IT END UP LOOKING LIKE

I can't seem to figure out the formula for the third column.

Possible duplicate of https://stackoverflow.com/questions/18768020/if-statement-how-to-leave-cell-blank-if-condition-is-false-does-not-work — zx8754, Oct 23 '18 at 13:12

Bathsheba · Accepted Answer · 2018-10-24T15:41:01.800

2

Yes this is a quirk that goes right back to the dawn of spreadsheet applications; in an empty worksheet the formula =A1 written anywhere other than the top left cell will evaluate to 0.

One way, in Google Sheets, is to use something like

=IF(ISBLANK(A1), ,A1 - B1)

In Microsoft Excel you need to use double quotations characters in the second argument, noting that this injects a blank string into the output.

edited Oct 24 '18 at 15:41

answered Oct 23 '18 at 12:54

Bathsheba

231,907
34
361
483

Isn't it A1-B1? 8 - 9 = -1, not the other way around. – Oct 23 '18 at 12:58
@jeeeped The question does not specify but that is hardly the crux of the issue. – Bathsheba Oct 23 '18 at 12:59
You might to click on that link to an image of the sample data. – Oct 23 '18 at 13:00
1

@Jeeped I never click on external links, but I’ll change it round. – Bathsheba Oct 23 '18 at 13:01
1

In Google Sheets it's possible to return a blank instead of an empty text. i.e. `=IF(ISBLANK(A1), , A1 - B1))` – Rubén Oct 24 '18 at 00:50
Thank You! =IF(ISBLANK(A1), , A1 - B1) Worked perfectly as far as I can tell at this point! Also, thanks for the help editing my post...first post ;) – Justin Dunna McDonough Oct 24 '18 at 13:19

score 1 · Answer 2 · answered Oct 23 '18 at 12:57

1

This if statement (put in the 3rd Column) checks the first column if blank then just set cell as blank else perform the subtraction:- =IF(A1="", "", A1-B1)

answered Oct 23 '18 at 12:57

Yuppski

137
2
10

"" is an empty text not a blank. Use ISBLANK(A1) to test if a cell is blank. – Rubén Oct 24 '18 at 00:49

Sheets is treating "0" like a blank cell

2 Answers2