Compare everything against everything

Using Excel 2016, how do I compare every row to every other row, and see which are most/least similar?

Example: 4 rows with 6 numbers each in them.

 

2 4 98 7 3 2

6 4 .5 1 .4 1

3 1 16 2 4 9

3 700 22 9 .1 5

 

Each row, I have a formula that gives me a specific number. For ease, lets say “average”.

Averages in this example would be 19.333, 2.15, 5.833, 123.183

I want to compare each average against each other- 2.15/19.333 = 11% ; 5.833/19.333 = 30%  ; 19.333/123.183 = 15.7% ; …etc

Is there a formula I can plug in that does this. The spreadsheet I am creating is 3000+ rows… if it helps, I am looking to divide only smaller numbers into larger numbers, always creating a number less than or equal to 100%.

By: Jon Giles

Leave a Reply

Your email address will not be published. Required fields are marked *