+ Reply to Thread
Results 1 to 17 of 17

Inconsistent if(and(or results, HELP!!

  1. #1
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Inconsistent if(and(or results, HELP!!

    Hi everyone,

    I've been working on this for 3 hours and I can't seem to figure it out. I have attached a sample template for your review. here's the problem:

    Column B: cells are either H, M, L (please note, there is space in front of each letter)
    Column C: cells are either H, M, L (please note, there are no spaces in front of each letter)
    Column D: I want a Yes answer if Column B shows an H (with a space in front) and Column C shows an M, OR, Column B shows an H (with a space in front) and Column C shows an L.

    I have the following formula:

    =IF(OR(AND(B2=" H",C2="M"),OR(B2=" H",C2="L")),"Yes","No")

    Here's the irritating thing: This formula works perfect for the first instances of H (with a space in front) and M, BUT NO OTHER INSTANCES OF IT!! H (with a space in front) and L works fine.

    HELP GURUS!!

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,146

    Re: Inconsistent if(and(or results, HELP!!

    You were almost there

    Please Login or Register  to view this content.
    Last edited by Pepe Le Mokko; 08-02-2013 at 11:41 AM.

  3. #3
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Inconsistent if(and(or results, HELP!!

    Hi Pepe,

    Thanks. However when I change the formula then rows with Column B = " H" and Column C = "L" shows No when it should be Yes.

  4. #4
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    Re: Inconsistent if(and(or results, HELP!!

    How about this formula:
    =IF(B2=" H";IF(C2="M";"Yes";IF(C2="L";"Yes";"No"));"No")

  5. #5
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Inconsistent if(and(or results, HELP!!

    Hi L-Drr,

    Sorry that one won't calculate.

  6. #6
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    Re: Inconsistent if(and(or results, HELP!!

    It works for me, check the attachment. Maybe it's syntax.
    Attached Files Attached Files
    When I say semicolon, u say comma!

  7. #7
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    Re: Inconsistent if(and(or results, HELP!!

    There seems to still be a mistake on row 25, but that's because B25 only looks like " H" but it isn't... (try typing " H" in B25, then it does work)

  8. #8
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Inconsistent if(and(or results, HELP!!

    Hi L-Drr,

    Thanks for taking the time to look into this. I've checked your template and you seem to have the same problem as me. The first H/M line (B2) gives the correct result as do the remaining H/L lines, however the remaining H/M lines (example, B41, B44) still give a No when they should be Yes like in B2.

  9. #9
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    Re: Inconsistent if(and(or results, HELP!!

    Quote Originally Posted by L-Drr View Post
    There seems to still be a mistake on row 25, but that's because B25 only looks like " H" but it isn't... (try typing " H" in B25, then it does work)
    That's what's happening. Some data only look like " H" but in fact aren't (they're probably something like " H ")

  10. #10
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    Re: Inconsistent if(and(or results, HELP!!

    If you want all values that start with " H" to be correct (in combination with M or L), tell me and I'll change the formula, it's easy

  11. #11
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    Re: Inconsistent if(and(or results, HELP!!

    Correction: if you want all values in column B that contain an H to be correct (...)

  12. #12
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Inconsistent if(and(or results, HELP!!

    Hi L-Drr,

    It seems like the leading space in the B column letters may be the underlying problem. I've tried to use the TRIM function to remove the space in the column but it doesn't seem to work. Any help would be greatly appreciated.

  13. #13
    Forum Contributor
    Join Date
    07-29-2013
    Location
    Oshawa
    MS-Off Ver
    Excel 2010
    Posts
    660

    Re: Inconsistent if(and(or results, HELP!!

    Hi L-Drr,

    Actually, thanks to your astute observation that some, but not all letters in column B had a leading space, I've expanded my original if/and/or formula to look for those with and without lead spaces. The results are not correct. Thanks so much !!

  14. #14
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Inconsistent if(and(or results, HELP!!

    @all

    in #4 there is also a leading space in the formula before the H.

    Maybe that's also causing the trouble.

    Please Login or Register  to view this content.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  15. #15
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    Re: Inconsistent if(and(or results, HELP!!

    If I understand correctly you've solved the problem? Great!

    Please don't forget to add reputaion and mark thread as solved

  16. #16
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    493

    Re: Inconsistent if(and(or results, HELP!!

    Quote Originally Posted by oeldere View Post
    @all

    in #4 there is also a leading space in the formula before the H.

    Maybe that's also causing the trouble.

    Please Login or Register  to view this content.
    I would go for this one:
    =IF(AND(ISERROR(FIND("H";B2))=FALSE;OR(C2="M";C2="L"));"Yes";"No")

    So all records containing an H in column B are correct

  17. #17
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Inconsistent if(and(or results, HELP!!

    Try this:

    =IF(AND(OR(B2=CHAR(160)&"H",B2=" H"),OR(C2={"M","L"})),"Yes","No")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. IF problem - inconsistent results
    By silvabod in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2013, 05:26 PM
  2. Inconsistent Calculation Results
    By thomasutley in forum Excel General
    Replies: 5
    Last Post: 04-26-2012, 12:14 AM
  3. Inconsistent IF Results
    By BW29 in forum Excel General
    Replies: 3
    Last Post: 12-20-2010, 10:12 AM
  4. [SOLVED] Inconsistent results with =LOOKUP?
    By watkincm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-31-2006, 03:25 PM
  5. Inconsistent results baffling
    By kcw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-01-2005, 11:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1