Rank item based on weighted categories

RocketAnt

New Member
Joined
Apr 1, 2014
Messages
15
Hi, I'm looking for a way to rank a list of stores based on different categories, which are weighted differently. In the example below, we have 5 different stores that I want to rank. Sales category has a weight of 70%, UPT has a weight of 5%, Conv% has a weight of 10%, and Hours used has a weight of 15%. For Sales, UPT, and Conv%, the higher the number or %, the better. For Hours Used, the lower the number the better it is. I was wondering what formulas would help me with this. I've tried sumproduct and rank, but could not figure out how. Any suggestions would help, thanks

StoreSalesUPTConv%Hours Used
CC-0800k1.540%800
MO-9950k2.360%500
RX-3725k225%300
TP-9210k1.250%200
XD-2125k132%400
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,821
Maybe:

Book1
ABCDEFGHIJK
1StoreSalesUPTConv%Hours UsedScoreRankCategoryWeight
2CC-08001.540%800564.0753Sales70%
3MO-99502.360%500716.1151UPT5%
4RX-3725225%300585.12Conv%10%
5TP-92101.250%200242.064Hours Used15%
6XD-2125132%400150.755
Sheet6
Cell Formulas
RangeFormula
G2:G6G2=B2*$K$2+C2*$K$3+D2*100*$K$4+(MAX(E:E)-E2)*$K$5
H2:H6H2=RANK(G2,$G$2:$G$6)


I took the "k" off the values in B. I did not write the values as 800000, etc. since that would vastly increase the relative weighting. Conv% I multiplied by 100, or that would have ended up being almost inconsequential. For the hours used, I found the maximum hours in column E, then subtracted the value on each row from that, effectively reversing the order. Then the RANK function is quite basic. You can combine both formulas (G:H) into one, but it makes more sense this way.

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,795
Messages
5,638,373
Members
417,024
Latest member
Mrpica01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top