equal result zero or blank

[andreisobaru]

Hi,

So when you put a formula in a cell that it is equal to another:
let's say in A1 you write =A2
and A2 is blank
then in A1 it appears 0
but sometimes, just sometimes, when you equal a cell to a blank cell, the first cell remains blank
what determines this behaviour? I need the cells to remain blank when I equal them to a blank cell
I am not looking for a solution like A1=if(A2=0,"",A2), this is not applicable in my case, I simply want to know what determines for the cell with the formula if it shows 0 or remains blank.

thanks,
Andrei

 

[andreisobaru]

perhaps I should mention that in my case the link (A1=A2) is made from one workbook to another (A1 is in one workbook and A2 in another), but I don't know if this influences the behaviour.
thanks

 

[silencer828]

Hi,

So when you put a formula in a cell that it is equal to another:
let's say in A1 you write =A2
and A2 is blank
then in A1 it appears 0
but sometimes, just sometimes, when you equal a cell to a blank cell, the first cell remains blank
what determines this behaviour? I need the cells to remain blank when I equal them to a blank cell
I am not looking for a solution like A1=if(A2=0,"",A2), this is not applicable in my case, I simply want to know what determines for the cell with the formula if it shows 0 or remains blank.

thanks,
Andrei

Try to check the cells format of A1, when it's number, it should show 0, if it's text, it should show blank

 

[andreisobaru]

yes, that was my train of thought too, I tried to format A1 same as A2, to no result. If I format A1 as text, it still shows 0, only it's aligned to the left
thanks anyway

 

[pgc01]

Hi,

So when you put a formula in a cell that it is equal to another:
let's say in A1 you write =A2
and A2 is blank
then in A1 it appears 0
but sometimes, just sometimes, when you equal a cell to a blank cell, the first cell remains blank
what determines this behaviour? I need the cells to remain blank when I equal them to a blank cell
I am not looking for a solution like A1=if(A2=0,"",A2), this is not applicable in my case, I simply want to know what determines for the cell with the formula if it shows 0 or remains blank.

thanks,
Andrei

Hi Andrei
Welcome to the board

What do you mean by "A2 is blank"?

With

=A2

if A2 is empty you'll get 0, else you get the value in A2.
Can you clarify?

 

[andreisobaru]

Hi,

A2 is blank as in you select A2 and press delete - so nothing there.
But I would like for A1 to be absolutely blank too when I equal it to A2, not to show a 0.

I have tried to upload a screen cap - hope it is visible, otherwise should be downloadable from here https://docs.google.com/file/d/0B_M5fVOscDOuWmRCNHhNNUtpUW8/edit?usp=sharing

in this screencap A1 equals A2 and shows 0
but B1 equals B2 and it remains blank
If it is still not clear I will try to provide more info

thanks

 

[pgc01]

Hi,

A2 is blank as in you select A2 and press delete - so nothing there.
But I would like for A1 to be absolutely blank too when I equal it to A2, not to show a 0.

If you say that you press delete in A2 then A2 will be empty.

In A1 you write a formula, therefore it can never be empty.

This means that what you want is impossible.

What you can do is for ex. to write an empty string in A1 when A2 is empty.

=IF(ISBLANK(A2),"",A2)

In this case you test if A2 is empty and, if it is true, you'll write an empty string in A1.

 

[andreisobaru]

thanks for the input. I will try to explain better.
it is correct - A2 will be empty
if I put A1=A2 (a formula), obviously in A1 will be written [=A2] - this will show in the formula bar
but in the cell, not in the formula bar (as the result, not as the formula), most often it will show 0 (if A2 is empty)
I want A1 to show a blank cell, not a 0 (this happens sometimes but I don't know by what rules)

thanks,
Andrei

If you say that you press delete in A2 then A2 will be empty.

In A1 you write a formula, therefore it can never be empty.

This means that what you want is impossible.

What you can do is for ex. to write an empty string in A1 when A2 is empty.

=IF(ISBLANK(A2),"",A2)

In this case you test if A2 is empty and, if it is true, you'll write an empty string in A1.

 

[andreisobaru]

https://docs.google.com/file/d/0B_M5fVOscDOuRTZGb09lMVdXWnM/edit?usp=sharing

I took another screen cap which I hope is more explicit (at the link above)
A1 has the formula =A2, and it shows zero
on the other hand, B1 (selected) has the formula =B2, and it shows a blank cell

this is what I want to find out: by which rules sometimes it shows zero and sometimes blanks

thanks,
Andrei

 

[Jonmo1]

Was B2 the result of a formula that returned "" ?
"" is NOT blank, it is a Null Text String
Even after you've done Copy / Paste special / Values on that cell.