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)
i try to use the formula you post, but there is an error as i execute it.
=IF(ISBLANK(A2),"",A2*3) is not a blank cell but an empty string
Takeshi wrote on December 31, 1969 19:00 EST
=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
wrong
youcantryreachingme (Chris) wrote on December 31, 1969 19:00 EST
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.
would love to know the answer
williamsbg wrote on July 20, 2006 08:01 EST
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 ""?
The answer is 42
williamsbg wrote on July 20, 2006 08:10 EST
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 ([url]http://tushar-mehta.com)[/url], 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
[url]http://www.geocities.com/jonpeltier/Excel/index.html[/url]
#NA isn't really a "blank cell" for sorting purposes
jfkelley wrote on August 15, 2007 09:14 EST
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.
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
Getting to Yes: Negotiating Agreement Without Giving In
