column similarity

[Damian]

I need to be able to rate the similarity of two columns, purely on a 'hit or
miss' basis on each cell. I apologise if this is confusing!

For example:
If column A looks like:
1

1
1
1

1

and column B looks like:

1
1

1
1
1

(where 1 is a 'hit' and a blank is a 'miss' for 7 bits of data) Is there a
way of comparing the two and achieving a value of similarity or dissimilarity
(ie % difference)?

Thank you anyone who got this far!

 

[Ivanl]

Not sure if this is what you want, but here goes:

you have 7 bits of data A1:A7
to compare with B1:B7

in cells C1:C7 input formula =ABS(A1-B1), ABS (A2-B2), etc. until row 7

in cell C8 use formula =SUM(C1:C7)/7 to get the percentage difference

 

[Ivanl]

you can also use an array formula

Not sure if this is what you want, but here goes:

you have 7 bits of data A1:A7
to compare with B1:B7

in cells C1:C7 input formula =ABS(A1-B1), ABS (A2-B2), etc. until row 7

in cell C8 use formula =SUM(C1:C7)/7 to get the percentage difference