Rating System Help

Amnesiac

Board Regular
Joined
Apr 16, 2009
Messages
144
Hello. I am trying to come up with a rating system in Excel. I don't want this to end up a popularity contest, so I want to use experience as a criteria too. Users will rate at item A through F (could be anything from restaurants to roller coasters) with a value of 1 to 5, 1 being lowest and 5 being highest. Not every user will rank every item, only the ones they have ever experienced. To account for this I have a user experience index. I counted the total number of items experienced by each user, ranked them in reverse order, then divided by the total number of users (in this case there are 5). Thus, each users experience index is between 0 and 1, with 1 being the most experienced user.

I also wanted to incorporate a popularity index. I count how many users have used each item, rank them in reverse order, then divide by the total number of items. The popularity index is between 0 and 1, with 1 being the most popular item. Attached is a picture of an example of my setup:

<table style="border-collapse: collapse; width: 484pt;" border="0" cellpadding="0" cellspacing="0" width="643"><col style="width: 57pt;" width="76"> <col style="width: 66pt;" width="88"> <col style="width: 57pt;" width="76"> <col style="width: 34pt;" width="45"> <col style="width: 35pt;" width="46"> <col style="width: 48pt;" width="64"> <col style="width: 11pt;" width="14"> <col style="width: 84pt;" width="112"> <col style="width: 28pt;" width="37"> <col style="width: 55pt;" width="73"> <col style="width: 9pt;" width="12"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt; width: 57pt;" width="76" height="20"> </td> <td class="xl72" style="border-left: medium none; width: 66pt;" width="88">User 1 Rating</td> <td class="xl72" style="border-left: medium none; width: 57pt;" width="76">User 2</td> <td class="xl72" style="border-left: medium none; width: 34pt;" width="45">User 3</td> <td class="xl72" style="border-left: medium none; width: 35pt;" width="46">User 4</td> <td class="xl72" style="border-left: medium none; width: 48pt;" width="64">User 5</td> <td class="xl71" style="width: 11pt;" width="14"> </td> <td class="xl72" style="width: 84pt;" width="112">Popularity Count</td> <td class="xl73" style="border-left: medium none; width: 28pt;" width="37">Rank</td> <td class="xl72" style="border-left: medium none; width: 55pt;" width="73">Pop. Index</td> <td class="xl69" style="width: 9pt;" width="12"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="border-top: medium none; height: 15pt;" height="20">A</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl66" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71"> </td> <td class="xl66" style="border-top: medium none;">3</td> <td class="xl66" style="border-left: medium none;">2</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0.33</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="border-top: medium none; height: 15pt;" height="20">B</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl71"> </td> <td class="xl66" style="border-top: medium none;">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">6</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">1.00</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="border-top: medium none; height: 15pt;" height="20">C</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl66" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl71"> </td> <td class="xl66" style="border-top: medium none;">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0.33</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="border-top: medium none; height: 15pt;" height="20">D</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl66" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl71"> </td> <td class="xl66" style="border-top: medium none;">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0.17</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="border-top: medium none; height: 15pt;" height="20">E</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl66" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71"> </td> <td class="xl66" style="border-top: medium none;">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0.33</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="border-top: medium none; height: 15pt;" height="20">F</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl71"> </td> <td class="xl66" style="border-top: medium none;">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl74" style="border-top: medium none; border-left: medium none;">0.33</td> <td class="xl69"> </td> </tr> <tr style="height: 6pt;" height="8"> <td class="xl69" style="height: 6pt;" height="8"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">User Count</td> <td class="xl66" style="border-left: medium none;">5</td> <td class="xl66" style="border-left: medium none;">1</td> <td class="xl66" style="border-left: medium none;">4</td> <td class="xl66" style="border-left: medium none;">6</td> <td class="xl66" style="border-left: medium none;">3</td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" height="20">Rank</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="border-top: medium none; height: 15pt;" height="20">User Index</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">0.8</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">0.2</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">0.6</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">0.4</td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl69"> </td> <td class="xl71"> </td> <td class="xl71"> </td> <td class="xl69"> </td> </tr> </tbody></table>

So now that I have this data I am not sure what to do with it. How can I combine them in a meaningful way? I've tried this formula:

(User rating * user experience index)/SUM(user experience <wbr>index) * popularity index

The problem is probably with the popularity index. With it being linear, it gives far too much advantage to the most popular items, and goes too far to penalize those that don't see as much action. It over-compensates for the problem I described. I don't remember anything from the one statistics class I took in college so I'm not sure what to do. It feels like the linear experience index actually works really well, it just seems like the popularity index is off.


Any suggestions?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top