# Inconsistent if(and(or results, HELP!!

1. ## 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!

2. ## Re: Inconsistent if(and(or results, HELP!!

You were almost there

``Please Login or Register  to view this content.``

3. ## 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. ## Re: Inconsistent if(and(or results, HELP!!

=IF(B2=" H";IF(C2="M";"Yes";IF(C2="L";"Yes";"No"));"No")

5. ## Re: Inconsistent if(and(or results, HELP!!

Hi L-Drr,

Sorry that one won't calculate.

6. ## Re: Inconsistent if(and(or results, HELP!!

It works for me, check the attachment. Maybe it's syntax.

7. ## 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. ## 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. ## Re: Inconsistent if(and(or results, HELP!!

Originally Posted by L-Drr
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. ## 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. ## Re: Inconsistent if(and(or results, HELP!!

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

12. ## 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. ## 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. ## 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.``

15. ## Re: Inconsistent if(and(or results, HELP!!

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

16. ## Re: Inconsistent if(and(or results, HELP!!

Originally Posted by oeldere
@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. ## Re: Inconsistent if(and(or results, HELP!!

Try this:

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

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

#### 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