Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making Zeros look like no entry
I have a spreadsheet that makes some calculation in a column, based on
multiplying numbers entered in 2 other columns. However, for some of the rows, there is no "input" data, and so the calculation simply indicates the answer is zero. This gives me a column with lots of zero's in the cells. I'd prefer there simply to be a blank cell, whenever the result of the formula is zero. Is there a way to achieve that? Thanks much. ddc |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making Zeros look like no entry
hi
you didn't give enough info to write a proper formula so i'll have to make one up. =if(A1+B1=0,"",A1+B1) or if the formala A1+B1 = 0, display nothing, else desplay the formula results. Regards FSt1 "DeeDeeCee" wrote: I have a spreadsheet that makes some calculation in a column, based on multiplying numbers entered in 2 other columns. However, for some of the rows, there is no "input" data, and so the calculation simply indicates the answer is zero. This gives me a column with lots of zero's in the cells. I'd prefer there simply to be a blank cell, whenever the result of the formula is zero. Is there a way to achieve that? Thanks much. ddc |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making Zeros look like no entry
If you prefer to hide ALL zeros throughout the WB just de-select the 'zero
values' in the 'Tools' 'Options' 'Vies" in order to avoid any formula(s) modification(s). Micky "DeeDeeCee" wrote: I have a spreadsheet that makes some calculation in a column, based on multiplying numbers entered in 2 other columns. However, for some of the rows, there is no "input" data, and so the calculation simply indicates the answer is zero. This gives me a column with lots of zero's in the cells. I'd prefer there simply to be a blank cell, whenever the result of the formula is zero. Is there a way to achieve that? Thanks much. ddc |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making Zeros look like no entry
Sorry...
This effects every sheet, by itself, which makes it more flexible. Micky If you prefer to hide ALL zeros throughout the WB just de-select the 'zero values' in the 'Tools' 'Options' 'Vies" in order to avoid any formula(s) modification(s). Micky "DeeDeeCee" wrote: I have a spreadsheet that makes some calculation in a column, based on multiplying numbers entered in 2 other columns. However, for some of the rows, there is no "input" data, and so the calculation simply indicates the answer is zero. This gives me a column with lots of zero's in the cells. I'd prefer there simply to be a blank cell, whenever the result of the formula is zero. Is there a way to achieve that? Thanks much. ddc |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making Zeros look like no entry
Custom Format the cells as: General;-General;
http://www.ozgrid.com/Excel/CustomFormats.htm Or, Under Excel Options hide zero values. -- Regards Dave Hawley www.ozgrid.com "DeeDeeCee" wrote in message ... I have a spreadsheet that makes some calculation in a column, based on multiplying numbers entered in 2 other columns. However, for some of the rows, there is no "input" data, and so the calculation simply indicates the answer is zero. This gives me a column with lots of zero's in the cells. I'd prefer there simply to be a blank cell, whenever the result of the formula is zero. Is there a way to achieve that? Thanks much. ddc |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Making Zeros look like no entry
Another option that I don't think has been mentioned yet:
Use Conditional Formatting on the column to set the font color to the same color as the cell (usually white) if the value in the cell is zero. This has the end effect of leaving zero in the cell if you need it for any calculations such as averaging, but not cluttering up the column with displayed zeros; and it's a little more precise in affecting only that column not all worksheets in hiding zeros. "DeeDeeCee" wrote: I have a spreadsheet that makes some calculation in a column, based on multiplying numbers entered in 2 other columns. However, for some of the rows, there is no "input" data, and so the calculation simply indicates the answer is zero. This gives me a column with lots of zero's in the cells. I'd prefer there simply to be a blank cell, whenever the result of the formula is zero. Is there a way to achieve that? Thanks much. ddc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making the AND function look at only part of a cell entry | Excel Discussion (Misc queries) | |||
how do you store real time in excel when making an entry | Excel Worksheet Functions | |||
automatically prefix cell entry with leading zeros | Excel Worksheet Functions | |||
How to program data entry to avoid zeros or last 5 digit. | Excel Discussion (Misc queries) | |||
Making Cell Entry Mandatory | Excel Discussion (Misc queries) |