PDA

View Full Version : Yet another XL formula rqd.

B.M.
28-02-2005, 08:41 AM
I give up, I think senility has well and truly set in!

All I want to do is return a 1 if three numbers in column A match three numbers in column B, regardless of order, and 0 if they dont.

Ok:

A B

1 3
2 2
3 1

This I want to produce a 1

A B
1 3
2 15
3 1

This I want to produce a 0

Now, quite why I cant get this to work eludes me, so rather than tear out what little remains of my hair Ill seek advice. :)

Cheers

Bob

beama
28-02-2005, 08:48 AM
=if(a1=b1,1,0)

is I think what you need first result after the condition a1=b1 is if its true 1 and if false 0 secound result

B.M.
28-02-2005, 09:15 AM
Not quite Beama.

A1,and A2, and A3, must match B1, and B2, and B3, but in no particular order.

andrew93
28-02-2005, 09:18 AM
Hi Bob

You can do this with a slight variation on the formula from your previous question. But this time there are 6 conditions to test for, rather than just the 2 conditions in your previous question.

Using the previous formula as the basis, you could use something like this :

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

but that sort of formula will get pretty long-winded if you want to extend your list of numbers much further. If you end up trying to compare great long lists of numbers, then you could try something fancy like this instead :

=IF( AND( SUM(A1:A3) = SUM(B1:B3), MIN(A1:A3) = MIN(B1:B3), MAX(A1:A3) = MAX(B1:B3), AVERAGE(A1:A3) = AVERAGE(B1:B3)), 1,0)

but there is no guarantee it will work 100% of the time for longer lists of numbers.

I'm sure Parry will know a smart function or two to work this out for any length of numbers (no pressure Parry! :D )

HTH, Andrew :)

beama
28-02-2005, 11:46 AM
vlookup maybe ill have a play later sorry busy at the moment

B.M.
28-02-2005, 01:12 PM
Well done again Andrew, that worked a treat.

I was working along the same lines but including (IFS) which was working fine until I got to the eighth IF where I got a big but and no maybe. :)

Beama, I considered trying lookup myself but I think the lookup list has to be in order and in this case that was not guaranteed. Thanks anyway.

Bob

Parry
28-02-2005, 01:32 PM
Hi, those are some very clever formulas Andrew. :D

I dont think this is better but just a different approach: -

=AND(SUM(A1:A3)=SUM(B1:B3),(MATCH(B1,\$A\$1:\$A\$3,0)+ MATCH(B2,\$A\$1:\$A\$3,0)+MATCH(B3,\$A\$1:\$A\$3,0))=6)

The logic is...

Match will return the position within the range, so if all the positions added together equal 6 (1+2+3) then they must be the same. The sum is required because match will return a hit if say the range b1:b3 was 2,2,2 but if they add up to the same amount then they must be the same numbers (if my arithmatic is correct).

regards
Graham

EDIT: btw, this returns True if they are matched or #NA if they are not. This could easily be turned into 1's and 0's if necessary.

Parry
28-02-2005, 02:00 PM
Heres with 1's and 0's...

=IF(AND(SUM(A1:A3)=SUM(B1:B3),(MATCH(B1,\$A\$1:\$A\$3, 0)+ MATCH(B2,\$A\$1:\$A\$3,0)+MATCH(B3,\$A\$1:\$A\$3,0))=6),1, 0)