ExcelBanter - #value

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - #value (https://www.excelbanter.com/excel-programming/289792-value.html)

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

Lee wrote:
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.

On Fri, 30 Jan 2004 15:16:06 -0800, "Lee" wrote:

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.

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.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

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

--
Message posted from http://www.ExcelForum.com

Lee

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

A <space would not be visible.

Try EditClearContents on C1

Gord Dibben Excel MVP

On Fri, 30 Jan 2004 15:16:06 -0800, "Lee" wrote:

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.

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!

---
Message posted from http://www.ExcelForum.com/

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-----
On Fri, 30 Jan 2004 15:16:06 -0800, "Lee"
wrote:

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.

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.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know
what to do.)
* Please keep all replies in this Newsgroup. Thanks! *
.

On Mon, 2 Feb 2004 11:08:36 -0800, "lee" wrote:

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.

Since IsBlank returns False, what do the formulas

=CODE(C1)

and

=LEN(C1)

return?

-----Original Message-----
On Fri, 30 Jan 2004 15:16:06 -0800, "Lee"
wrote:

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.

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.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *