 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008OR 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) =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 > I'm trying o figure out why this formula isn't working as expected. > [quoted text clipped - 6 lines] > > =IF(OR(ISNA(F11),F11=0),"",F11) Correction all references to F12 should be F11 Tyro > =IF(ISNA(F12),"",IF(F12=0,"",F12)) > [quoted text clipped - 15 lines] >> >> =IF(OR(ISNA(F11),F11=0),"",F11) >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.  SignatureBiff Microsoft Excel MVP > =IF(ISNA(F12),"",IF(F12=0,"",F12)) > [quoted text clipped - 15 lines] >> >> =IF(OR(ISNA(F11),F11=0),"",F11) Yes the false path produces the N/A Tyro > >the formula evaluates as false, so you get #N/A as an answer > [quoted text clipped - 20 lines] >>> >>> =IF(OR(ISNA(F11),F11=0),"",F11) 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. SignatureBiff Microsoft Excel MVP > Yes the false path produces the N/A > [quoted text clipped - 24 lines] >>>> >>>> =IF(OR(ISNA(F11),F11=0),"",F11) 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 > >the formula evaluates as false, so you get #N/A as an answer > [quoted text clipped - 20 lines] >>> >>> =IF(OR(ISNA(F11),F11=0),"",F11) >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. SignatureBiff Microsoft Excel MVP > 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 [quoted text clipped - 28 lines] >>>> >>>> =IF(OR(ISNA(F11),F11=0),"",F11) 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,"") SignatureBiff Microsoft Excel MVP > I'm trying o figure out why this formula isn't working as expected. > [quoted text clipped - 6 lines] > > =IF(OR(ISNA(F11),F11=0),"",F11) Actually F11 is a text value. Is there another formula that might work besides =IF(COUNT(1/F11),F11,"")?? > The problem is that if the cell contains #N/A then: > [quoted text clipped - 27 lines] > > > =IF(OR(ISNA(F11),F11=0),"",F11) 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,"") SignatureBiff Microsoft Excel MVP > Actually F11 is a text value. Is there another formula that might work > besides =IF(COUNT(1/F11),F11,"")?? [quoted text clipped - 30 lines] >> >> > =IF(OR(ISNA(F11),F11=0),"",F11) I was simply addressing the OP's request, not presenting a panacea. Tyro > If you want to trap only #N/A errors but let other errors propagate then > you can use Tyro's formula. [quoted text clipped - 37 lines] >>> >>> > =IF(OR(ISNA(F11),F11=0),"",F11) 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. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > The problem is that if the cell contains #N/A then: > [quoted text clipped - 23 lines] >> >> =IF(OR(ISNA(F11),F11=0),"",F11) 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 SignatureRegads, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Biff, > [quoted text clipped - 30 lines] >>> >>> =IF(OR(ISNA(F11),F11=0),"",F11) >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. SignatureBiff Microsoft Excel MVP > 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 [quoted text clipped - 34 lines] >>>> >>>> =IF(OR(ISNA(F11),F11=0),"",F11) 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. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > >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. [quoted text clipped - 58 lines] >>>>> >>>>> =IF(OR(ISNA(F11),F11=0),"",F11) >Actually F11 is a text value. So much for my interpretation! Oh, well! I actually like it when folks ask for explanations. SignatureBiff Microsoft Excel MVP > 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. [quoted text clipped - 63 lines] >>>>>> >>>>>> =IF(OR(ISNA(F11),F11=0),"",F11) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.