| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Zero if zero yet blank if blank?
I am trying to evaluate a cell and return zero if zero is put into the cell,
blank if the cell is left blank and evaluate a formula if a number other then zero is entered. I have tried: =IF(AJ7=0,0,(Formula) with and without conditional formating 0;-0;;@ as well as =IF(AJ10=0,0,IF(AJ10=",",",",(Formula))) as well as =IF(AJ10=0,0,IF(AJ10="","",(Formula))) It seems that a blank cell and a cell with a '0' in it are treated the same?? Does anyone have anymore ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero if zero yet blank if blank?
Try testing for the empty cell first...
=IF(A7="","",IF(A7=0,0,<<your formula)) Rick "OBlake" wrote in message ... I am trying to evaluate a cell and return zero if zero is put into the cell, blank if the cell is left blank and evaluate a formula if a number other then zero is entered. I have tried: =IF(AJ7=0,0,(Formula) with and without conditional formating 0;-0;;@ as well as =IF(AJ10=0,0,IF(AJ10=",",",",(Formula))) as well as =IF(AJ10=0,0,IF(AJ10="","",(Formula))) It seems that a blank cell and a cell with a '0' in it are treated the same?? Does anyone have anymore ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero if zero yet blank if blank?
Just change the order in which you test:
=IF(A1="","",IF(A1=0,0,A1)) Blank is treated as zero (in this case), but not the other way around! -- Kind regards, Niek Otten Microsoft MVP - Excel "OBlake" wrote in message ... |I am trying to evaluate a cell and return zero if zero is put into the cell, | blank if the cell is left blank and evaluate a formula if a number other then | zero is entered. | I have tried: | =IF(AJ7=0,0,(Formula) with and without conditional formating 0;-0;;@ | as well as | =IF(AJ10=0,0,IF(AJ10=",",",",(Formula))) | as well as | =IF(AJ10=0,0,IF(AJ10="","",(Formula))) | It seems that a blank cell and a cell with a '0' in it are treated the same?? | Does anyone have anymore ideas? | |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero if zero yet blank if blank?
If the cell is EMPTY, then you can use this to distinguish:
=IF(ISBLANK(AJ7),"",IF(AJ7=0,0,your_formula)) Hope this helps. Pete On Jun 29, 3:26*pm, OBlake wrote: I am trying to evaluate a cell and return zero if zero is put into the cell, * blank if the cell is left blank and evaluate a formula if a number other then zero is entered. I have tried: =IF(AJ7=0,0,(Formula) with and without conditional formating 0;-0;;@ as well as =IF(AJ10=0,0,IF(AJ10=",",",",(Formula))) as well as =IF(AJ10=0,0,IF(AJ10="","",(Formula))) It seems that a blank cell and a cell with a '0' in it are treated the same?? Does anyone have anymore ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero if zero yet blank if blank?
=IF(A1="","",IF(A1=0,0,<formula))
OBlake wrote: I am trying to evaluate a cell and return zero if zero is put into the cell, blank if the cell is left blank and evaluate a formula if a number other then zero is entered. I have tried: =IF(AJ7=0,0,(Formula) with and without conditional formating 0;-0;;@ as well as =IF(AJ10=0,0,IF(AJ10=",",",",(Formula))) as well as =IF(AJ10=0,0,IF(AJ10="","",(Formula))) It seems that a blank cell and a cell with a '0' in it are treated the same?? Does anyone have anymore ideas? -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Zero if zero yet blank if blank?
Thanks! Works perfectly!
"Niek Otten" wrote: Just change the order in which you test: =IF(A1="","",IF(A1=0,0,A1)) Blank is treated as zero (in this case), but not the other way around! -- Kind regards, Niek Otten Microsoft MVP - Excel "OBlake" wrote in message ... |I am trying to evaluate a cell and return zero if zero is put into the cell, | blank if the cell is left blank and evaluate a formula if a number other then | zero is entered. | I have tried: | =IF(AJ7=0,0,(Formula) with and without conditional formating 0;-0;;@ | as well as | =IF(AJ10=0,0,IF(AJ10=",",",",(Formula))) | as well as | =IF(AJ10=0,0,IF(AJ10="","",(Formula))) | It seems that a blank cell and a cell with a '0' in it are treated the same?? | Does anyone have anymore ideas? | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
| Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
| Not showing blank and non blank items in filter mode for values | Excel Worksheet Functions | |||
| how to get excel to display blank if reference cell blank | Excel Worksheet Functions | |||
| How do I make a blank cell with a date format blank? | Excel Worksheet Functions | |||
Linear Mode
