Can a function return a Null (blank ) value? Maybe a custom functi

[Guest]

Problem-

I have hit the problem in Excel, that any function that references a blank
cell destroys the "blankness" 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 "Cell value
is not 0" (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 "Keepblank()" 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?

 

[Guest]

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="","",A1)

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

 

[Guest]

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 "count only non-empty strings and numbers" 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 "<null>" for empty
cells; (b) Searches the target range for "<null>" 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

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="","",A1)

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


--

Gary's Student

Problem-

I have hit the problem in Excel, that any function that references a blank
cell destroys the "blankness" 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 "Cell value
is not 0" (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 "Keepblank()" 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?