Hidden Constant Causes #Value Error?

[Dave]

Looked at a co-worker's spreadsheet today that displays the #Value
error in many similar cells. Each of these cells contains a formula
that simply sums a range of 3 non-contiguous cells in the same column
above the cell containing the formula. The formula toolbar indicates
the reason for the error is that a cell or cells in the range contains
a constant which cannot be evaluated. However, none of the cells
displays any value, constant or formula in the formula bar when clicked
on, and the cells are not part of a named range as far as I can see. If
I select each of the cells one at a time and press delete then undo the
deletion, no change in the formula result is observed. However, if I
select the first two cells referenced by the formula and press delete,
the formula result is 0. The #Value error is still displayed if I undo
those deletions and select cell 2 and 3 and click delete. It seems the
constant(s) somehow relate to the combination of cells 2 and 3 (or they
both contain constants of their own?). One other strange looking aspect
is that the cells seem to have a number format that shows Custom in the
list of formats and Custom_ (yes, that's an underscore) for the custom
format. For the life of me, I cannot figure out the #Value error when
nothing shows in the formula bar. Does anyone have any clue?
Thanks,
Dave

 

[Blue Hornet]

Well, you've given a lot of detail, but unfortunately left some out,
too. For example, what IS in the three non-contiguous cells being
summed? User-entered values? Formulas?

It sounds to me like the problem is in the first or second cell. (I
didn't understand your reasoning behind deleting the values in all
three cells, "and then undo the deletion". Why undo? I think you got
closer in your next example when you selected cells 1 & 2 and deleted,
to get a formula result of 0.

So, the third cell evaluates to 0, and we don't have a problem there.
What happens if you ONLY delete the value in cell 1, and don't undo?
Cell 2 only?

It seems to me that something is happening in one of those two cells,
and the Custom or Custom_ number format seems to point that way, too.
It's very possible (since you haven't shown the form of your Custom
formatting) that the value in that cell is hidden, or a non-displaying
error, or even something as simple as a "space" character. Is
Conditionnal Formatting turned on to avoid displaying a certain value,
or error? Is a formula returning some kind of text value?

You can reset all formats to General and turn off Conditional
Formatting and any existing patterns or color combinations to see
what's really in the cell. (And if the worksheet is protected, you may
need to unprotect so you can evaluate the contents that may be hidden
that way, too.)

Chris