Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement
Is there a way to simply IF statements that, for example, want to change the
result if a zero comes up. I have a formula with a long denominator. I don't want the DIV0 error so I just want to say "IF the (denominator formula) is 0, then make it 1. However, I don't want to type that whole formula that is full of lookups and offsets in twice to accomplish this (once to ask if it's zero, then again to set the value if it is not zero). If not, I think I'm just going to write it myself. CONDITIONALIF ([formula] , [value to check against] , [value if condition is met]) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement
You could just check the denominator.
=if(denom=0,"",numer/denom) or if you're using xl2007, you can use =iferror(). Arun wrote: Is there a way to simply IF statements that, for example, want to change the result if a zero comes up. I have a formula with a long denominator. I don't want the DIV0 error so I just want to say "IF the (denominator formula) is 0, then make it 1. However, I don't want to type that whole formula that is full of lookups and offsets in twice to accomplish this (once to ask if it's zero, then again to set the value if it is not zero). If not, I think I'm just going to write it myself. CONDITIONALIF ([formula] , [value to check against] , [value if condition is met]) -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement
Arun,
Su =IF("Denominator formula"=0,1,"Numerator formula"/"Denominator Formula") (be sure to remove the double quotes) Just copy & paste the denominator formula so you don't have to retype it. HTH, Conan "Arun" wrote in message ... Is there a way to simply IF statements that, for example, want to change the result if a zero comes up. I have a formula with a long denominator. I don't want the DIV0 error so I just want to say "IF the (denominator formula) is 0, then make it 1. However, I don't want to type that whole formula that is full of lookups and offsets in twice to accomplish this (once to ask if it's zero, then again to set the value if it is not zero). If not, I think I'm just going to write it myself. CONDITIONALIF ([formula] , [value to check against] , [value if condition is met]) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement
Hi Arun
Try: =IF(denominator formula=0,1,denominator formula) Rather than type it out twice, simply highlight the formula and copy and paste to the second location. This is a better idea as it also eliminates typos. HTH Michael M "Arun" wrote: Is there a way to simply IF statements that, for example, want to change the result if a zero comes up. I have a formula with a long denominator. I don't want the DIV0 error so I just want to say "IF the (denominator formula) is 0, then make it 1. However, I don't want to type that whole formula that is full of lookups and offsets in twice to accomplish this (once to ask if it's zero, then again to set the value if it is not zero). If not, I think I'm just going to write it myself. CONDITIONALIF ([formula] , [value to check against] , [value if condition is met]) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement
Post an example of one of the current formulas that needs the trap.
Could be several ways to go. Maybe even a macro to change them all at once with the trap added. Gord Dibben MS Excel MVP On Thu, 13 Dec 2007 13:44:06 -0800, Arun wrote: Is there a way to simply IF statements that, for example, want to change the result if a zero comes up. I have a formula with a long denominator. I don't want the DIV0 error so I just want to say "IF the (denominator formula) is 0, then make it 1. However, I don't want to type that whole formula that is full of lookups and offsets in twice to accomplish this (once to ask if it's zero, then again to set the value if it is not zero). If not, I think I'm just going to write it myself. CONDITIONALIF ([formula] , [value to check against] , [value if condition is met]) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement
Thank you for all the replies, but it is not an issue of the length of the
formula but rather the run time. The denominator of formula has multiple index, offset and lookup functions and searches a database of over 5000 entries. The formula is in over 100 different cells. The file already takes a a long time to calculate, but since it has to run the formula twice each time (once to check it is not 0 then again to get the value if it is not 0) I figured I could cut the run time in half by simplifying. Come to think of it, Dave Peterson's second suggestion using the iferror function will probably do it. Thanks again. Arun "Gord Dibben" wrote: Post an example of one of the current formulas that needs the trap. Could be several ways to go. Maybe even a macro to change them all at once with the trap added. Gord Dibben MS Excel MVP On Thu, 13 Dec 2007 13:44:06 -0800, Arun wrote: Is there a way to simply IF statements that, for example, want to change the result if a zero comes up. I have a formula with a long denominator. I don't want the DIV0 error so I just want to say "IF the (denominator formula) is 0, then make it 1. However, I don't want to type that whole formula that is full of lookups and offsets in twice to accomplish this (once to ask if it's zero, then again to set the value if it is not zero). If not, I think I'm just going to write it myself. CONDITIONALIF ([formula] , [value to check against] , [value if condition is met]) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
Help with if statement..... | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |