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!

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


"Damian" wrote:

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!

you can also use an array formula

"Ivanl" wrote:

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


"Damian" wrote:

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!