Create a 'Blank' Cell (2002)

[vikingpad]

I am trying to create a "blank" cell if certain conditions are met (divide by zero) so that the resultant value in the cell will trigger a "skip" when using the COUNTA function. As an example, I have a cell formula of =IF(ISBLANK(cell reference)," ",(fixed cell)/(cell reference)). If the cell reference is zero, the formula "should" return a blank.

Unfortunately, " " (double quotation mark-spacebar-double quotation mark) does not create a "blank" that the COUNTA function recognizes.

What is the correct way to create such a blank?

[HansV]

I'm afraid that there is no formula that will make a cell blank. A cell that contains a formula is by definition not empty, even if the formula returns an empty string ("" without a space in between). You'll have to find a different way to count non-blank cells. The test cellreference="" returns TRUE if the cell is really empty, but also if it contains a formula that results in "". If you have cells containing #N/A (or formulas resulting in #N/A), you must provide a way to handle those too.

For example: =SUM(IF(ISNA(A1:A14),0,IF(A1:A14="",0,1))), array-entered (confirm with Ctrl+Shift+Enter) will count the number of cells in A1:A14 that are not empty or "" or #N/A.

[vikingpad]

Thanks. With a little tweaking, your solution worked PERFECTLY. Thanks.

[frogmand]

I have a similar problem, however, I am actually summing a range of data that may oftem times contain #DIV/0! in the cell as well as #NA. I understand why some of my formulas may throw this out as a result and thats find, however, I still need the sum to work for the cells that actually have numbers in them. How can I write a formula that will exclude any cell that has #DIV/0! or #NA in any given range?

[HansV]

A slight variation on my reply to Vikingpad will probably do what you want.

To sum cells A1:A37, ignoring cells with text or error values, use

=SUM(IF(ISERROR(A1:A37),0,A1:A37))

It must be an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter.

[frogmand]

Great! Works exactly like I want.

Thanks.

LaMont

[madalyn]

A somewhat related question - I'm trying to graph some linked cells and I want the blanks in the original to show as blanks so they won't be plotted. I have a graph that changes as a user picks a different category from a drop-down control. The linked cells (that are the graph source) use indexing to find the correct row of data in the full data set. But if the full data set has blanks, the linked cells graph as zero's no matter what formula I use since the source is technically not blank. I could fill the source using a macro but I'd rather avoid that if possible so I won't have to deal with user education around security and opening a file with a macro. Any ideas?

[HansV]

You must use formulas that return #N/A if the original is blank. something like =IF(ISBLANK(A1),NA(),A1)
#N/A is the only value that will be treated as an empty cell by a chart.
See the thread starting at <post#=3686>post 3686</post#> for a discussion of this subject.

[madalyn]

Thanks - it works! I'd tried putting the #n/a in the original data, which worked but the customer didn't like. Forgot that I could do my link that way too.

[sdckapr]

A slight clarification:

#N/A is NOT equivalent to an empty cell in a plot

a #n/a error does NOT plot the point but the point BEFORE and AFTER will be connected if you have it formatted to "connect the dots". This is the ONLY error that this will work for; all others will plot as if the number is a zero.

A truly "empty cell" will NOT plot the point AND the LINE connecting the points will NOT be drawn "breaking the line". I have found no way to simulate this "blank cell" with a result, the ONLY way is to leave the cell BLANK/empty.

Steve

[HansV]

Thanks Steve, that is useful knowledge.