Updating Ranking Column

[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 columns B (Price) & C (KPI Score) and give a ranking based on the lowest price and best score. Ranking should be 1- whatever.

Even better would be a way to rank column B price 75% and column C score at 25% to make a total of 100% total ranking score. I would also like for the rank to change if I change column B or column C. Picture screenshot below:

I do have a formula already in the file:

Column B: =SUMPRODUCT($B$2:$C$2,B4:C4)
Column C: =RANK.AVG(D4,$D$4:$D$7,0)

if I changed the pricing and/or score the ranking goes out of whack and I am not sure why:

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.

Can someone help with this?

 

[Fluff]

Duplicate to: Ranking multiple columns

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.