software

Problem-

I have hit the problem in Excel, that any function that references a blank
cell destroys the quot;blanknessquot; of the result. You can convert Null (a blank
cell) to zero, or an empty string, but these are not the same as blank!

In the original source range, I can use a conditional format of quot;Cell value
is not 0quot; (bizarrely, but it works) to highlight cells that contain any
number or text. I can also create subtotal lines and use the SubTotal count
function SubTotal(3, [Range]) to count the nonblank cells.

However if I make a duplicate copy of the range on another sheet using any
function, the function result is always converted to zero (which screws up
the subtotal count) or an empty string (that screws up the conditional
formats).

I tried writing a custom quot;Keepblank()quot; function, but even here if the
function returns a null value Excel is converting this to a zero.

This has to be an FAQ. Is there a way with functions, or do I have to write
VBA code to bulk-copy the range to retain the blank values?

You are 100% correct!! Normally users don't care about blankness, but
sometimes it really matters. Say you AVERAGE() cells A1 thru A10. AVERAGE()
ignores blanks. But if you link other cells to A1 thru A10 and try to
AVERAGE() them, you may not get the same result because the link will return
0 where the original is blank.

You can manage this however.

instead of using:
=A1 to make the link
use:
=IF(A1=quot;quot;,quot;quot;,A1)

What you are telling Excel is to always use A1 (if it's blank, then use a
blank)--

Gary's Studentquot;colin_equot; wrote:

gt; Problem-
gt;
gt; I have hit the problem in Excel, that any function that references a blank
gt; cell destroys the quot;blanknessquot; of the result. You can convert Null (a blank
gt; cell) to zero, or an empty string, but these are not the same as blank!
gt;
gt; In the original source range, I can use a conditional format of quot;Cell value
gt; is not 0quot; (bizarrely, but it works) to highlight cells that contain any
gt; number or text. I can also create subtotal lines and use the SubTotal count
gt; function SubTotal(3, [Range]) to count the nonblank cells.
gt;
gt; However if I make a duplicate copy of the range on another sheet using any
gt; function, the function result is always converted to zero (which screws up
gt; the subtotal count) or an empty string (that screws up the conditional
gt; formats).
gt;
gt; I tried writing a custom quot;Keepblank()quot; function, but even here if the
gt; function returns a null value Excel is converting this to a zero.
gt;
gt; This has to be an FAQ. Is there a way with functions, or do I have to write
gt; VBA code to bulk-copy the range to retain the blank values?

I'm familiar with this approach. However an empty string is not the same as a
blank cell (null).

I actually tried this early on. However it failed because of the issue with
subtotals.

The SUBTOTAL(3,,,) function actually does the equivalent of COUNTA(), empty
cells in the source range are not counted, but empty strings in the result
range ARE counted, because an empty string is still a string! Thus the
results are different

There is no quot;count only non-empty strings and numbersquot; SUBTOTAL function, so
this is a showstopper.

Currently I have hacked-up a deeply nasty solution in VBA, that (a)
Duplicates the source range, but inserts the magic string quot;lt;nullgt;quot; for empty
cells; (b) Searches the target range for quot;lt;nullgt;quot; and deletes the cell
contents. It works but it's not pretty, and it means you have to understand
VBA and named ranges.

Basically somewhere back in the mists of time someone, maybe in Lotus123 or
even VisiCalc, made the wrong decision, to have nulls propagate as zeroes,
and spreadsheets have been maintaining compatibility with this design error
ever since.

SQL databases for example do exactly the opposite. Nulls ALWAYS propagate as
nulls, and if you want to convert nulls to zeroes you have to say so.

Even if MicroSoft can't change the convention overnight, it's about time
they provided a way of making Excel behave in a way that preserves nulls.

In the meantime I was hoping for a function-level (vs. VBA) workaround.

Regards: Colin

quot;Gary''s Studentquot; wrote:

gt; You are 100% correct!! Normally users don't care about blankness, but
gt; sometimes it really matters. Say you AVERAGE() cells A1 thru A10. AVERAGE()
gt; ignores blanks. But if you link other cells to A1 thru A10 and try to
gt; AVERAGE() them, you may not get the same result because the link will return
gt; 0 where the original is blank.
gt;
gt; You can manage this however.
gt;
gt; instead of using:
gt; =A1 to make the link
gt; use:
gt; =IF(A1=quot;quot;,quot;quot;,A1)
gt;
gt; What you are telling Excel is to always use A1 (if it's blank, then use a
gt; blank)
gt;
gt;
gt; --
gt;
gt; Gary's Student
gt;
gt;
gt; quot;colin_equot; wrote:
gt;
gt; gt; Problem-
gt; gt;
gt; gt; I have hit the problem in Excel, that any function that references a blank
gt; gt; cell destroys the quot;blanknessquot; of the result. You can convert Null (a blank
gt; gt; cell) to zero, or an empty string, but these are not the same as blank!
gt; gt;
gt; gt; In the original source range, I can use a conditional format of quot;Cell value
gt; gt; is not 0quot; (bizarrely, but it works) to highlight cells that contain any
gt; gt; number or text. I can also create subtotal lines and use the SubTotal count
gt; gt; function SubTotal(3, [Range]) to count the nonblank cells.
gt; gt;
gt; gt; However if I make a duplicate copy of the range on another sheet using any
gt; gt; function, the function result is always converted to zero (which screws up
gt; gt; the subtotal count) or an empty string (that screws up the conditional
gt; gt; formats).
gt; gt;
gt; gt; I tried writing a custom quot;Keepblank()quot; function, but even here if the
gt; gt; function returns a null value Excel is converting this to a zero.
gt; gt;
gt; gt; This has to be an FAQ. Is there a way with functions, or do I have to write
gt; gt; VBA code to bulk-copy the range to retain the blank values?