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?
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?
