Comparing Data Values and Scoring Similarities

[jebben]

Hello,

I have a large Excel file. I want to compare data from five different
columns in each row. I want to see which of the values is most similar to
the value in another column. It is also possible that the values will be
totally different and not all that similar to one another. I would like to
score this data on a scale, where a value of 1 would indicate similarity to
the first condition with a value of 5 indicating similarity with the last
condition. I know this question may be somewhat unclear---I am just not
really sure how to do this. What I have is a spectrum of values that go up
moving left to right across the sheet, and I want to know where the data in
the next column fits in the spectrum. I could do this by hand, but there are
close to 3,000 rows to go through.

Thanks for any help you can provide!

 

[Victor Delta]

Hello,

I have a large Excel file. I want to compare data from five different
columns in each row. I want to see which of the values is most similar to
the value in another column. It is also possible that the values will be
totally different and not all that similar to one another. I would like
to
score this data on a scale, where a value of 1 would indicate similarity
to
the first condition with a value of 5 indicating similarity with the last
condition. I know this question may be somewhat unclear---I am just not
really sure how to do this. What I have is a spectrum of values that go
up
moving left to right across the sheet, and I want to know where the data
in
the next column fits in the spectrum. I could do this by hand, but there
are
close to 3,000 rows to go through.

Thanks for any help you can provide!

Say the 5 columns are A-E, and the new data is in Col F. In Col G, I would
create a formula based on nested IF functions to test whether F is greater
than A, B, C, D, or E - outputting the appropriate letter. This will give
you the data you want.

Another way would be to take the next 5 columns and use a different one to
test if F is greater than A-E - with say an output of X if it is. This would
give you a more 'visual' guide to where the data fits in the spectrum.

HTH

V