Rank item based on weighted categories

[RocketAnt]

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

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Store	Sales	UPT	Conv%	Hours Used		Score	Rank		Category	Weight
2	CC-0	800	1.5	40%	800		564.075	3		Sales	70%
3	MO-9	950	2.3	60%	500		716.115	1		UPT	5%
4	RX-3	725	2	25%	300		585.1	2		Conv%	10%
5	TP-9	210	1.2	50%	200		242.06	4		Hours Used	15%
6	XD-2	125	1	32%	400		150.75	5
Sheet6

Cell Formulas
Range	Formula
G2:G6	G2	=B2*$K$2+C2*$K$3+D2*100*$K$4+(MAX(E:E)-E2)*$K$5
H2:H6	H2	=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]

Thank you so much!! This works out perfectly!

 

[Eric W]

Happy to help!