# Rank item based on weighted categories

#### RocketAnt

##### New Member
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

 Store Sales UPT Conv% Hours Used CC-0 800k 1.5 40% 800 MO-9 950k 2.3 60% 500 RX-3 725k 2 25% 300 TP-9 210k 1.2 50% 200 XD-2 125k 1 32% 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
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!

#### RocketAnt

##### New Member
Thank you so much!! This works out perfectly!

Happy to help!

Replies
1
Views
305
Replies
1
Views
506
Replies
1
Views
842
Replies
6
Views
637
Replies
1
Views
398

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.

### Which adblocker are you using?

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

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