Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested If not returning correct values
Hello:
I have the following formula: =IF((T18/G18<=0),"NA",IF(ISERROR(T18/G18),"NA",T18/G18)) It returns the correct values outside of the "ISERROR" section -- so when there is a division by "0" it gives you the standard "#DIV/0!" which I want to be replaced by "NA". Can you please review the formula and tell me what might be wrong that it still keeps it as "#DIV/0!"? Thank you. Monika |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested If not returning correct values
Think of the arguments of an IF() function as being evaluated from left to
right. So doesn't it make sense that you need test the error condition before testing a condition that might cause the error condition? =if(iserror(T18/G18),"NA",if(T18/G18<=0,"NA",T18/G18)) But if #DIV/0 is the only error you are worried about, you could write: =if(G18=0,"NA",if(T18/G18<=0,"NA",T18/G18)) ----- original message ----- "murkaboris" wrote in message ... Hello: I have the following formula: =IF((T18/G18<=0),"NA",IF(ISERROR(T18/G18),"NA",T18/G18)) It returns the correct values outside of the "ISERROR" section -- so when there is a division by "0" it gives you the standard "#DIV/0!" which I want to be replaced by "NA". Can you please review the formula and tell me what might be wrong that it still keeps it as "#DIV/0!"? Thank you. Monika |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested If not returning correct values
Hi Joe:
I've tried to reverse it and it comes back with an error now highlighting the "0" in the second portion of the formula after the "<="....any ideas? Thanks Monika "JoeU2004" wrote: Think of the arguments of an IF() function as being evaluated from left to right. So doesn't it make sense that you need test the error condition before testing a condition that might cause the error condition? =if(iserror(T18/G18),"NA",if(T18/G18<=0,"NA",T18/G18)) But if #DIV/0 is the only error you are worried about, you could write: =if(G18=0,"NA",if(T18/G18<=0,"NA",T18/G18)) ----- original message ----- "murkaboris" wrote in message ... Hello: I have the following formula: =IF((T18/G18<=0),"NA",IF(ISERROR(T18/G18),"NA",T18/G18)) It returns the correct values outside of the "ISERROR" section -- so when there is a division by "0" it gives you the standard "#DIV/0!" which I want to be replaced by "NA". Can you please review the formula and tell me what might be wrong that it still keeps it as "#DIV/0!"? Thank you. Monika |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested If not returning correct values
"murkaboris" wrote:
I've tried to reverse it and it comes back with an error now highlighting the "0" in the second portion of the formula after the "<="....any ideas? None, since you did not copy-and-paste here exactly what you entered for the formula. I believe you are talking about a syntax error -- a typo. If you copy-and-paste either of the formulas I provided, I believe you will have no problem. I don't. ----- original message ----- "murkaboris" wrote in message ... Hi Joe: I've tried to reverse it and it comes back with an error now highlighting the "0" in the second portion of the formula after the "<="....any ideas? Thanks Monika "JoeU2004" wrote: Think of the arguments of an IF() function as being evaluated from left to right. So doesn't it make sense that you need test the error condition before testing a condition that might cause the error condition? =if(iserror(T18/G18),"NA",if(T18/G18<=0,"NA",T18/G18)) But if #DIV/0 is the only error you are worried about, you could write: =if(G18=0,"NA",if(T18/G18<=0,"NA",T18/G18)) ----- original message ----- "murkaboris" wrote in message ... Hello: I have the following formula: =IF((T18/G18<=0),"NA",IF(ISERROR(T18/G18),"NA",T18/G18)) It returns the correct values outside of the "ISERROR" section -- so when there is a division by "0" it gives you the standard "#DIV/0!" which I want to be replaced by "NA". Can you please review the formula and tell me what might be wrong that it still keeps it as "#DIV/0!"? Thank you. Monika |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested If not returning correct values
Hi Joe:
You were right, not sure what it was with the "0" but when I copied yours it worked. Thanks for your help! Monika "JoeU2004" wrote: "murkaboris" wrote: I've tried to reverse it and it comes back with an error now highlighting the "0" in the second portion of the formula after the "<="....any ideas? None, since you did not copy-and-paste here exactly what you entered for the formula. I believe you are talking about a syntax error -- a typo. If you copy-and-paste either of the formulas I provided, I believe you will have no problem. I don't. ----- original message ----- "murkaboris" wrote in message ... Hi Joe: I've tried to reverse it and it comes back with an error now highlighting the "0" in the second portion of the formula after the "<="....any ideas? Thanks Monika "JoeU2004" wrote: Think of the arguments of an IF() function as being evaluated from left to right. So doesn't it make sense that you need test the error condition before testing a condition that might cause the error condition? =if(iserror(T18/G18),"NA",if(T18/G18<=0,"NA",T18/G18)) But if #DIV/0 is the only error you are worried about, you could write: =if(G18=0,"NA",if(T18/G18<=0,"NA",T18/G18)) ----- original message ----- "murkaboris" wrote in message ... Hello: I have the following formula: =IF((T18/G18<=0),"NA",IF(ISERROR(T18/G18),"NA",T18/G18)) It returns the correct values outside of the "ISERROR" section -- so when there is a division by "0" it gives you the standard "#DIV/0!" which I want to be replaced by "NA". Can you please review the formula and tell me what might be wrong that it still keeps it as "#DIV/0!"? Thank you. Monika |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested If not returning correct values
Try this:
=IF(N(G18),IF(T18/G180,T18/G18,"NA"),"NA") "murkaboris" wrote: Hello: I have the following formula: =IF((T18/G18<=0),"NA",IF(ISERROR(T18/G18),"NA",T18/G18)) It returns the correct values outside of the "ISERROR" section -- so when there is a division by "0" it gives you the standard "#DIV/0!" which I want to be replaced by "NA". Can you please review the formula and tell me what might be wrong that it still keeps it as "#DIV/0!"? Thank you. Monika |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup not returning correct value | Excel Worksheet Functions | |||
Match formula not returning correct values. | Excel Discussion (Misc queries) | |||
MAX Function not returning correct value | Excel Worksheet Functions | |||
VLOOKUP not returning correct value | Excel Worksheet Functions | |||
Vlookup not returning correct value | Excel Worksheet Functions |