Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Zeros and #DIV/0!
In a cell I have formula that uses data from other cells to calculate values.
Before data is entered into those cells I always see either zeros or #DIV/0! on my spreadsheet. How do fix it so the cell with the formula is empty until I enter corresponding data in the other cells? thanks -- JD |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Zeros and #DIV/0!
#DIV/0 can be cleaned up by changing the formula to check for a zero
denominator. =if(C2=0, "", B2/C2) As for the zeros there are a couple of options. One is to not show any zero values on the sheet by selecting Tools - Options - View tab and uncheck Zero Values. The other option is to hide the zero's with condtional formatting. Select the cell range and then Format - Conditional Formatting - Cell Value is = 0 and format the text to be the same colour as the background (usually white)... -- HTH... Jim Thomlinson "JD" wrote: In a cell I have formula that uses data from other cells to calculate values. Before data is entered into those cells I always see either zeros or #DIV/0! on my spreadsheet. How do fix it so the cell with the formula is empty until I enter corresponding data in the other cells? thanks -- JD |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Zeros and #DIV/0!
You do something like this:
=if(iserror(yourformula),"",yourformula) Regards, Fred. "JD" wrote in message ... In a cell I have formula that uses data from other cells to calculate values. Before data is entered into those cells I always see either zeros or #DIV/0! on my spreadsheet. How do fix it so the cell with the formula is empty until I enter corresponding data in the other cells? thanks -- JD |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Zeros and #DIV/0!
Not to pick but IsError is kind of a dangerous formula to use in a case like
this. Any error and not just Div/0 will return a blank. If someone deletes a cell, instead of getting a #ref telling you there is a problem, you get a blank and no one knows there is a problem. While iserror is a handy formula it should actually be used very sparingly. To that end I actually can not remember the last time I used it and I spend a good part of my time creating speadsheets... -- HTH... Jim Thomlinson "Fred Smith" wrote: You do something like this: =if(iserror(yourformula),"",yourformula) Regards, Fred. "JD" wrote in message ... In a cell I have formula that uses data from other cells to calculate values. Before data is entered into those cells I always see either zeros or #DIV/0! on my spreadsheet. How do fix it so the cell with the formula is empty until I enter corresponding data in the other cells? thanks -- JD |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Zeros and #DIV/0!
Trap for the blank cells.
=IF(A1="","",B1/A1) or similar Gord Dibben MS Excel MVP On Fri, 5 Sep 2008 15:06:01 -0700, JD wrote: In a cell I have formula that uses data from other cells to calculate values. Before data is entered into those cells I always see either zeros or #DIV/0! on my spreadsheet. How do fix it so the cell with the formula is empty until I enter corresponding data in the other cells? thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Zeros and #DIV/0!
Thanks for the help,
I also posted the question on "AVERAGE WEEKLY MAXIMUMS" any ideas on that one? -- JD "JD" wrote: In a cell I have formula that uses data from other cells to calculate values. Before data is entered into those cells I always see either zeros or #DIV/0! on my spreadsheet. How do fix it so the cell with the formula is empty until I enter corresponding data in the other cells? thanks -- JD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when i megre cells that begin with zeros , I lose the zeros | Excel Discussion (Misc queries) | |||
Essbase: Text zeros to number zeros | Excel Discussion (Misc queries) | |||
Charting zeros as non zeros | Charts and Charting in Excel | |||
add zeros | Excel Worksheet Functions | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) |