| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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,"") |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Empty strings appear as zeros | Charts and Charting in Excel | |||
| pivot tables, calculated fields, empty cells and zeros | Excel Discussion (Misc queries) | |||
| pivot tables, calculated fields, empty cells and zeros | Excel Discussion (Misc queries) | |||
| Average Function (include Blank Cells and Zeros) | Excel Discussion (Misc queries) | |||
| Lookup without empty cells. | Excel Discussion (Misc queries) | |||
Linear Mode
