I think that this is a good way to go, with weights.Originally Posted by Paul_Hossler
As an CONCEPTUAL EXAMPLE ....… you could look at something like this
10 cities (GREEN), 3 metrics (YELLOW)with each metric having a weighting factor (ORANGE)
The BLUE are just random numbers 0-100 but could be your raw scoring of City-X against City-Y for Metric-M (Col A - Col K)
Each raw score is weighted (Col M - Col N) in Col P - Col W
Weighted scores are summed Col Y - Col Z
The weighted summary is sorted High to Low in Col AB - Col AC
I used 2 UDFs and array functions
F9 to recalc and see what happens
Capture.jpg
How can I go from my tabular data to a matrix as in your screenshot?
Edit:
I have a small formula that calculates the difference in percentage units between two city pairs.
Say City1 vs City2. City1 could be 30%, and 50% better. Does this help in anyway?
