Formula shows as True, should shows as False

[Eduards]

Anyone got any ideas why my bottom cell comes out as True as it should be False, based on the formula 103OL is linked to 413OL not 400OL
Attached a workbook below.

I'm trying to identify which code is wrong in the list by having this formula next to it, instead of me visually looking at the data and try to spot a mistake.

[Stever7]

Edit (note for mods):

I assume it was okay at this point to reply as the user had edited the title? Apologies if I jumped the gun here and was supposed to wait for a mod to check and approve it.

Original post:

I've had a quick look at your formula is a bit confusing. You use the function "Right" a lot but I can't see why you're doing so and I get the feeling you don't quite understand how it works? Just in case:

Please Login or Register  to view this content.

Will return the characters of string X from the end (right hand side) until the number of characters Y. So for example;

Please Login or Register  to view this content.

Will return;

Please Login or Register  to view this content.

As it's starting at the right hand side and including all characters until character 3 (from the right with the final character being 1).

Notably, if the number of characters (Y from earlier) is not included, it will default to 1; thus giving only the final character in the provided string. You used RIGHT without Y and were checking if the input was equal to strings containing more than 1 character; that won't ever give a True result as a 1 character string can't equal a string with more than 1 character.

Next, your formula seemed a little muddled which I think was a result of the issue with using RIGHT. I've edited it and it now behaves like I think you want it to. So try putting the following in D1 and dragging it down;

Please Login or Register  to view this content.

If that isn't doing what you want it do, let me know as it's probably me just not understanding your problem fully!

[6StringJazzer]

Can you please describe what you want your formula to do?

In spite of Stever7's excellent analysis, I still can't understand what you want your formula to do. It has a couple of logic problems. You seem to expect it to return TRUE or FALSE, but if this condition is TRUE

OR($B1="396OL",$B1="400OL")

then the IF will return the string "101OL", but then you include that result as part of an AND. This will not cause an error, but it doesn't make sense to use "101OL" as a boolean value.

[Eduards]

Hi Stever,
I got this formula from one of the members. Thanks for your help i got what i need. 2 quick questions...

If I want to revert all true values to false and false values to true. Do I just put in =NOT?

What does the following do?
=$A1,$A1<>"")

[Eduards]

Idea:
Column A has set codes.
Within each code, there is a code inserted or represents another codes,however you want to say it, I'm sure there is a term for this.
For example:
101OL = either 396OL or 400OL

I want the formula to look at 101OL in cell A1 & compare it to cell B2.
If A1 = 101OL
and
B1 = 101OL
... then true

If A1 = 101OL
and
B1 = 400OL
... then true

If A1 = 101OL
and
B1 = 396OL
...then true

If A1 = 103OL (Or any other codes that not listed in formula)
and
B1 = 396OL or 400OL or 101OL
...then false

Hope this makes sense

[Stever7]

Hi Eduards,

If you want to toggle True<->False, then yes you can use the NOT function. This function works such that:

Please Login or Register  to view this content.

If X is True, then False is returned; and vice versa. Thus if you were to apply it to the code I posted earlier, you'd write it such that:

Please Login or Register  to view this content.

Otherwise, if you wanted to keep the output of the piece of code I posted earlier the same, and have a separate column with the toggled Boolean values then you'd want to put something like the below in say E1 and then drag down:

Please Login or Register  to view this content.

As for your other question, there are two different things in the bit of code you pasted; however I think you refer to just the final bit...

Please Login or Register  to view this content.

Yes? If so, I believe (hopefully one of the forum excel wizards can correct me if I'm not 100% correct on this) this simply checks A1 to see if it's got a length to it. This can be useful as the function:

Please Login or Register  to view this content.

Would return False if there's anything in the cell at all; including a simple apostrophe (which will disappear upon entry), formula, etc. The code you mentioned would return true if there's effectively no length to what is being displayed in the cell; this includes apostrophes and also functions that return "" and thus look empty, such as:

Please Login or Register  to view this content.

Hope that helps

[Eduards]

Just encountered a problem.
If i change any of the cells in B column to any number, the value comes out as true.

[Stever7]

Just encountered a problem.
If i change any of the cells in B column to any number, the value comes out as true.

Sorry, that's my fault, I overlooked a piece of the logic.

Okay, so 101OL is the valid A value for either 396OL or 400OL B values; yeah? And 103OL is the valid A value for a B value of 413OL; correct? Are they the only valid B values? Assuming so, this should correct the mistake I made;

Please Login or Register  to view this content.

This will give you more False values than before, as you have a few values in B that aren't either 396OL, 400OL or 413OL

[Eduards]

Also forgot to mention,
101OL is the valid A value for B value of 396OL / 400OL / and also 101OL (Duplicate are true values also)
103OL is the Valid A value for B value of 413OL / 103OL

there will be other codes, such as: 450OL
450OL will be just a 1 value and doesn't represent any other values as 101OL and 103OL.
So if A = 450OL and B = 450OL = True
if A= 450OL and B = any other value = false

[Stever7]

Okay, based on that I've updated the formula (see attached file). I've also split it up to have a helper column as I feel this will make it easier for you to maintain / add to.

The helper cell contains this equation:

Please Login or Register  to view this content.

So starting with the bit I'm not sure if you'll have encountered before;

Please Login or Register  to view this content.

The curly brackets signify an array, and basically what will happen here is it'll check to see if the contents of B2 is equal to each entry in that array and return True/False for each. So if B2 was "Spam" you'd get the result of:

Please Login or Register  to view this content.

Whereas, if B2 was "400OL" you'd get:

Please Login or Register  to view this content.

Hopefully that makes sense. The OR that wraps around it, basically looks at all the True/False values in the resultant array and will become True if any value in said array is True (so for the above cases, it would return False when B2 was "Spam" and True when B2 was "400OL").

If the first Or is True then it'll set the Helper Column value (D2 in this case) to "101OL" otherwise it will go on to check to see if the B2 value was a valid one for "103OL"; should that also fail then currently it will set the Helper Column value (D2 here) to False.

Next, the Output column contains the following:

Please Login or Register  to view this content.

Starting with the second part of this, the OR function, it's simply checking to see if the A value is either equal to the helper column value or the B value. If either of these are True then OR becomes True as this is a valid case; and if neither are True then OR becomes False.

The AND statement is there to just make sure the A value isn't blank; in all honesty I think this could be removed with how things have progressed but I've left it for now.

Finally, the If statement should be pretty straight forward and leads to the final result being given

The single column method is basically the same but all rolled up into one; I'd suggest using the helper column version tbh as it will be easier to update, etc.

P.S. I've just noticed that I made some cell references semi-absolute and others not; that was just an oversight.

[Eduards]

Perfection!
Explanation is great, makes perfect sense.

[Stever7]

Not a problem; glad I could help

[Eduards]

OR($B2={"369OL","400OL"})

If my code in cell B2 = 396OL
It returns false?
How so?

[Stever7]

OR($B2={"369OL","400OL"})

If my code in cell B2 = 396OL
It returns false?
How so?

Because I made a typo :P I put "369OL" not "396OL"

[Eduards]

Because I made a typo :P I put "369OL" not "396OL"

Got me confused then haha