How do get the LOOKUP function to not read empty cells as zeros
 

I am trying to reverse code 0s and 1s with =LOOKUP (reference cell,
{0,1},{1,0}). When the reference cell is empty, I'd like the function to
also return an empty cell.

How do get the LOOKUP function to not read empty cells as zeros
 

Hi,

why are you using lookup?

=IF(A1=0,1,IF(A1=1,0,""))


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"LGriffin" wrote:

I am trying to reverse code 0s and 1s with =LOOKUP (reference cell,
{0,1},{1,0}). When the reference cell is empty, I'd like the function to
also return an empty cell.

How do get the LOOKUP function to not read empty cells as zeros
 

Maybe

=IF(ISBLANK(A1),"",LOOKUP(A1,{0,1},{1,0}))

Mike

"LGriffin" wrote:

I am trying to reverse code 0s and 1s with =LOOKUP (reference cell,
{0,1},{1,0}). When the reference cell is empty, I'd like the function to
also return an empty cell.

How do get the LOOKUP function to not read empty cells as zeros
 

"LGriffin" wrote:
I am trying to reverse code 0s and 1s with =LOOKUP (reference cell,
{0,1},{1,0}). When the reference cell is empty, I'd like the function to
also return an empty cell.

=if(A1="","",if(A1,0,1))

treats all non-zero A1 as 1, changing them to 0.

A formula cannot "return an empty cell". It can only make a cell __appear__
blank by returning a null string.

For that reason, use A1="", not ISBLANK(A1), to test for a blank cell.
ISBLANK() returns true only when the cell is truly empty -- that is, no
formula and no value. A1="" returns true in that case as well as when the
value in A1 is the null string, such as the result of IF() expression like
the above.

How do get the LOOKUP function to not read empty cells as zeros
 

"Shane Devenshire" wrote:
=IF(A1=0,1,IF(A1=1,0,""))

Returns 1 when A1 is empty. But the OP wrote: "When the reference cell is
empty, I'd like the function to also return an empty cell".

Of course, a formula cannot "return an empty cell". But it can return a
null string.


----- original message -----

"Shane Devenshire" wrote in
message ...
Hi,

why are you using lookup?

=IF(A1=0,1,IF(A1=1,0,""))


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"LGriffin" wrote:

I am trying to reverse code 0s and 1s with =LOOKUP (reference cell,
{0,1},{1,0}). When the reference cell is empty, I'd like the function to
also return an empty cell.

How do get the LOOKUP function to not read empty cells as zeros
 

Try one of these formula:

=--NOT(A1)
or
=--(A1<1)




"LGriffin" wrote:

I am trying to reverse code 0s and 1s with =LOOKUP (reference cell,
{0,1},{1,0}). When the reference cell is empty, I'd like the function to
also return an empty cell.

How do get the LOOKUP function to not read empty cells as zeros
 

Another one...

=DELTA(A1)


"LGriffin" wrote:

I am trying to reverse code 0s and 1s with =LOOKUP (reference cell,
{0,1},{1,0}). When the reference cell is empty, I'd like the function to
also return an empty cell.

How do get the LOOKUP function to not read empty cells as zeros
 

.....and another

=1-A1


"LGriffin" wrote:

I am trying to reverse code 0s and 1s with =LOOKUP (reference cell,
{0,1},{1,0}). When the reference cell is empty, I'd like the function to
also return an empty cell.

How do get the LOOKUP function to not read empty cells as zeros
 

Teethless mama wrote...
....and another

=1-A1
....

Expand for blank cells to

=IF(COUNT(A1),1-A1,"")

How do get the LOOKUP function to not read empty cells as zeros
 

Improvement....

I wrote:
=if(A1="","",if(A1,0,1))

=if(A1="", "", --NOT(A1))


----- original message -----

"JoeU2004" wrote in message
...
"LGriffin" wrote:
I am trying to reverse code 0s and 1s with =LOOKUP (reference cell,
{0,1},{1,0}). When the reference cell is empty, I'd like the function to
also return an empty cell.

=if(A1="","",if(A1,0,1))

treats all non-zero A1 as 1, changing them to 0.

A formula cannot "return an empty cell". It can only make a cell
__appear__ blank by returning a null string.

For that reason, use A1="", not ISBLANK(A1), to test for a blank cell.
ISBLANK() returns true only when the cell is truly empty -- that is, no
formula and no value. A1="" returns true in that case as well as when the
value in A1 is the null string, such as the result of IF() expression like
the above.