如何获得Excel中的评论加权平均数？ [英] How to get a weighted average for reviews in Excel?

问题描述

所以这是我的挑战。我有一个电子表格，如下所示：

  prod_id |包|值|耐用|壮举|缓解|等级|评分数
 1 75 85 99 90 90 88 1 
 2 90 95 81 86 87 88 9 
 3 87 86 80 85 82 84 37 
 4 92 80 68 67 45 70 5 
 5 93 81 94 93 90 90 4 
 6 93 70 60 60 70 70 1 
  

每个产品都有单独的成绩标准（包装 - 易用性），总体平均成绩以及产品收到的评分数。

整个数据集我把68％的产品放在80-89级的范围内。我需要重新计算成绩以考虑到产品的评级数量，所以远低于总平均评分数量的产品排名较低（并获得较低的成绩）。基本上，84级和100级等级的产品应该高于95级，只有5级的产品。

我希望这是有道理的，谢谢任何帮助提前！

解决方案

您需要的是一个有意义的加权算法。您可以选择任何对您有意义的内容，但根据您的要求，首先要尝试的是将原始等级乘以加权因子。计算得出，由于评分数除以总分数的比例可以得出答案：

  prod id raw等级＃级别加权等级
 1 88 1 0.01754386 1.543859649 
 2 88 9 0.157894737 13.89473684 
 3 84 37 0.649122807 54.52631579 
 4 70 5 0.087719298 6.140350877 
 5 90 4 0.070175439 6.315789474 
 6 70 1 0.01754386 1.228070175 
 57 
  

不知道这是否有意义为您的问题，但它确实符合您的要求。也许你可以规范加权等级，所以prod id＃3是100，并从那里缩放其余的。

看看集体情报为其他一些想法。

So here's my challenge. I have a spreadsheet that looks like this:

prod_id | pack  | value | durable | feat | ease | grade  | # of ratings
1         75      85      99        90     90     88       1
2         90      95      81        86     87     88       9
3         87      86      80        85     82     84       37
4         92      80      68        67     45     70       5
5         93      81      94        93     90     90       4
6         93      70      60        60     70     70       1

Each product has individual grade criteria (packaging - ease of use), an overall average grade, and number of ratings the product received.

The entire data set I have places 68% of the products within the 80-89 grade range. I need to have the grades recalculated to take into account the number of ratings for the product, so products that fall far below the total average number of ratings are ranked lower (and receive a lower grade). Basically a product with a grade of 84 and 100 ratings should rank higher than a product with a grade of 95 with only 5 ratings.

I hope this makes sense, thanks for any help in advance!

解决方案

What you need is a meaningful algorithm for weighting. You can choose anything that makes sense to you, but the first thing to try, based on your requirements, is to multiply the raw grade by a weighting factor. Calculate that as the ratio of the # of ratings divided by the total # of ratings gives this for an answer:

prod id raw grade   # ratings   weight          weighted grade
1         88          1           0.01754386      1.543859649 
2         88          9           0.157894737     13.89473684
3         84          37          0.649122807     54.52631579
4         70          5           0.087719298     6.140350877
5         90          4           0.070175439     6.315789474
6         70          1           0.01754386      1.228070175
                       57       

Not sure if this makes sense for your problem, but it does meet your requirements. Maybe you can normalize the weighted grades so prod id # 3 is 100 and scale the rest from that.

Have a look at "Collective Intelligence" for some other ideas.

这篇关于如何获得Excel中的评论加权平均数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！