Search results

  1. Jennifer Murphy

    How to assign a Z Score for missing values

    There is one more option that I forgot to mention. I could exclude any missing values from the weighted rating calculation. This has some advantages, but my concern is that a product with just 1 or 2 high ratings could put it higher in the rankings than it deserves -- higher than other products...
  2. Jennifer Murphy

    How to assign a Z Score for missing values

    Here is that last table where I delete all of the values in all of the examples in the original post. Now there is quite a bit of movement in the overall rankings. My question is, what is the best way to handle missing values? Is it assigning them the average value, as I have done here or, on...
  3. Jennifer Murphy

    How to assign a Z Score for missing values

    This forum has been very helpful to me and my "peripheral" understanding of statistics. For that, I thank you all. As a result of suggestions I received here, I have made good progress in my weighted rating project. The suggestion to use z scores was a game changer. I now have a way to...
  4. Jennifer Murphy

    How to compare similar ratings?

    I thought that a 95% (0.05) confidence interval was a +/- range about the mean within which we have 95% confidence. So if the mean is 50 and the 95% confidence interval is 5 (+/-5), then we have 95% confidence that the true value is somewhere between 45 and 55. I did the subtraction to get the...
  5. Jennifer Murphy

    How to compare similar ratings?

    OK, let's see if I understand anything. Here's that same data with, I think, the calculations you suggested. I did it step by step so I could see the how it works. Columns F & G are the first 2 steps. The totals are in Row 25. Column H is confidence interval. Column I is the adjusted rating...
  6. Jennifer Murphy

    How to compare similar ratings?

    I have been trying to come up with a way to adjust a list of ratings, as in the example below, based on the number of reviews, but have not been able to come up anything. Can you help me with a formula for Column E that makes some sort of adjustment to the Amazon ratings in C based on the number...
  7. Jennifer Murphy

    How to compare similar ratings?

    My end goal is a tool that I can use to help me select the best product of the ones that are available. Whenever I set out to buy something that has a lot of “factors” that I need to consider, such as price, weight, resolution, response time, calories, capacity, noise level, power, efficiency...
  8. Jennifer Murphy

    How to compare similar ratings?

    OK. After some reading and some fiddling, I think I have settled on z scores as the best way to compare sample ratings on diverse scales. The table below illustrates some ways that this could be implemented for Amazon product ratings. These are the actual ratings for 19 actual products. These...
  9. Jennifer Murphy

    How to compare similar ratings?

    (sigh) I believe I have been thinking backwards, or maybe sideways. I don't want the z scores within the ratings for a single product. I want the z scores for the ratings among (or between) all of the products bweing considered. I believe this table handles it correctly. Let me know if this is...
  10. Jennifer Murphy

    How to compare similar ratings?

    I think I have the mean, but the std deviation is eluding me. Can you help with the formulas for std deviation? Columns C-H contain the Amazon ratings. I-N are based on those numbers. Columns Q-U are my calcvulations of the actual numbers of ratings in each category. V-X are based on those...
  11. Jennifer Murphy

    How to compare similar ratings?

    Most of the products on Amazon have a 5-star rating (1.0 - 5.0). Few products have ratings below 4.0 and almost none below 3.0. The majority are clustered around 4.2 to 4.7 or so. The ratings include the number of reviews from which those ratings were calculated. Those numbers can vary from just...
  12. Jennifer Murphy

    Normalized Score v Z Score

    Now that I have the z-scores working, I need to generalize to other kinds of ratings. This table contains some data about portable treadmills for home use. I would appreciate suggestions for methods for generating rating scales that I can merge with the z-scores to come up with an overall...
  13. Jennifer Murphy

    Normalized Score v Z Score

    The Normalize comparison is similar. As you say, this one transforms the ratings to the narrower range of the actual ratings, rather than maximum range. This is also what the Z Score does, but it also centers them about the actual mean, so that I see positive and negative ratings, and I think it...
  14. Jennifer Murphy

    Normalized Score v Z Score

    This is an excellent point. Thank you. Another important vote for Z Scores. In my last example, I did add a weighting factor. My intent was to give me a way to assign weights to the ratings. My goal in this project is to include product properties in addition to ratings. For example, I was...
  15. Jennifer Murphy

    Normalized Score v Z Score

    Yes, I discovered an error or two in the formulas. I think the most recent tables are correct.
  16. Jennifer Murphy

    Normalized Score v Z Score

    That's an interesting idea. I put it to work with my 2 methods in this sheet. Columns D-F have the 3 sets of ratings. Notice that the second set is in reverse order. Columns G has the sum of the Z Scores in columns L-N. Column H those ranks. Column I has the sum of the scaled ratings in columns...
  17. Jennifer Murphy

    Normalized Score v Z Score

    I'll do that, but I am not trying to determine the reliability of the raters. I am trying to determine an "average" or "composite" rating of the product.
  18. Jennifer Murphy

    Normalized Score v Z Score

    I don't think there is a "true" ranking or rating. It depends on who you ask. In this example, I asked three sources. The data is artificial, but not totally random. I have looked at a lot of ratings from different sources. It is very common to have a product rated very highly on one source and...
  19. Jennifer Murphy

    Normalized Score v Z Score

    I would like to come up with an algorithm that will allow me to compare different products based on ratings from different sources using different scales. This Excel table compares 5 products (A-E) with 3 ratings (Rtg1 - Rtg 3). The actual ratings are in columns H, K, & N. The maximum and...
  20. Jennifer Murphy

    Ranked Choice Voting

    I hear on the news that several states are using "ranked choice voting". As I understand it, instead of voting for one single candidate, voters assign ranks to multiple candidates. They give their top choice a "1", their second choice a "2", and so on. If a candidate gets >50% of the "1" votes...