Solving for an Overall Rank based on Multiple Criteria... Please Help!

[phoebejk]

I have a dataset with about 500 rows, each row resembling a page. Each page had so many visitors, generated x amount of dollars, and had x number enter on it as their first page view. I'm trying to give an overall rank for each page based on the visits, revenue, and entries and compare across pages. I also want to look at Revenue being the most important metric or priority, followed by visits, followed by entries. So I need something weighted perhaps? The goal is to know what pages work best and what pages can we think about getting rid of. Below is a sample set of data. I've tried using rank, countifs, sumproducts, nested versions of each but nothing I am doing is working as intended. Any thoughts or insights?


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 143"]
<tbody>[TR]
[TD="width: 143"]Page Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Visits[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="width: 76"]Revenue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Entries[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 143"]
<tbody>[TR]
[TD="class: xl66, width: 143"]Best Low Rates[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]59300[/TD]
[TD]11843[/TD]
[TD]53187[/TD]
[/TR]
[TR]
[TD][TABLE="width: 143"]
<tbody>[TR]
[TD="class: xl66, width: 143"]Stay More, Save More[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]52488[/TD]
[TD]192372[/TD]
[TD]39137[/TD]
[/TR]
[TR]
[TD][TABLE="width: 143"]
<tbody>[TR]
[TD="class: xl66, width: 143"]Stay And Save[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]52251[/TD]
[TD]46596[/TD]
[TD]50587[/TD]
[/TR]
[TR]
[TD][TABLE="width: 143"]
<tbody>[TR]
[TD="class: xl66, width: 143"]Savings Calendar[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]46246[/TD]
[TD]983869[/TD]
[TD]768[/TD]
[/TR]
[TR]
[TD][TABLE="width: 143"]
<tbody>[TR]
[TD="class: xl66, width: 143"]Guest Suites[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]44519[/TD]
[TD]601843[/TD]
[TD]5433[/TD]
[/TR]
[TR]
[TD][TABLE="width: 143"]
<tbody>[TR]
[TD="class: xl66, width: 143"]Guest Rooms[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]41324[/TD]
[TD]565491[/TD]
[TD]4688[/TD]
[/TR]
[TR]
[TD][TABLE="width: 143"]
<tbody>[TR]
[TD="class: xl66, width: 143"]Compare Features[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]41160[/TD]
[TD]1015577[/TD]
[TD]4936[/TD]
[/TR]
[TR]
[TD][TABLE="width: 143"]
<tbody>[TR]
[TD="class: xl66, width: 143"]Wedding Venues[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]32913[/TD]
[TD]43842[/TD]
[TD]9908[/TD]
[/TR]
</tbody>[/TABLE]

 

[Wicker]

If you know how much you wanted your "overall rank" to be weighted, it would eradicate much of our guess work.
In the mean time, I cooked up something super low tech, super quick and super easy to do until you decide you want "tiebreaker goes to Revenue Rank"
Step 1: Add these columns after "Entries":"Vrank","Rrank","Erank","Avg", and "Arank".
Step 2: Turn Filters On. Make sure that each header has that little arrow in its bottom right.
Step 3: Sort "Visits" largest to smallest.
Step 4: In the first nonheader cell of Vrank (it was E2 for me) put a 1. Put a 2 in the cell directly below it (E3 for me).
Step 5: Highlight both the 1 and 2 (E2 and E3 for me). there will be a little black square in the lower right corner of the 2 cell (E3 for me). Double click it. This should autonumber the column for you giving you their Vrank(V for Visit, so I'm sure you can work out from there what the other ones mean).
Step 6: Repeat steps 3-5 for Revenue and Rrank, as well as for Entries and Erank.
Step 7: the first nonheader Avg cell (H2 for me) will add all of the ranks together and divide by 3. My equation looked like this: =SUM(E2:G2)/3
Step 8: highlight the cell you just made and notice that small black square again. Double click it and the formula will correctly copy itself all the way to the bottom, checking each row.
Step 9: Sort smallest to largest by Rrank THEN sort smallest to largest in Avg. This will mean any tiebreakers in Avg will go to the page that has the better revenue rank.
Step 10: In the first nonheader cell of Arank (it was I2 for me) put a 1. Put a 2 in the cell directly below it (I3 for me).
Step 11: Highlight both the 1 and 2 (I2 and I3 for me). there will be a little black square in the lower right corner of the 2 cell (I3 for me). Double click it. This should autonumber the column for you giving you their Arank/Overall-Rank.

There! Now you have your template! Just erase the old page data (columns A-D, leave the headers of course) and add in the new data whenever the need arises. After that just do steps 3-6 and 9-11 and POOF! You are done! I'd be surprised if it took you even three minutes each time you got a new data set!

 

[Wicker]

So that was what I cooked up WITHOUT doing any research. Here is what I have now:

Assuming Vrank is the E column, E2 should equal:
=RANK(B2,$B$2:$B$9)
where $B$2:$B:$9 is your total data range for the Visits column (assumed to be column B). Since you said you had 500 or so, you'll have to change that "9" to the last number, just make it $B$2:$B$5001 and you'll probably never have to change the equation again (until you get 5002 pages, of course). Highlight this new E2, double click that magical little square and voila.
Do the same to the other _Rank columns (change the letters so that the reference the correct columns. Don't worry about Arank being inversed, we're about to fix that.

For Avg, assuming it is the H column, H2 should equal:
=(SUM(E2:G2)/3*-1)
this essentially makes all the numbers negative. the RANK function puts the biggest number as number 1, so by multiplying everything by -1, the numbers closest to zero are actually the biggest.

That's it for now.
Now you just swap out your data and the table will automatically give you all the numbers you need (just make sure the formula columns reach down to at least your data columns). So yeah, now essentially whenever you paste in your new data, you are done, so from max 3 minutes to max 3 seconds (can anyone really push crtl+v that slow)? That's the power of PineSo-er, Excel formulas!

 

[phoebejk]

Thank You for your help!!

So that was what I cooked up WITHOUT doing any research. Here is what I have now:

Assuming Vrank is the E column, E2 should equal:
=RANK(B2,$B$2:$B$9)
where $B$2:$B:$9 is your total data range for the Visits column (assumed to be column B). Since you said you had 500 or so, you'll have to change that "9" to the last number, just make it $B$2:$B$5001 and you'll probably never have to change the equation again (until you get 5002 pages, of course). Highlight this new E2, double click that magical little square and voila.
Do the same to the other _Rank columns (change the letters so that the reference the correct columns. Don't worry about Arank being inversed, we're about to fix that.

For Avg, assuming it is the H column, H2 should equal:
=(SUM(E2:G2)/3*-1)
this essentially makes all the numbers negative. the RANK function puts the biggest number as number 1, so by multiplying everything by -1, the numbers closest to zero are actually the biggest.

That's it for now.
Now you just swap out your data and the table will automatically give you all the numbers you need (just make sure the formula columns reach down to at least your data columns). So yeah, now essentially whenever you paste in your new data, you are done, so from max 3 minutes to max 3 seconds (can anyone really push crtl+v that slow)? That's the power of PineSo-er, Excel formulas!