Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! to read as 0
Is there a simple formula for displaying "0" as a result, not "#DIV/0!", when
cells are intentionally left blank or contain "0" as a divisor ? If so, how is it applied with a formula already in the cell? Hope someone's got the answer.. thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! to read as 0
PATTO wrote:
Is there a simple formula for displaying "0" as a result, not "#DIV/0!", when cells are intentionally left blank or contain "0" as a divisor ? If so, how is it applied with a formula already in the cell? Usually, the best way is to simply test the divisor. If you know D1 can be only blank or a number, you can do the following: =if(D1=0, 0, A1/D1) If the divisor is an expression, you might need to test it. For example: =if(D1+D2=0, 0, A1/(D1+D2)) If the divisor might contain a non-number, you might want to do something like the following: =if(N(D1)=0, 0, A1/D1) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! to read as 0
you can use an iserror such as
=IF(ISERROR(1/0),"",1/0) -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "PATTO" wrote: Is there a simple formula for displaying "0" as a result, not "#DIV/0!", when cells are intentionally left blank or contain "0" as a divisor ? If so, how is it applied with a formula already in the cell? Hope someone's got the answer.. thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! to read as 0
If you have a formula like =A1/B1 returning an unwanted #DIV/0! error then to
show zero instead =IF(B1,A1/B1,0) "John Bundy" wrote: you can use an iserror such as =IF(ISERROR(1/0),"",1/0) -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "PATTO" wrote: Is there a simple formula for displaying "0" as a result, not "#DIV/0!", when cells are intentionally left blank or contain "0" as a divisor ? If so, how is it applied with a formula already in the cell? Hope someone's got the answer.. thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! to read as 0
While this will work to display "" rather than #DIV/0 (though the OP
wanted to display 0), it will also cause any other errors to fail silently. Better to test the divisor: =IF(B1=0, 0, A1/B1) In article , John Bundy (remove) wrote: you can use an iserror such as =IF(ISERROR(1/0),"",1/0) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! to read as 0
Just for the record, in Excel 2007 you can use the new IFERROR function.
E.g., =IFERROR(x/y,"") It will return x/y is no error occurs, or an empty string if an error occurs. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "John Bundy" (remove) wrote in message ... you can use an iserror such as =IF(ISERROR(1/0),"",1/0) -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "PATTO" wrote: Is there a simple formula for displaying "0" as a result, not "#DIV/0!", when cells are intentionally left blank or contain "0" as a divisor ? If so, how is it applied with a formula already in the cell? Hope someone's got the answer.. thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! to read as 0
Chip Pearson wrote:
Just for the record, in Excel 2007 you can use the new IFERROR function. E.g., =IFERROR(x/y,"") It will return x/y is no error occurs, or an empty string if an error occurs. Kudos to Bill's Kids for finally recognizing the need for this. It avoids evaluating every expression twice (klunk!). Now, did they also increase the nested function depth to at least 8 ;-). |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! to read as 0
Now, did they also increase the nested function depth to at least 8 ;-).
Yes, they did. The limit now is, I think, 64 nested functions. I would never want to try to debug a formula with 64 levels of parens, but you can now write such a function. Sixty-four parens is )))))))))))))))))))))))))))))))))))))))))))))))))) )))))))))))))) It would be no small effort to try to match up opening and closing parens. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) wrote in message oups.com... Chip Pearson wrote: Just for the record, in Excel 2007 you can use the new IFERROR function. E.g., =IFERROR(x/y,"") It will return x/y is no error occurs, or an empty string if an error occurs. Kudos to Bill's Kids for finally recognizing the need for this. It avoids evaluating every expression twice (klunk!). Now, did they also increase the nested function depth to at least 8 ;-). |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
#DIV/0! to read as 0
Now, did they also increase the nested function depth to at least 8 ;-).
Here's some interesting late night reading... Improving Performance in Excel 2007 http://msdn2.microsoft.com/en-us/library/aa730921.aspx -- HTH :) Dana DeLouis Windows XP & Office 2003 wrote in message oups.com... Chip Pearson wrote: Just for the record, in Excel 2007 you can use the new IFERROR function. E.g., =IFERROR(x/y,"") It will return x/y is no error occurs, or an empty string if an error occurs. Kudos to Bill's Kids for finally recognizing the need for this. It avoids evaluating every expression twice (klunk!). Now, did they also increase the nested function depth to at least 8 ;-). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with dividing and using ISERROR for Div/0! | Excel Worksheet Functions | |||
How to hide #DIV/0! in cells waiting for input? | Excel Discussion (Misc queries) | |||
remove read only - not showing up in properties | Excel Worksheet Functions | |||
Send Excel File As Read Only | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |