|
OR function problem
I'm trying o figure out why this formula isn't working as expected.
I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
=IF(ISNA(F12),"",IF(F12=0,"",F12))
The OR function evaluates both contitions. If the cell F12 contains an error #N/A, the comparison F11=0 produces an error and the formula evaluates as false, so you get #N/A as an answer. You can see this using formula evaluator. Tyro "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
The problem is that if the cell contains #N/A then:
ISNA(F11) = TRUE But: F11=0 = #N/A Which causes OR to return #N/A I'm assuming from your description that F11 is supposed to be a *numeric value* but might be #N/A. Try this: =IF(COUNT(1/F11),F11,"") -- Biff Microsoft Excel MVP "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
Correction all references to F12 should be F11
Tyro "Tyro" wrote in message et... =IF(ISNA(F12),"",IF(F12=0,"",F12)) The OR function evaluates both contitions. If the cell F12 contains an error #N/A, the comparison F11=0 produces an error and the formula evaluates as false, so you get #N/A as an answer. You can see this using formula evaluator. Tyro "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
the formula evaluates as false, so you get #N/A as an answer
Not exactly. If OR evaluated to FALSE then the result of the formula would be the IF_value_if_false argument. -- Biff Microsoft Excel MVP "Tyro" wrote in message et... =IF(ISNA(F12),"",IF(F12=0,"",F12)) The OR function evaluates both contitions. If the cell F12 contains an error #N/A, the comparison F11=0 produces an error and the formula evaluates as false, so you get #N/A as an answer. You can see this using formula evaluator. Tyro "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
Yes the false path produces the N/A
Tyro "T. Valko" wrote in message ... the formula evaluates as false, so you get #N/A as an answer Not exactly. If OR evaluated to FALSE then the result of the formula would be the IF_value_if_false argument. -- Biff Microsoft Excel MVP "Tyro" wrote in message et... =IF(ISNA(F12),"",IF(F12=0,"",F12)) The OR function evaluates both contitions. If the cell F12 contains an error #N/A, the comparison F11=0 produces an error and the formula evaluates as false, so you get #N/A as an answer. You can see this using formula evaluator. Tyro "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
What I'm trying to say is this =IF(OR(ISNA(F11),F11=0),"",F11) That formula
evaluates as an error because the error condition in F11 creates an error when F11=0 is evaluated because F11 holds an error. I understand the basics of formula evaluation after 45 years as a systems programmer. Thank you. Tyro "T. Valko" wrote in message ... the formula evaluates as false, so you get #N/A as an answer Not exactly. If OR evaluated to FALSE then the result of the formula would be the IF_value_if_false argument. -- Biff Microsoft Excel MVP "Tyro" wrote in message et... =IF(ISNA(F12),"",IF(F12=0,"",F12)) The OR function evaluates both contitions. If the cell F12 contains an error #N/A, the comparison F11=0 produces an error and the formula evaluates as false, so you get #N/A as an answer. You can see this using formula evaluator. Tyro "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
Not exactly. The formula *never* gets to the value_if_false argument.
Once an error is generated that's where the formula stops and the result of the formula is the error (unless you've written the formula to account for that). For example, the formula I used intentionally generates an error under certain conditions but accounts for it. -- Biff Microsoft Excel MVP "Tyro" wrote in message et... Yes the false path produces the N/A Tyro "T. Valko" wrote in message ... the formula evaluates as false, so you get #N/A as an answer Not exactly. If OR evaluated to FALSE then the result of the formula would be the IF_value_if_false argument. -- Biff Microsoft Excel MVP "Tyro" wrote in message et... =IF(ISNA(F12),"",IF(F12=0,"",F12)) The OR function evaluates both contitions. If the cell F12 contains an error #N/A, the comparison F11=0 produces an error and the formula evaluates as false, so you get #N/A as an answer. You can see this using formula evaluator. Tyro "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
I understand the basics of formula evaluation after 45 years as a systems
programmer. You and I might know that but the OP and others reading this post might not know that so we should do our best to provide accurate explanations so that others may benefit. -- Biff Microsoft Excel MVP "Tyro" wrote in message et... What I'm trying to say is this =IF(OR(ISNA(F11),F11=0),"",F11) That formula evaluates as an error because the error condition in F11 creates an error when F11=0 is evaluated because F11 holds an error. I understand the basics of formula evaluation after 45 years as a systems programmer. Thank you. Tyro "T. Valko" wrote in message ... the formula evaluates as false, so you get #N/A as an answer Not exactly. If OR evaluated to FALSE then the result of the formula would be the IF_value_if_false argument. -- Biff Microsoft Excel MVP "Tyro" wrote in message et... =IF(ISNA(F12),"",IF(F12=0,"",F12)) The OR function evaluates both contitions. If the cell F12 contains an error #N/A, the comparison F11=0 produces an error and the formula evaluates as false, so you get #N/A as an answer. You can see this using formula evaluator. Tyro "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
Actually F11 is a text value. Is there another formula that might work
besides =IF(COUNT(1/F11),F11,"")?? On Mar 6, 10:54 pm, "T. Valko" wrote: The problem is that if the cell contains #N/A then: ISNA(F11) = TRUE But: F11=0 = #N/A Which causes OR to return #N/A I'm assuming from your description that F11 is supposed to be a *numeric value* but might be #N/A. Try this: =IF(COUNT(1/F11),F11,"") -- Biff Microsoft Excel MVP "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
If you want to trap only #N/A errors but let other errors propagate then you
can use Tyro's formula. This will trap all errors: =IF(ISTEXT(F11),F11,"") -- Biff Microsoft Excel MVP "luvnrocs" wrote in message ... Actually F11 is a text value. Is there another formula that might work besides =IF(COUNT(1/F11),F11,"")?? On Mar 6, 10:54 pm, "T. Valko" wrote: The problem is that if the cell contains #N/A then: ISNA(F11) = TRUE But: F11=0 = #N/A Which causes OR to return #N/A I'm assuming from your description that F11 is supposed to be a *numeric value* but might be #N/A. Try this: =IF(COUNT(1/F11),F11,"") -- Biff Microsoft Excel MVP "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
I was simply addressing the OP's request, not presenting a panacea.
Tyro "T. Valko" wrote in message ... If you want to trap only #N/A errors but let other errors propagate then you can use Tyro's formula. This will trap all errors: =IF(ISTEXT(F11),F11,"") -- Biff Microsoft Excel MVP "luvnrocs" wrote in message ... Actually F11 is a text value. Is there another formula that might work besides =IF(COUNT(1/F11),F11,"")?? On Mar 6, 10:54 pm, "T. Valko" wrote: The problem is that if the cell contains #N/A then: ISNA(F11) = TRUE But: F11=0 = #N/A Which causes OR to return #N/A I'm assuming from your description that F11 is supposed to be a *numeric value* but might be #N/A. Try this: =IF(COUNT(1/F11),F11,"") -- Biff Microsoft Excel MVP "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
No I was wrong there - if the return in F11 is 0, which may be a legal
return, your formula returns an empty string but =IF(COUNT(F11),F11,"") rteuns the zero -- Regads, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Biff, May I ask why you used =IF(COUNT(1/F11),F11,"") when =IF(COUNT(F11),F11,"") seems to work with numeric or #N/A returns in F11? I assumne that you had a good reason. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "T. Valko" wrote in message ... The problem is that if the cell contains #N/A then: ISNA(F11) = TRUE But: F11=0 = #N/A Which causes OR to return #N/A I'm assuming from your description that F11 is supposed to be a *numeric value* but might be #N/A. Try this: =IF(COUNT(1/F11),F11,"") -- Biff Microsoft Excel MVP "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
I need to check in a column to see if any cells have the number zero
or #NA. If either condition is true....make the destination cell blank. My interpretation of that is the value of F11 is either numeric or it may be #N/A. The OP specifically says if numeric 0 is present then leave the cell blank. =IF(COUNT(1/F11),F11,"") If F11 is numeric 0 (or empty), 1/F11 = #DIV/0!. COUNT returns 0, the logical_test evaluates to FALSE and leaves the cell blank. If F11 is #N/A, 1/F11 = #N/A. COUNT returns 0, the logical_test evaluates to FALSE and leaves the cell blank. If F11 is any number other than 0, 1/F11 = some number, COUNT returns 1, the logical_test evaluates to TRUE and returns the value of F11. Based on my interpretation that F11 is supposed to be a number, 1/F11 also acts as an error trap if F11 contains a TEXT entry. 1/text = #VALUE!, COUNT returns 0, the logical_test evaluates to FALSE and leaves the cell blank. -- Biff Microsoft Excel MVP "Sandy Mann" wrote in message ... No I was wrong there - if the return in F11 is 0, which may be a legal return, your formula returns an empty string but =IF(COUNT(F11),F11,"") rteuns the zero -- Regads, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Biff, May I ask why you used =IF(COUNT(1/F11),F11,"") when =IF(COUNT(F11),F11,"") seems to work with numeric or #N/A returns in F11? I assumne that you had a good reason. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "T. Valko" wrote in message ... The problem is that if the cell contains #N/A then: ISNA(F11) = TRUE But: F11=0 = #N/A Which causes OR to return #N/A I'm assuming from your description that F11 is supposed to be a *numeric value* but might be #N/A. Try this: =IF(COUNT(1/F11),F11,"") -- Biff Microsoft Excel MVP "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
My Apologies Biff. I never went back to the OP's first post to see what he
had said, which of course I should have before saying anything. Thank you for your tolerance and very full explanation. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "T. Valko" wrote in message ... I need to check in a column to see if any cells have the number zero or #NA. If either condition is true....make the destination cell blank. My interpretation of that is the value of F11 is either numeric or it may be #N/A. The OP specifically says if numeric 0 is present then leave the cell blank. =IF(COUNT(1/F11),F11,"") If F11 is numeric 0 (or empty), 1/F11 = #DIV/0!. COUNT returns 0, the logical_test evaluates to FALSE and leaves the cell blank. If F11 is #N/A, 1/F11 = #N/A. COUNT returns 0, the logical_test evaluates to FALSE and leaves the cell blank. If F11 is any number other than 0, 1/F11 = some number, COUNT returns 1, the logical_test evaluates to TRUE and returns the value of F11. Based on my interpretation that F11 is supposed to be a number, 1/F11 also acts as an error trap if F11 contains a TEXT entry. 1/text = #VALUE!, COUNT returns 0, the logical_test evaluates to FALSE and leaves the cell blank. -- Biff Microsoft Excel MVP "Sandy Mann" wrote in message ... No I was wrong there - if the return in F11 is 0, which may be a legal return, your formula returns an empty string but =IF(COUNT(F11),F11,"") rteuns the zero -- Regads, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Biff, May I ask why you used =IF(COUNT(1/F11),F11,"") when =IF(COUNT(F11),F11,"") seems to work with numeric or #N/A returns in F11? I assumne that you had a good reason. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "T. Valko" wrote in message ... The problem is that if the cell contains #N/A then: ISNA(F11) = TRUE But: F11=0 = #N/A Which causes OR to return #N/A I'm assuming from your description that F11 is supposed to be a *numeric value* but might be #N/A. Try this: =IF(COUNT(1/F11),F11,"") -- Biff Microsoft Excel MVP "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
OR function problem
Actually F11 is a text value.
So much for my interpretation! Oh, well! I actually like it when folks ask for explanations. -- Biff Microsoft Excel MVP "Sandy Mann" wrote in message ... My Apologies Biff. I never went back to the OP's first post to see what he had said, which of course I should have before saying anything. Thank you for your tolerance and very full explanation. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "T. Valko" wrote in message ... I need to check in a column to see if any cells have the number zero or #NA. If either condition is true....make the destination cell blank. My interpretation of that is the value of F11 is either numeric or it may be #N/A. The OP specifically says if numeric 0 is present then leave the cell blank. =IF(COUNT(1/F11),F11,"") If F11 is numeric 0 (or empty), 1/F11 = #DIV/0!. COUNT returns 0, the logical_test evaluates to FALSE and leaves the cell blank. If F11 is #N/A, 1/F11 = #N/A. COUNT returns 0, the logical_test evaluates to FALSE and leaves the cell blank. If F11 is any number other than 0, 1/F11 = some number, COUNT returns 1, the logical_test evaluates to TRUE and returns the value of F11. Based on my interpretation that F11 is supposed to be a number, 1/F11 also acts as an error trap if F11 contains a TEXT entry. 1/text = #VALUE!, COUNT returns 0, the logical_test evaluates to FALSE and leaves the cell blank. -- Biff Microsoft Excel MVP "Sandy Mann" wrote in message ... No I was wrong there - if the return in F11 is 0, which may be a legal return, your formula returns an empty string but =IF(COUNT(F11),F11,"") rteuns the zero -- Regads, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Biff, May I ask why you used =IF(COUNT(1/F11),F11,"") when =IF(COUNT(F11),F11,"") seems to work with numeric or #N/A returns in F11? I assumne that you had a good reason. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "T. Valko" wrote in message ... The problem is that if the cell contains #N/A then: ISNA(F11) = TRUE But: F11=0 = #N/A Which causes OR to return #N/A I'm assuming from your description that F11 is supposed to be a *numeric value* but might be #N/A. Try this: =IF(COUNT(1/F11),F11,"") -- Biff Microsoft Excel MVP "luvnrocs" wrote in message ... I'm trying o figure out why this formula isn't working as expected. I need to check in a column to see if any cells have the number zero or #NA. If either condition is true, OR should return "True" and then make the destination cell blank. If neither condition is met then OR returns "False" and the value from F11 is copied to G11. This formula is in cell G11: =IF(OR(ISNA(F11),F11=0),"",F11) |
| All times are GMT +1. The time now is 06:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com