asked on

Excel formula which leaves a cell truly blank (empty)

Hello,

When writing a formula in Excel (2010), how do you specify to leave a cell truly blank?

I am familiar with the double-quotation marks ("") used in Excel formula notation but although nothing is displayed, it is apparently not truly the same as a blank cell.

For example, suppose you've got a list of values (positive or negative) in column B and a formula in column C which returns the value in column B whenever it is positive—but for negative values in column B, it returns "". And then, suppose in column D, you insert a formula which displays a running balance for the entries in column C:

B2: any positive or negative number
C2: =IF(B2>0,B2,"")
D2: =D1+C2

In this case, the column D formula works fine as long as the values in column B are positive. But as soon as it encounters a negative value in column B (which results in an entry of "" in column C), it returns #VALUE!

I realize there are other ways to get around this problem (eg change "" to 0) in this simple example but the point of the question is to ask if there is an alternative to "" which behaves truly as a blank cell.

Thanks

Saqib Husain

Try

=sum(d1,c2)

ASKER CERTIFIED SOLUTION

Alan

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Steve_Brady

ASKER

>>>ssaqibh
Try
=sum(d1,c2)

Thanks for the response Syed.

Using the =SUM() function is one of the "other ways to get around this problem" I was referring to above. Following is an example with some screenshots which I'm hopeful will explain why:

Suppose you download the transactions from a typical bank account and paste them into a spreadsheet (Fig. 1):

Now suppose you decide to add three columns to show Debits, Credits & the Balance, respectively. Using formulas with double-quotation marks ("") (columns F & G in Fig. 2) as I described above, runs you into a problem with the balance formula (column H in Fig. 2):

As I mentioned, that can be fixed by replacing "" with 0 but then you must either hide zeros or deal with all the clutter (Fig. 3):

The alternative you suggested is to use =SUM() but obviously only if the equation is wrong (Fig. 4):

I don't know any way to introduce a subtraction operator into a =SUM() function (maybe that's what I should be asking about). I know that =SUM() can have the components specified as a range [eg =SUM(A1:A100)] or as individual cells—and in the latter, individual values can be preceded by a minus sign but the formula does not like -"" (minus double-quotation marks) (Fig. 5):

Your thoughts?