Combining Two, IF AND Statements With OR Trouble

Mostly an issue of defining the 'blocks' and putting them together.

IF(OR((AND1),(AND2)),"WON","")

Heres a text version of the formula, for pasting in Row 1 of the table:

IF(OR(AND(RIGHT(A1,1)="1",RIGHT(B1,1)="3"),AND(RIGHT(B1,1)="1",RIGHT(A1,1)="3") ), "WON","")

Regards,

Barry

There’s another possibility, namely, are the cell contents numbers?

What if the cells contain, “Aaa1”, “Bbb3”? Would that still be a winner?

I modified Barry’s excellent example formula to add some data checking and reporting.

Here’s the modified formula:

IF(AND(ISNUMBER(A2),ISNUMBER(B2)),IF(OR(AND(RIGHT(A2,1)="1",RIGHT(B2,1)="3"),AND(RIGHT(B2,1)="1",RIGHT(A2,1)="3") ),"WON",""),"INVALID DATA")

I added a header row in my test table in Numbers, so the formula starts on row 2.

I tested copying the formula using Numbers Autofill and that passed.

This message would not post with my iPad screenshot of my test table in Numbers, but I did test the formula using your numbers and some alphanumeric data I added to test the formula on good and bad cell data.

Another option for you to consider or borrow from.

You can thank Barry for the majority of it. Without his example, I might not have taken the time to embark on this result. Funny how formulas can look so simple but take so long sometimes to properly develop and test for accuracy.

Regards,

John

You stated that the Excel formula you posted works in Excel but not Numbers. I believe that is not correct. The Excel formula will give the same answer in Numbers as in Excel (but in Numbers it will have a blue warning triangle in the cell telling you that you are using a boolean in place of a number). However, the formula does not match what you requested. It reads "if A1 ends with 1 or 3 and B1 ends with 1 or 3 then WON, else blank". Any combination of 1's and 3's (1-1, 1-3, 3-1, 3-3) will result in WON. You requested "if A1 ends with 1 and B1 ends with 3 or if A1 ends with 3 and B1 ends 1 then WON, else blank". In that version, only the combinations 1-3 and 3-1 result in WON.

I assume (as did Barry) you want the answer to your question, not the answer the Excel formula gives.

I believe you’re missing another possibility that you’re not testing for, namely, are the cell contents numbers?

What if the cells contain, “Aaa1”, “Bbb3”? Would that still be a winner?

The very first test in your formula should be if the cell contents are numbers. Correct? Or am I assuming too much? Whenever you’re validating data entry, invalid data should be the first thing you check for. If not valid data, then an error condition exists and the user needs to be told that what they typed is not acceptable.

Good programming always includes good error checking and error resolution.

Hi Johne,

You wrote: "I believe you’re missing another possibility that you’re not testing for, namely, are the cell contents numbers?"

True, but is the test necessary? From the OP's description of the situation, there appears to be no need for the cell contents to be treated differently depending on their being text or being numbers. The only specification given in the description is that one of the values ends with the character or digit "3" and the other ends with the character or digit "1".

Regards,

Barry

Hey Barry,

My comment, “I believe you’re missing another possibility…” wasn’t aimed at you. I was pointing out that possibility to the original poster. I realized later that I shouldn’t have said it like that. My apologies to you if I offended you.

I wasn’t being critical of your formula, not at all. In fact, your formula looks great and gave me an easy starting point to add what I added. I agree, is it worth the time. I guess only the user knows if the consequences of bad data leading to erroneous results is a big enough problem to worry about.

If I wasn’t retired with so much spare time and you hadn’t done most of the work for me, already, perhaps I wouldn’t have chimed in. It’s the old programmer in me that got me thinking of it, that’s all. I should have been more tactful. My bad.

Best regards,

John

No offence taken, John. Yours is a valid concern.

Looking forward to more posts from you in this community.

Regards,

Barry