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


"colin_e" wrote:

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?