Normally, this blog is about me trying to help you with Excel spreadsheets. Well today, maybe you can help me. I am trying to come up with a new rating system in Excel. I don't want this to end up being a popularity contest, so I want to use experience as a criteria too. Users will rate an 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.
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 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?
No comments:
Post a Comment
I'd love to hear from you!
-Nick