Ranking multiple columns

[mrcsbenson]

I am trying to come up with a ranking system that would take into account two separate values (from 2 different columns) and come up with a combined score (or rank).

In an ideal world, the formula would look at column(s) 'B' & 'C' and return a score that would rank these accounts from 1- whatever

Even better would be a way to rank column B holding 75% and column C weighting at 25% to make a total of 100% total ranking score.

 

[MountainFog]

1 Can you post that in this thread, explaining what you would expect?

 

[mrcsbenson]

I just made a change with the price from the picture above, before I made this change Airline 1 was #2 and Airline 2 was #1 because it had the lowest cost and the highest score but now when I changed the price for Airline 1 and Airline 2 the ranking didn’t change which is odd if the formula is taking into account the price (75%) and score (25%) and the price is way more than Airline 3 and 4 even with the score still being high.

Hopefully that makes sense in what I am saying

 

[mrcsbenson]

1 Can you post that in this thread, explaining what you would expect?

I just made a change with the price from the picture above, before I made this change Airline 1 was #2 and Airline 2 was #1 because it had the lowest cost and the highest score but now when I changed the price for Airline 1 and Airline 2 the ranking didn’t change which is odd if the formula is taking into account the price (75%) and score (25%) and the price is way more than Airline 3 and 4 even with the score still being high.

Hopefully that makes sense in what I am saying

 

[Cubist]

See if this works for you.

Book1
	A	B	C	D	E
1		Price	KPI	Combined Score	Rank
2	Air1	2	8.5	0.451	3
3	Air2	3	12.5	0.250	4
4	Air3	0.66	5.5	0.782	1
5	Air4	0.55	3	0.750	2
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=LET( norm_price,1-(B2:B5-MIN($B$2:$B$5))/(MAX($B$2:$B$5)-MIN($B$2:$B$5)), norm_KPI,(C2:C5-MIN($C$2:$C$5))/(MAX($C$2:$C$5)-MIN($C$2:$C$5)), 0.75*norm_price+0.25*norm_KPI)
E2:E5	E2	=RANK.EQ(D2,$D$2#)
Dynamic array formulas.