Forum Home
Press F1
 
Thread ID: 54948 2005-02-26 19:46:00 XL formula B.M. (505) Press F1
Post ID Timestamp Content User
328462 2005-02-26 19:46:00 Can somebody show me how to write a simple enough XL formula that wont work for me. (Let me put my teeth in)

=if(a1=b1, or (a1=b2, and a2=b1 or a2=b2, then 1 if true and 0 if false.

The best I can get is #value, or I have too many arguments!! How does it know? :)

Cheers

Bob 		B.M. (505)
328463 2005-02-26 21:36:00 Hi Bob

This can definitely be done but you might need to clarify the if conditions - the and's and or's can get confusing.

The if condition works like this :

=if( test, value if true, value if false)

the or condition works like this :

or( test 1, test 2, optional test 3 and so on)

the and condition works like this :

and( test 1, test 2)

Applying this to your formula, it might look something like this :

=if( or( a1=b1, and ( a1=b2, or ( a2=b1 or a2=b2 ) ) ), 1 , 0 )

Interpretation : if a1 = b1 then return 1, alternatively if a1 = b2 then either a2 = b1 or a2 = b2 also have to be true to also return the result of 1.

I'm not sure if this is quite right so if it returns the incorrect result then please clarify how you want it to work (or try the alternative formula below).

An alternative formula (if the first doesn't work correctly) might be this :

=if( or( a1=b1, a2=b2, and ( a1=b2, a2=b1 ) ), 1 , 0 )

Interpretation : If either a1 = b1 or a2 = b2 then return 1, if neither of these are true then a1 must eual b2 and a2 must equal b1 to return the value 1.

HTH, Andrew :) 		andrew93 (249)
328464 2005-02-26 22:23:00 Thanks Andrew, close but no cigar. (yet) :)

Ok, let’s put the number 1 in A1 and 2 in A2

Then we put the numbers 2 in B1 and 1 in B2

Now in C1 we want a formula that produces 1 if the same two numbers appear in columns A & B in any order or 0 if they don’t.

So the above example would produce a 1 in C1 but if we change any of the numbers in A1,B1, A2, B2, to say 3 or any other number we return 0.

Is that any clearer of am I still talking scribble? :confused: 		B.M. (505)
328465 2005-02-27 00:49:00 Hi Bob

Now I understand a little more - but not 100%.

I think the following might be what you want :

=IF(OR(AND(A1=B2,A2=B1),AND(A1=B1,A2=B2)),1,0)

BUT, what about the following numbers? What would you want to return in this scenario?

Col A Col B
1 1
2 1

Andrew 		andrew93 (249)
328466 2005-02-27 01:04:00 Col A Col B
1 1
2 1


No problem Andrew, that can't happen.

I'm off to test your formula, back soon. :) 		B.M. (505)
328467 2005-02-27 01:26:00 You beaut Andrew. Works 100% (so far anyway) :)

I had the first AND before the OR and, I got the wrong answer! grrrrrrr!

Many thanks again Andrew.

Bob 		B.M. (505)
328468 2005-02-27 01:38:00 "no problem, Andrew, that can't happen" .

Them's dangerous words . ;) From a lot of programming experience I can assure you that if you don't handle the cases which "can't happen" they will happen .

If you are lucky, they crash the programme . If not, you get wrong answers, which since they come out of the computer are trusted . :groan:

You'ld be very surprised to see how often something which might happen once in 12 million operations actually occurs . 		Graham L (2)
1