software

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);quot;emptyquot;;quot;fullquot;) and the result in A3 is... quot;fullquot;. I do not
get it.
Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
=if(A2gt;0;quot;number higher than 0quot;;quot;otherquot;) and the result in A3 is... quot;number
higher than 0quot;!!!!.... 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.


A2: =IF(A1=quot;quot;,quot;quot;,A1)
A3: =IF(LEN(A2)=0,quot;emptyquot;,quot;fullquot;)

Regards

Trevorquot;Nicoscotquot; gt; wrote in message
...
gt;I do not understand why a cell that contains a formula which result is
gt;empty
gt; is not considered as an empty cell.
gt; Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
gt; =if(isblank(A2);quot;emptyquot;;quot;fullquot;) and the result in A3 is... quot;fullquot;. I do
gt; not
gt; get it.
gt; Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I
gt; type
gt; =if(A2gt;0;quot;number higher than 0quot;;quot;otherquot;) and the result in A3 is...
gt; quot;number
gt; higher than 0quot;!!!!.... Even in the case that the value in A2 was 0 how
gt; could
gt; it be strictly superior to 0!!! That makes no sense at all.
gt; Please tell me how to create a reference to an empty cell whose result is
gt; indeed an empty cell. I am going crazy.
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 quot;quot; to find a blank value in a cell with a formula?

=IF(A2=quot;quot;,quot;Emptyquot;,quot;Fullquot;)

HTH,
Elkar
quot;Nicoscotquot; wrote:

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

Stop pulling your hair out!

gt; Example: The cell A1 is empty

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

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

gt;=if(isblank(A2);quot;emptyquot;;quot;fullquot;)

The ISBLANK function does not recognize formula blanks as blank cells so
that's why you get quot;fullquot;.

=IF(A2=quot;quot;,quot;emptyquot;,quot;fullquot;) will return quot;emptyquot;.

gt; =if(A2gt;0;quot;number higher than 0quot;;quot;otherquot;)

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

Biff

quot;Nicoscotquot; gt; wrote in message
...
gt;I do not understand why a cell that contains a formula which result is
gt;empty
gt; is not considered as an empty cell.
gt; Example: The cell A1 is empty; in cell A2 I type =A1; in cell A3 I type
gt; =if(isblank(A2);quot;emptyquot;;quot;fullquot;) and the result in A3 is... quot;fullquot;. I do
gt; not
gt; get it.
gt; Other example:The cell A1 is empty; in cell A2 I type =A1; in cell A3 I
gt; type
gt; =if(A2gt;0;quot;number higher than 0quot;;quot;otherquot;) and the result in A3 is...
gt; quot;number
gt; higher than 0quot;!!!!.... Even in the case that the value in A2 was 0 how
gt; could
gt; it be strictly superior to 0!!! That makes no sense at all.
gt; Please tell me how to create a reference to an empty cell whose result is
gt; indeed an empty cell. I am going crazy.
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);quot;quot;;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 =quot;quot;*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 !?quot;Elkarquot; wrote:

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

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

quot;Biffquot; wrote:

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

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

quot;Biffquot; wrote:

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

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

The software programmers did that!

More specifically, TEXT evaluates to gt; any number.

Biff

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

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

Biff

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

quot;Biffquot; wrote:

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