#value

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

A "#Value" is shown in a cell with a formuka of "=1*C1"
where C1 has no visible value or character in there. If
put a "0" in C1, the "#Value" changed to "0." why?
thanks.
 
Hi Lee
are you sure, that C1 is empty. e.g. no spaces are in C1. Try the
following:
=ISBLANK(C1)
if this returns FALSE some values (probably blanks) are in C1 as the
formula =1*C1 would return '0' if C1 is blank

HTH
Frank
 
A "#Value" is shown in a cell with a formuka of "=1*C1"
where C1 has no visible value or character in there. If
put a "0" in C1, the "#Value" changed to "0." why?
thanks.
Click to expand...

You say "nothing visible", but that doesn't preclude the presence of
space character(s). Being text, those would be enough to yield the
error value. (I assume that you've checked that there's no formula
like ="" in there either? It wouldn't be visible in the cell (unless
you were in edit mode), but would be in the formula bar unless the
sheet is protected and the cell Protection property set to Hidden.)

One way to check is the formula =ISBLANK(C1). If that returns False,
then there's something in there whether you can see it or not.
 
You probably have an invisible character in there. A space o
something. Or your font COULD be the same color as your background
though if you see the '0' you enter than that's almost definitely no
the case unless you have some kind of conditional formatting on it.
Try doing a right click and "Clear Contents" - Piku
 
Lee

If C1 had a <space> in it, you would get the error.

A <space> would not be visible.

Try Edit>Clear>Contents on C1

Gord Dibben Excel MVP
 
I just KNOW there has to be at least one more person out there to reply
to this question in exactly the same way as we did! I bet ten dollars
his name is "JEFF"! - Pikus
<just kidding about the wager. I'm too poor to gamble!>
 
I can make it work easily, but I am interested in finding
out what's in there becasue there are a lot of these and
they are copied into excel from ACCESS output (I believe)
so I can eliminate the problem ealier on. Now, since I
can't attached the little example, I would just describe
it. Isblank will give false so there is something there
and I don't think it's a space. If I click my cursor at
the editing bar and click some other cell (without doing
any typing in the problem cell), all the formula using C1
works. Any idea? thanks.
 
I can make it work easily, but I am interested in finding
out what's in there becasue there are a lot of these and
they are copied into excel from ACCESS output (I believe)
so I can eliminate the problem ealier on. Now, since I
can't attached the little example, I would just describe
it. Isblank will give false so there is something there
and I don't think it's a space. If I click my cursor at
the editing bar and click some other cell (without doing
any typing in the problem cell), all the formula using C1
works. Any idea? thanks.
-----Original Message-----
Click to expand...
 
I can make it work easily, but I am interested in finding
out what's in there becasue there are a lot of these and
they are copied into excel from ACCESS output (I believe)
so I can eliminate the problem ealier on. Now, since I
can't attached the little example, I would just describe
it. Isblank will give false so there is something there
and I don't think it's a space. If I click my cursor at
the editing bar and click some other cell (without doing
any typing in the problem cell), all the formula using C1
works. Any idea? thanks.
Click to expand...

Since IsBlank returns False, what do the formulas

=CODE(C1)

and

=LEN(C1)

return?
 
Back
Top