Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Options for DIV#/0
I have a formula that is VERY long and contains three nested components that
require a 'division' function. Any of these three 'divisors' could equate to zero, which of course will cause a DIV/#0 error. I cannot have this, because the result of this cell needs to be used in other formulas, which would just permeate the issue. However, I do not want to nest 3 additional "IF(ISERROR...." statements within an already unwieldy formula to cover the 3 division components. Is there any sort of function that can be incorporated at the start of my formula that basically says "If the final result of this formula is an error, then return a zero, otherwise return the calculated result"? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Options for DIV#/0
You have right idea, but instead of doing 3 seperate checks, just do one.
Since you didn't give example of your formula... =IF(ISERROR(YourFormula),0,YourFormula) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "jday" wrote: I have a formula that is VERY long and contains three nested components that require a 'division' function. Any of these three 'divisors' could equate to zero, which of course will cause a DIV/#0 error. I cannot have this, because the result of this cell needs to be used in other formulas, which would just permeate the issue. However, I do not want to nest 3 additional "IF(ISERROR...." statements within an already unwieldy formula to cover the 3 division components. Is there any sort of function that can be incorporated at the start of my formula that basically says "If the final result of this formula is an error, then return a zero, otherwise return the calculated result"? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Options for DIV#/0
If you're using Excel 2007, then you can use the IFERROR function:
=IFERROR(your_formua,"") For older versions of Excel, you'll need to use the "unwieldly" approach. But you don't have to repeat it 3 times, just once. =IF(ISERROR(your_formula),"",your_formula) HTH Elkar "jday" wrote: I have a formula that is VERY long and contains three nested components that require a 'division' function. Any of these three 'divisors' could equate to zero, which of course will cause a DIV/#0 error. I cannot have this, because the result of this cell needs to be used in other formulas, which would just permeate the issue. However, I do not want to nest 3 additional "IF(ISERROR...." statements within an already unwieldy formula to cover the 3 division components. Is there any sort of function that can be incorporated at the start of my formula that basically says "If the final result of this formula is an error, then return a zero, otherwise return the calculated result"? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Options for DIV#/0
I have used a shorter version like this
if (A1*A2*A3<0,"YOUR FORMULA",0) Where A1,A2,A3 would be replaced by your three divisor. This would leave any other errors "intact" and only isolate the DIV#/0. I have fould this helpful since sometimes other errors crop up that you may wnat to see. -- If this helps, please remember to click yes. "Elkar" wrote: If you're using Excel 2007, then you can use the IFERROR function: =IFERROR(your_formua,"") For older versions of Excel, you'll need to use the "unwieldly" approach. But you don't have to repeat it 3 times, just once. =IF(ISERROR(your_formula),"",your_formula) HTH Elkar "jday" wrote: I have a formula that is VERY long and contains three nested components that require a 'division' function. Any of these three 'divisors' could equate to zero, which of course will cause a DIV/#0 error. I cannot have this, because the result of this cell needs to be used in other formulas, which would just permeate the issue. However, I do not want to nest 3 additional "IF(ISERROR...." statements within an already unwieldy formula to cover the 3 division components. Is there any sort of function that can be incorporated at the start of my formula that basically says "If the final result of this formula is an error, then return a zero, otherwise return the calculated result"? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Options for DIV#/0
I was not familiar with this new "IFERROR" function in Excel 2007. This is
exactly what I needed! The ISERROR version would work too, but the formula is 3 wrapped lines long, so to reference that formula twice within the cell would have been TOO MUCH!! Thanks for your help. "Elkar" wrote: If you're using Excel 2007, then you can use the IFERROR function: =IFERROR(your_formua,"") For older versions of Excel, you'll need to use the "unwieldly" approach. But you don't have to repeat it 3 times, just once. =IF(ISERROR(your_formula),"",your_formula) HTH Elkar "jday" wrote: I have a formula that is VERY long and contains three nested components that require a 'division' function. Any of these three 'divisors' could equate to zero, which of course will cause a DIV/#0 error. I cannot have this, because the result of this cell needs to be used in other formulas, which would just permeate the issue. However, I do not want to nest 3 additional "IF(ISERROR...." statements within an already unwieldy formula to cover the 3 division components. Is there any sort of function that can be incorporated at the start of my formula that basically says "If the final result of this formula is an error, then return a zero, otherwise return the calculated result"? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Options for DIV#/0
That is a great point too. My issue is that each of the 'divisors' is
actually a lengthy SUMIF statement -- not a cell reference or value. I will definitely keep this in mind for future use though---thanks! "Paul C" wrote: I have used a shorter version like this if (A1*A2*A3<0,"YOUR FORMULA",0) Where A1,A2,A3 would be replaced by your three divisor. This would leave any other errors "intact" and only isolate the DIV#/0. I have fould this helpful since sometimes other errors crop up that you may wnat to see. -- If this helps, please remember to click yes. "Elkar" wrote: If you're using Excel 2007, then you can use the IFERROR function: =IFERROR(your_formua,"") For older versions of Excel, you'll need to use the "unwieldly" approach. But you don't have to repeat it 3 times, just once. =IF(ISERROR(your_formula),"",your_formula) HTH Elkar "jday" wrote: I have a formula that is VERY long and contains three nested components that require a 'division' function. Any of these three 'divisors' could equate to zero, which of course will cause a DIV/#0 error. I cannot have this, because the result of this cell needs to be used in other formulas, which would just permeate the issue. However, I do not want to nest 3 additional "IF(ISERROR...." statements within an already unwieldy formula to cover the 3 division components. Is there any sort of function that can be incorporated at the start of my formula that basically says "If the final result of this formula is an error, then return a zero, otherwise return the calculated result"? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Options for DIV#/0
I was not familiar with this new "IFERROR" function in Excel 2007.
In the future, mention that you are using Excel 2007 when you ask your questions... that way, you can get answers that are targeted to your setup. -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More than 2 IF options | Excel Worksheet Functions | |||
IF with 3 options | Excel Worksheet Functions | |||
how do I add more toolbar options to my right click options | Excel Discussion (Misc queries) | |||
FTP options | Excel Discussion (Misc queries) | |||
what are my options? | Excel Discussion (Misc queries) |