Adjusting a Formula to Return a Blank, Rather than a Zero, When Operating on

Takeshi · 01-01-1970, 12:00 AM

=IF(ISBLANK(A2),"",A2*3) has some problem because =ISBLANK(IF(ISBLANK(A2),"",A2*3)) returns false while =ISTEXT(IF(ISBLANK(A2),"",A2*3)) returns true when A2 is blank

youcantryreachingme (Chris) · 01-01-1970, 12:00 AM

the previous comment is right - this is not leaving the cell blank, it is assigning an empty string.

i have a chart, and for charts you can tell excel how to handle blank cells - but in the case of a zero value or an empty string, the chart plots a zero value

what i want is a formula to properly return a blank cell (if conditions are correct) so that i can use my chart setting to inerpolate that value.

the supplied answer is just wrong.

**ExcelTip** · 08-23-2005, 03:40 AM

Problem:

List1 (column A) includes numbers as well as blank cells.
When using the following formula to multiply each number in List1 by 3:
=IF(ISBLANK(A2),,A2*3)
Zeros are returned for all empty cells.
We want to adjust the formula so that blanks are returned for all empty cells in List1.

Solution:

Use two quote marks (\"\"), as shown in the adjusted formula below, to indicate that a blank should be returned:
=IF(ISBLANK(A2),\"\",A2*3)

**AlieL** · 08-23-2005, 04:21 AM

i try to use the formula you post, but there is an error as i execute it.

**williamsbg** · 07-20-2006, 08:01 AM

Teh first question was important the answer was wrong. Let me restate it.

I have a formula:

=IF(some condition is true,5,"")

If the condition is true I want to plot the number 5. If it is false I want to treat the cell as though I had hit the delete key so that it ignores that data point completely. The question is: What do we need to put there in place of ""?

**williamsbg** · 07-20-2006, 08:10 AM

Just found the answer on another site! Here it is....

Rob -

Even if it looks blank, a cell with a formula isn't, it returns "",
which Excel plots as zero, like any text string. Replace the "" in the
formula with NA(), which returns an #N/A error in the cell. It looks
ugly in the sheet, but is interpolated over by the chart. Use
conditional formatting to hide the error in the worksheet. If the chart
has lines connecting the data, the line will go from the point before
#N/A to the point after; you can't make the line stop, to leave a gap.
Tushar Mehta has an addin on his web site that removes the formula with
#N/A by a blank cell (http://tushar-mehta.com), which helps the chart,
but removes the dynamic nature of a cell with a formula. We need MS to
invent a BLANK() or NULL() worksheet function.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html

**jfkelley** · 08-15-2007, 09:14 AM

My challenge:
I have a formula that calculates an integer value if all the antecedent cells are non-blank. Else, it puts a blank ("").

If I were Ascending sorting, this would be fine (put all blanks at the end). However, I am Descending sorting and all the "" cells come out on top!

I tried the NA() solution above, but the result is the same, they come out on top, i.e., #NA is not a true "blank cell". (BTW: If I go to my formula cells and Delete, achieving a True "blank cell", then the Descending sort works as I want it to, i.e., the nulls get sorted down underneath the cells with numeric values in them.)

Unfortunately, there is no ascii character (that I can find) that will sort underneath numbers in a Descending sort.