Forum Discussion
Determining New Rank/Order to Maximize Sales
I have a data set from a website that lists items in Category Order. Using the Product Views, Click-thru Rate, Sales, and Sales/Views% I need to re-order the Category Order that will maximize Product Views. I have no idea if there is a function or formula I can use to do this. Any help would be greatly appreciated!
| SKU | Category Order | Product Views | Click-thru Rate | # Sales | Sales/Views% | New Category Order |
| 01-01-055 | 1 | 189 | 13.94% | 33 | 17.5% | |
| 01-01-500 | 2 | 204 | 15.04% | 31 | 15.2% | |
| 02-11-003 | 3 | 98 | 7.23% | 11 | 11.2% | |
| 01-01-002 | 4 | 96 | 7.08% | 10 | 10.4% | |
| 01-01-070 | 5 | 64 | 4.72% | 3 | 4.7% | |
| 01-01-001 | 6 | 77 | 5.68% | 6 | 7.8% | |
| 02-01-002 | 7 | 34 | 2.51% | 5 | 14.7% | |
| 01-01-054 | 8 | 35 | 2.58% | 0 | 0.0% | |
| 01-01-130 | 9 | 34 | 2.51% | 0 | 0.0% | |
| 01-01-039 | 10 | 47 | 3.47% | 6 | 12.8% | |
| 01-01-112 | 11 | 33 | 2.43% | 9 | 27.3% | |
| 02-01-024 | 12 | 22 | 1.62% | 2 | 9.1% | |
| 01-01-015 | 13 | 30 | 2.21% | 1 | 3.3% | |
| 02-01-004 | 14 | 26 | 1.92% | 1 | 3.8% | |
| 02-01-005 | 15 | 23 | 1.70% | 3 | 13.0% | |
| 01-01-009 | 16 | 22 | 1.62% | 0 | 0.0% | |
| 01-01-022 | 17 | 23 | 1.70% | 3 | 13.0% | |
| 01-01-096 | 18 | 19 | 1.40% | 0 | 0.0% | |
| 01-01-016 | 19 | 15 | 1.11% | 5 | 33.3% | |
| 01-01-095 | 20 | 15 | 1.11% | 0 | 0.0% | |
| 01-01-083 | 21 | 14 | 1.03% | 1 | 7.1% | |
| 01-01-116 | 22 | 13 | 0.96% | 1 | 7.7% | |
| 02-01-018 | 23 | 11 | 0.81% | 2 | 18.2% | |
| 02-11-004 | 24 | 12 | 0.88% | 0 | 0.0% | |
| 01-01-308 | 25 | 11 | 0.81% | 1 | 9.1% | |
| 01-01-089 | 26 | 17 | 1.25% | 0 | 0.0% | |
| 01-01-003 | 27 | 10 | 0.74% | 0 | 0.0% | |
| 01-01-092 | 28 | 9 | 0.66% | 0 | 0.0% | |
| 02-01-093 | 29 | 9 | 0.66% | 1 | 11.1% | |
| 02-01-021 | 30 | 15 | 1.11% | 0 | 0.0% | |
| 01-01-091 | 31 | 8 | 0.59% | 1 | 12.5% | |
| 01-01-090 | 32 | 7 | 0.52% | 1 | 14.3% | |
| 02-01-003 | 33 | 17 | 1.25% | 2 | 11.8% | |
| 01-01-044 | 34 | 7 | 0.52% | 0 | 0.0% | |
| 02-01-508 | 35 | 7 | 0.52% | 0 | 0.0% | |
| 01-01-304 | 36 | 6 | 0.44% | 0 | 0.0% | |
| 02-01-502 | 37 | 14 | 1.03% | 0 | 0.0% | |
| 01-01-025 | 38 | 11 | 0.81% | 2 | 18.2% | |
| 01-01-303 | 39 | 5 | 0.37% | 0 | 0.0% | |
| 01-01-018 | 40 | 5 | 0.37% | 1 | 20.0% | |
| 02-11-017 | 41 | 5 | 0.37% | 1 | 20.0% | |
| 01-01-019 | 42 | 4 | 0.29% | 0 | 0.0% | |
| 01-01-106 | 43 | 4 | 0.29% | 3 | 75.0% | |
| 02-01-071 | 44 | 3 | 0.22% | 0 | 0.0% | |
| 01-01-502 | 45 | 3 | 0.22% | 0 | 0.0% | |
| 02-01-017 | 46 | 7 | 0.52% | 2 | 28.6% | |
| 01-01-073 | 47 | 8 | 0.59% | 1 | 12.5% | |
| 02-11-002 | 48 | 4 | 0.29% | 0 | 0.0% | |
| 01-01-100 | 49 | 1 | 0.07% | 0 | 0.0% | |
| 02-01-027 | 50 | 1 | 0.07% | 0 | 0.0% | |
| 01-01-101 | 51 | 0 | 0.00% | 0 | 0.0% | |
| 02-01-072 | 52 | 0 | 0.00% | 0 | 0.0% | |
| 01-01-099 | 53 | 2 | 0.15% | 1 | 50.0% | |
| 02-11-005 | 54 | 0 | 0.00% | 0 | 0.0% | |
| 01-01-098 | 55 | 0 | 0.00% | 0 | 0.0% |
1 Reply
bfishy81 You can do this using a RANK.EQ function. However, in order to deal with duplicate ranks, you need to combine it with a COUNTIF function containing an expanding reference. I made a custom video showing how to build and implement this formula in your case: https://www.youtube.com/watch?v=D3_p4ZLNuSs
=RANK.EQ(C2,$C$2:$C$56)+COUNTIF($C$2:C2,C2)-1Optional tip if this was helpful: [link removed by admin]
