why a reference to an empty cell is not considered empty

[Nicoscot]

I do not understand why a cell that contains a formula which result is empty
is not considered as an empty cell.
Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
=if(isblank(A2);"empty";"full") and the result in A3 is... "full". I do not
get it.
Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
=if(A2>0;"number higher than 0";"other") and the result in A3 is... "number
higher than 0"!!!!.... Even in the case that the value in A2 was 0 how could
it be strictly superior to 0!!! That makes no sense at all.
Please tell me how to create a reference to an empty cell whose result is
indeed an empty cell. I am going crazy.

[Trevor Shuttleworth]

A2: =IF(A1="","",A1)
A3: =IF(LEN(A2)=0,"empty","full")

Regards

Trevor


"Nicoscot" <[email protected]> wrote in message
news:[email protected]...
>I do not understand why a cell that contains a formula which result is
>empty
> is not considered as an empty cell.
> Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
> =if(isblank(A2);"empty";"full") and the result in A3 is... "full". I do
> not
> get it.
> Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I
> type
> =if(A2>0;"number higher than 0";"other") and the result in A3 is...
> "number
> higher than 0"!!!!.... Even in the case that the value in A2 was 0 how
> could
> it be strictly superior to 0!!! That makes no sense at all.
> Please tell me how to create a reference to an empty cell whose result is
> indeed an empty cell. I am going crazy.

[Elkar]

The presence of a formula in a cell makes that cell non-blank. Even if the
value returned is nothing, it is stlil a returned value.

I'm not clear as to what exactly you're trying to do, but how about using
double-quotes "" to find a blank value in a cell with a formula?

=IF(A2="","Empty","Full")

HTH,
Elkar



"Nicoscot" wrote:

> I do not understand why a cell that contains a formula which result is empty
> is not considered as an empty cell.
> Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
> =if(isblank(A2);"empty";"full") and the result in A3 is... "full". I do not
> get it.
> Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
> =if(A2>0;"number higher than 0";"other") and the result in A3 is... "number
> higher than 0"!!!!.... Even in the case that the value in A2 was 0 how could
> it be strictly superior to 0!!! That makes no sense at all.
> Please tell me how to create a reference to an empty cell whose result is
> indeed an empty cell. I am going crazy.

[Biff]

Stop pulling your hair out!

> Example: The cell A1 is empty

If A1 contains a formula that returns a formula blank: "", that cell IS NOT
EMPTY.

If A1 truly was EMPTY then: =A1 would return 0.

>=if(isblank(A2);"empty";"full")

The ISBLANK function does not recognize formula blanks as blank cells so
that's why you get "full".

=IF(A2="","empty","full") will return "empty".

> =if(A2>0;"number higher than 0";"other")

If A2 contains the formula =A1 and A1 contains a formula blank, then A2 = ""
which is a TEXT value. A TEXT value will ALWAYS evaluate to >0 so that's why
you get "number higher than 0".

Biff

"Nicoscot" <[email protected]> wrote in message
news:[email protected]...
>I do not understand why a cell that contains a formula which result is
>empty
> is not considered as an empty cell.
> Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
> =if(isblank(A2);"empty";"full") and the result in A3 is... "full". I do
> not
> get it.
> Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I
> type
> =if(A2>0;"number higher than 0";"other") and the result in A3 is...
> "number
> higher than 0"!!!!.... Even in the case that the value in A2 was 0 how
> could
> it be strictly superior to 0!!! That makes no sense at all.
> Please tell me how to create a reference to an empty cell whose result is
> indeed an empty cell. I am going crazy.

[Nicoscot]

Another way of seeing it is that when I really have an empty cell in A1 and
in A2 I have a formula that says =A1*10 (for example) the result is 0 ...
fair enough...

....but if I have in A1 a formula that says =if(ISBLANK(B1);"";B1) (because I
realized that if I only say =B1 and B1 is blank than the formula returns 0
instead of blank) and that B1 is indeed a real blank than I do not see any 0
in A1, as I wanted, but the result in A2 becomes #VALUE!.
When I want to see the calculation steps I see =""*10 with the explanation
the next evaluation will result in an error.

I do not understand why Excel cannot considere the result in A1 just as it
considers a real empty cell !?


"Elkar" wrote:

> The presence of a formula in a cell makes that cell non-blank. Even if the
> value returned is nothing, it is stlil a returned value.
>
> I'm not clear as to what exactly you're trying to do, but how about using
> double-quotes "" to find a blank value in a cell with a formula?
>
> =IF(A2="","Empty","Full")
>
> HTH,
> Elkar
>
>
>
> "Nicoscot" wrote:
>
> > I do not understand why a cell that contains a formula which result is empty
> > is not considered as an empty cell.
> > Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
> > =if(isblank(A2);"empty";"full") and the result in A3 is... "full". I do not
> > get it.
> > Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
> > =if(A2>0;"number higher than 0";"other") and the result in A3 is... "number
> > higher than 0"!!!!.... Even in the case that the value in A2 was 0 how could
> > it be strictly superior to 0!!! That makes no sense at all.
> > Please tell me how to create a reference to an empty cell whose result is
> > indeed an empty cell. I am going crazy.

[Nicoscot]

Thanks Biff
But it is hard to understand the rational behind "TEXT is considered as >0";
I know those are the rules but it does not make much sense...

"Biff" wrote:

> Stop pulling your hair out!
>
> > Example: The cell A1 is empty
>
> If A1 contains a formula that returns a formula blank: "", that cell IS NOT
> EMPTY.
>
> If A1 truly was EMPTY then: =A1 would return 0.
>
> >=if(isblank(A2);"empty";"full")
>
> The ISBLANK function does not recognize formula blanks as blank cells so
> that's why you get "full".
>
> =IF(A2="","empty","full") will return "empty".
>
> > =if(A2>0;"number higher than 0";"other")
>
> If A2 contains the formula =A1 and A1 contains a formula blank, then A2 = ""
> which is a TEXT value. A TEXT value will ALWAYS evaluate to >0 so that's why
> you get "number higher than 0".
>
> Biff
>
> "Nicoscot" <[email protected]> wrote in message
> news:[email protected]...
> >I do not understand why a cell that contains a formula which result is
> >empty
> > is not considered as an empty cell.
> > Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
> > =if(isblank(A2);"empty";"full") and the result in A3 is... "full". I do
> > not
> > get it.
> > Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I
> > type
> > =if(A2>0;"number higher than 0";"other") and the result in A3 is...
> > "number
> > higher than 0"!!!!.... Even in the case that the value in A2 was 0 how
> > could
> > it be strictly superior to 0!!! That makes no sense at all.
> > Please tell me how to create a reference to an empty cell whose result is
> > indeed an empty cell. I am going crazy.
>
>
>

[Nicoscot]

So the real question would be do you know any way of having a cell refer to
another cell and actually be considered as a really empty cell if the refered
cell is actually empty?
i.e. A1 is actually empty and my cell A2 which is refering to A1 is
considered just like A1.
Thanks a lot

"Biff" wrote:

> Stop pulling your hair out!
>
> > Example: The cell A1 is empty
>
> If A1 contains a formula that returns a formula blank: "", that cell IS NOT
> EMPTY.
>
> If A1 truly was EMPTY then: =A1 would return 0.
>
> >=if(isblank(A2);"empty";"full")
>
> The ISBLANK function does not recognize formula blanks as blank cells so
> that's why you get "full".
>
> =IF(A2="","empty","full") will return "empty".
>
> > =if(A2>0;"number higher than 0";"other")
>
> If A2 contains the formula =A1 and A1 contains a formula blank, then A2 = ""
> which is a TEXT value. A TEXT value will ALWAYS evaluate to >0 so that's why
> you get "number higher than 0".
>
> Biff
>
> "Nicoscot" <[email protected]> wrote in message
> news:[email protected]...
> >I do not understand why a cell that contains a formula which result is
> >empty
> > is not considered as an empty cell.
> > Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
> > =if(isblank(A2);"empty";"full") and the result in A3 is... "full". I do
> > not
> > get it.
> > Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I
> > type
> > =if(A2>0;"number higher than 0";"other") and the result in A3 is...
> > "number
> > higher than 0"!!!!.... Even in the case that the value in A2 was 0 how
> > could
> > it be strictly superior to 0!!! That makes no sense at all.
> > Please tell me how to create a reference to an empty cell whose result is
> > indeed an empty cell. I am going crazy.
>
>
>

[Biff]

>But it is hard to understand the rational behind "TEXT is considered as
> >0";
>I know those are the rules but it does not make much sense...

The software programmers did that!

More specifically, TEXT evaluates to > any number.

Biff

"Nicoscot" <[email protected]> wrote in message
news:[email protected]...
> Thanks Biff
> But it is hard to understand the rational behind "TEXT is considered as
> >0";
> I know those are the rules but it does not make much sense...
>
> "Biff" wrote:
>
>> Stop pulling your hair out!
>>
>> > Example: The cell A1 is empty
>>
>> If A1 contains a formula that returns a formula blank: "", that cell IS
>> NOT
>> EMPTY.
>>
>> If A1 truly was EMPTY then: =A1 would return 0.
>>
>> >=if(isblank(A2);"empty";"full")
>>
>> The ISBLANK function does not recognize formula blanks as blank cells so
>> that's why you get "full".
>>
>> =IF(A2="","empty","full") will return "empty".
>>
>> > =if(A2>0;"number higher than 0";"other")
>>
>> If A2 contains the formula =A1 and A1 contains a formula blank, then A2 =
>> ""
>> which is a TEXT value. A TEXT value will ALWAYS evaluate to >0 so that's
>> why
>> you get "number higher than 0".
>>
>> Biff
>>
>> "Nicoscot" <[email protected]> wrote in message
>> news:[email protected]...
>> >I do not understand why a cell that contains a formula which result is
>> >empty
>> > is not considered as an empty cell.
>> > Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
>> > =if(isblank(A2);"empty";"full") and the result in A3 is... "full". I do
>> > not
>> > get it.
>> > Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I
>> > type
>> > =if(A2>0;"number higher than 0";"other") and the result in A3 is...
>> > "number
>> > higher than 0"!!!!.... Even in the case that the value in A2 was 0 how
>> > could
>> > it be strictly superior to 0!!! That makes no sense at all.
>> > Please tell me how to create a reference to an empty cell whose result
>> > is
>> > indeed an empty cell. I am going crazy.
>>
>>
>>

[Biff]

The short answer is no.

If a cell has a formula that returns a blank, that cell is not empty.

Biff

"Nicoscot" <[email protected]> wrote in message
news:[email protected]...
> So the real question would be do you know any way of having a cell refer
> to
> another cell and actually be considered as a really empty cell if the
> refered
> cell is actually empty?
> i.e. A1 is actually empty and my cell A2 which is refering to A1 is
> considered just like A1.
> Thanks a lot
>
> "Biff" wrote:
>
>> Stop pulling your hair out!
>>
>> > Example: The cell A1 is empty
>>
>> If A1 contains a formula that returns a formula blank: "", that cell IS
>> NOT
>> EMPTY.
>>
>> If A1 truly was EMPTY then: =A1 would return 0.
>>
>> >=if(isblank(A2);"empty";"full")
>>
>> The ISBLANK function does not recognize formula blanks as blank cells so
>> that's why you get "full".
>>
>> =IF(A2="","empty","full") will return "empty".
>>
>> > =if(A2>0;"number higher than 0";"other")
>>
>> If A2 contains the formula =A1 and A1 contains a formula blank, then A2 =
>> ""
>> which is a TEXT value. A TEXT value will ALWAYS evaluate to >0 so that's
>> why
>> you get "number higher than 0".
>>
>> Biff
>>
>> "Nicoscot" <[email protected]> wrote in message
>> news:[email protected]...
>> >I do not understand why a cell that contains a formula which result is
>> >empty
>> > is not considered as an empty cell.
>> > Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
>> > =if(isblank(A2);"empty";"full") and the result in A3 is... "full". I do
>> > not
>> > get it.
>> > Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I
>> > type
>> > =if(A2>0;"number higher than 0";"other") and the result in A3 is...
>> > "number
>> > higher than 0"!!!!.... Even in the case that the value in A2 was 0 how
>> > could
>> > it be strictly superior to 0!!! That makes no sense at all.
>> > Please tell me how to create a reference to an empty cell whose result
>> > is
>> > indeed an empty cell. I am going crazy.
>>
>>
>>

[Nicoscot]

Well.... that means I am not over yet with my worksheet
Thanks anyway Biff

"Biff" wrote:

> The short answer is no.
>
> If a cell has a formula that returns a blank, that cell is not empty.
>
> Biff
>
> "Nicoscot" <[email protected]> wrote in message
> news:[email protected]...
> > So the real question would be do you know any way of having a cell refer
> > to
> > another cell and actually be considered as a really empty cell if the
> > refered
> > cell is actually empty?
> > i.e. A1 is actually empty and my cell A2 which is refering to A1 is
> > considered just like A1.
> > Thanks a lot
> >
> > "Biff" wrote:
> >
> >> Stop pulling your hair out!
> >>
> >> > Example: The cell A1 is empty
> >>
> >> If A1 contains a formula that returns a formula blank: "", that cell IS
> >> NOT
> >> EMPTY.
> >>
> >> If A1 truly was EMPTY then: =A1 would return 0.
> >>
> >> >=if(isblank(A2);"empty";"full")
> >>
> >> The ISBLANK function does not recognize formula blanks as blank cells so
> >> that's why you get "full".
> >>
> >> =IF(A2="","empty","full") will return "empty".
> >>
> >> > =if(A2>0;"number higher than 0";"other")
> >>
> >> If A2 contains the formula =A1 and A1 contains a formula blank, then A2 =
> >> ""
> >> which is a TEXT value. A TEXT value will ALWAYS evaluate to >0 so that's
> >> why
> >> you get "number higher than 0".
> >>
> >> Biff
> >>
> >> "Nicoscot" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I do not understand why a cell that contains a formula which result is
> >> >empty
> >> > is not considered as an empty cell.
> >> > Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
> >> > =if(isblank(A2);"empty";"full") and the result in A3 is... "full". I do
> >> > not
> >> > get it.
> >> > Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I
> >> > type
> >> > =if(A2>0;"number higher than 0";"other") and the result in A3 is...
> >> > "number
> >> > higher than 0"!!!!.... Even in the case that the value in A2 was 0 how
> >> > could
> >> > it be strictly superior to 0!!! That makes no sense at all.
> >> > Please tell me how to create a reference to an empty cell whose result
> >> > is
> >> > indeed an empty cell. I am going crazy.
> >>
> >>
> >>
>
>
>

[paul]

why dont you tell us what you are trying to "solve"Wether you consider the
cell to be empty when excel doesnt is of no consequence,you can trap that
"error" and use that as a condition too.isblank iserror isna etc etc
--
paul
remove nospam for email addy!



"Nicoscot" wrote:

> Well.... that means I am not over yet with my worksheet
> Thanks anyway Biff
>
> "Biff" wrote:
>
> > The short answer is no.
> >
> > If a cell has a formula that returns a blank, that cell is not empty.
> >
> > Biff
> >
> > "Nicoscot" <[email protected]> wrote in message
> > news:[email protected]...
> > > So the real question would be do you know any way of having a cell refer
> > > to
> > > another cell and actually be considered as a really empty cell if the
> > > refered
> > > cell is actually empty?
> > > i.e. A1 is actually empty and my cell A2 which is refering to A1 is
> > > considered just like A1.
> > > Thanks a lot
> > >
> > > "Biff" wrote:
> > >
> > >> Stop pulling your hair out!
> > >>
> > >> > Example: The cell A1 is empty
> > >>
> > >> If A1 contains a formula that returns a formula blank: "", that cell IS
> > >> NOT
> > >> EMPTY.
> > >>
> > >> If A1 truly was EMPTY then: =A1 would return 0.
> > >>
> > >> >=if(isblank(A2);"empty";"full")
> > >>
> > >> The ISBLANK function does not recognize formula blanks as blank cells so
> > >> that's why you get "full".
> > >>
> > >> =IF(A2="","empty","full") will return "empty".
> > >>
> > >> > =if(A2>0;"number higher than 0";"other")
> > >>
> > >> If A2 contains the formula =A1 and A1 contains a formula blank, then A2 =
> > >> ""
> > >> which is a TEXT value. A TEXT value will ALWAYS evaluate to >0 so that's
> > >> why
> > >> you get "number higher than 0".
> > >>
> > >> Biff
> > >>
> > >> "Nicoscot" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >I do not understand why a cell that contains a formula which result is
> > >> >empty
> > >> > is not considered as an empty cell.
> > >> > Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
> > >> > =if(isblank(A2);"empty";"full") and the result in A3 is... "full". I do
> > >> > not
> > >> > get it.
> > >> > Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I
> > >> > type
> > >> > =if(A2>0;"number higher than 0";"other") and the result in A3 is...
> > >> > "number
> > >> > higher than 0"!!!!.... Even in the case that the value in A2 was 0 how
> > >> > could
> > >> > it be strictly superior to 0!!! That makes no sense at all.
> > >> > Please tell me how to create a reference to an empty cell whose result
> > >> > is
> > >> > indeed an empty cell. I am going crazy.
> > >>
> > >>
> > >>
> >
> >
> >