Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
handling #Div/0! errors
Is there a smarter way to do this?
In order to avoid the unsightly #Div/0! appearing in printouts, where I do a divide, I almost always do this: =if(isnumber(A1/B1),A1/B1,"--") Is there a way to tell excel to always show a specific value (in my case "--") whenever a #Div/0! shows up, so that I don't have to handle the exceptions in the cell formulae myself? The example above is really too simple. Often the division are built from much more complicated calculations which can make the whole if statement a 200 letters long. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
handling #Div/0! errors
Under normal circumstances, the only time you should be getting a #DIV/0!
error is if the denominator is equal to zero, so that should be a sufficient test... =IF(B1=0,"--",A1/B1) For your more complicated formula, just isolate and test all the denominators for all of the divisions and test them for 0. For example, =IF(OR(B1=0,C1=0),"--",A1/B1+A1/C1) Rick "rockhammer" wrote in message ... Is there a smarter way to do this? In order to avoid the unsightly #Div/0! appearing in printouts, where I do a divide, I almost always do this: =if(isnumber(A1/B1),A1/B1,"--") Is there a way to tell excel to always show a specific value (in my case "--") whenever a #Div/0! shows up, so that I don't have to handle the exceptions in the cell formulae myself? The example above is really too simple. Often the division are built from much more complicated calculations which can make the whole if statement a 200 letters long. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
handling #Div/0! errors
Thank you, Rick & Dana.
It's good to hear excel 2007 has =iferror(). I'm still using 2003 and really hesitant to switch since I'll face major backward compatibility issues w/ my employer's & friends' systems. Thanks. "Dana DeLouis" wrote: =if(isnumber(A1/B1),A1/B1,"--") Excel 2007 has the newer function... =IFERROR(A1/B1,"--") -- HTH :) Dana DeLouis "rockhammer" wrote in message ... Is there a smarter way to do this? In order to avoid the unsightly #Div/0! appearing in printouts, where I do a divide, I almost always do this: =if(isnumber(A1/B1),A1/B1,"--") Is there a way to tell excel to always show a specific value (in my case "--") whenever a #Div/0! shows up, so that I don't have to handle the exceptions in the cell formulae myself? The example above is really too simple. Often the division are built from much more complicated calculations which can make the whole if statement a 200 letters long. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
handling #Div/0! errors
I just checked to confirm my recollection: Excel 2003 has ISERR, ISERROR,
and ERROR,TYPE. One or the other (or a combination) should do what you need. "rockhammer" wrote: Thank you, Rick & Dana. It's good to hear excel 2007 has =iferror(). I'm still using 2003 and really hesitant to switch since I'll face major backward compatibility issues w/ my employer's & friends' systems. Thanks. "Dana DeLouis" wrote: =if(isnumber(A1/B1),A1/B1,"--") Excel 2007 has the newer function... =IFERROR(A1/B1,"--") -- HTH :) Dana DeLouis "rockhammer" wrote in message ... Is there a smarter way to do this? In order to avoid the unsightly #Div/0! appearing in printouts, where I do a divide, I almost always do this: =if(isnumber(A1/B1),A1/B1,"--") Is there a way to tell excel to always show a specific value (in my case "--") whenever a #Div/0! shows up, so that I don't have to handle the exceptions in the cell formulae myself? The example above is really too simple. Often the division are built from much more complicated calculations which can make the whole if statement a 200 letters long. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Handling Errors When Opening Word Docs From Excel VBA | Excel Programming | |||
Handling Errors with Workbooks.OpenText | Excel Programming | |||
Handling Errors and MessageBox | Excel Programming | |||
Handling errors in formulas (how annoying are they!) | Excel Discussion (Misc queries) | |||
Handling Errors from Worksheet Functions | Excel Programming |