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 |