Steve_Brady

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)

Thanks

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

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Thanks for the response Syed.>>>ssaqibh

Try

=sum(d1,c2)

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?

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Thanks for the great feedback.

=sum(d1,c2)