Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
(B2-A2)=0; How to get a blank and NOT '0'
Hi,
When I do (B2-A2) I get a '0'. How can I have a blank cell as a result even if the answer is '0'? Thanks Regards Rum |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
(B2-A2)=0; How to get a blank and NOT '0'
=if((B2-A2)=0,"",B2-A2)
"Rum" wrote: Hi, When I do (B2-A2) I get a '0'. How can I have a blank cell as a result even if the answer is '0'? Thanks Regards Rum |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
(B2-A2)=0; How to get a blank and NOT '0'
Hi,
if(B2-A2=0,"",B2-A2) "Rum" wrote: Hi, When I do (B2-A2) I get a '0'. How can I have a blank cell as a result even if the answer is '0'? Thanks Regards Rum |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
(B2-A2)=0; How to get a blank and NOT '0'
"Rum" wrote:
When I do (B2-A2) I get a '0'. How can I have a blank cell as a result even if the answer is '0'? If you want the value of the cell to be "blank" (actually the null string) instead of zero, then: =if(B2-A2 = 0, "", B2-A2) Alternatively, simply use =B2-A2 and create the custom format "General;-General;" without quotes. You can replace "General" with whatever numeric format you wish, e.g. "0.00;-0.00;" for the equivalient of Number with 2 decimal places, but blank for zero. Using a custom format, the value in the cell is still zero; it just appears blank. Using the IF() expression, the value in the cell is the string "" instead of zero. That can cause complications in formulas that reference the cell value. PS: Beware that sometimes B2-A2 __appears__ zero, but it actually is not. Neither approach avoids displaying 0 in that case. Are you worried about that? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
(B2-A2)=0; How to get a blank and NOT '0'
On Jul 29, 7:08�pm, "JoeU2004" wrote:
<snip Alternatively, simply use =B2-A2 and create the custom format "General;-General;" without quotes. �You can replace "General" with whatever numeric format you wish, e.g. "0.00;-0.00;" for the equivalient of Number with 2 decimal places, but blank for zero. <snip Is that so ? Maybe in later versions of Excel but my Excel 97 needs the third specification to show zero. ie . . . 0.00;-0.00;"" The first element is the format for positive values, the second is for negative values and the third is for zero values. Strings in the format specifier are useful for special displays. eg . . . "Pay �"0.00;"Refund �"0.00;"Nothing to pay" .. . . instead of using a function formula to generate alternative cell display strings. Alan Lloyd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Excel 2002: Return blank when VLOOKUP on blank cells | Excel Discussion (Misc queries) | |||
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 do I make a blank cell with a date format blank? | Excel Worksheet Functions |