Mystery Constant Causes #Value Error

[Dave]

I guess I stumped everyone in excel.worksheetfunctions group 'cause no
one answered July 28 post. Hope someone here has the answer.

A spreadsheet displays the #Value error in many cells. Each of these
cells contains a formula that simply sums 3 non-contiguous cells in the
same column e.g. =SUM(B16,B14,B12). 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, no data is visible in the
referenced cells (or formula bar when they are clicked on). There are
no named ranges that 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 (#Value) is observed. However, if I select the first two cells
referenced by the formula and press delete, the formula result becomes
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
relates to the combination of cells 2 and 3 (or they each contain a
constant). 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. However, one
cell on the sheet has the same format and it's formula result is 0, so
I don't think it's a formatting thing. For the life of me, I cannot
figure out the #Value error when nothing shows in the formula bar or
referenced cells. Does anyone have any clue how a cell could contain a
constant that can't be seen?
Thanks,
Dave

 

[Alan]

I guess I stumped everyone in excel.worksheetfunctions group 'cause
no one answered July 28 post. Hope someone here has the answer.

A spreadsheet displays the #Value error in many cells. Each of these
cells contains a formula that simply sums 3 non-contiguous cells in
the same column e.g. =SUM(B16,B14,B12). 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, no
data is visible in the referenced cells (or formula bar when they
are clicked on).
There are no named ranges that 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 (#Value) is observed. However, if I
select the first two cells referenced by the formula and press
delete, the formula result becomes
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 relates to the combination of cells 2 and 3 (or they each
contain a constant).
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. However,
one cell on the sheet has the same format and it's formula result is
0, so I don't think it's a formatting thing. For the life of me, I
cannot figure out the #Value error when nothing shows in the formula
bar or referenced cells. Does anyone have any clue how a cell could
contain a constant that can't be seen?
Thanks,
Dave

Could one of the cells contain a space (or multiple spaces)? That
might not appear in the formula bar, but cause the error?

HTH,

Alan.

 

[mangesh_yadav]

Try some of the following things.

For the first 2 cells in question, B14 and B16, try to see what does
the following return.
=ISNUMBER(B14)
=ISNUMBER(B16)
They should return TRUE for a number and you should not get any error.

Also check
=LEN(B14)
=LEN(B16)
If the cells don't contain anything, then this should return 0, and you
should not get the value error.

Formatting will never interfere with a formula.

Mangesh

 

[Arvi Laanemets]

Hi

Could one of the cells contain a space (or multiple spaces)? That
might not appear in the formula bar, but cause the error?

OP uses SUM(), not +. Sum function simply ignores any text values.

What happens, when only one of any 3 cells is summed? I.e. what do you get
with formulas like
=SUM(A1)
=SUM(A2)
=SUM(A3)
Maybe in such a way you can localize your problem.

 

[Alan]

Hi

OP uses SUM(), not +. Sum function simply ignores any text values.

Very good point - my apologies.

Alan.

 

[Lance G. Grey]

I'd check around for circular reference within the spreadsheet.

 

[Dave]

Alan, you were right. Using the Evaluate Formula tool on the formula
auditing toolbar, I noticed the reported evaluation of the first of the
3 cells was "" and the evaluation of the second was 0. When the math
was performed against these two, the #Value Error resulted. I clicked
in the first cell, then in the formula bar and hit backspace. To my
surprise, there was an empty string in the cell (a space). I guess a
resolution of 1024x768 will sometimes be my downfall because I looked
for that before, but didn't see it.
I learned some things from other posters in this thread as well. I see
this is the place to look for answers, not the other group. Thanks to
all!