0

I have product review data. The reviews are out of 5 stars and I know how many there are (ie quantity of reviews). I'm trying to calculate a weighted average of the reviews because I'm trying to distinguish the reviews by different product categories. If I simply do a pivot table average, it doesn't take into account the number of reviews. This is wrong:

enter image description here

So far, I have read and tried a lot with sumproduct and average.weighted but these seem to be better at calculating weighted averaged when you have the actual original values themselves (vs. the averages + quantity).

How does one calculate weighted average of averages:

enter image description here

Demo sheet: https://docs.google.com/spreadsheets/d/1WIdoCjazfprxRN5XW5PJ8lBQNAf8qmjgmRaZd3lhyJE/edit?usp=sharing

1

Kyle, it looks like you want this:

cat 1: AVERAGE.WEIGHTED(B2:B6, C2:C6)

cat 2: AVERAGE.WEIGHTED(B7:B11, C7:C11)

cat 3: AVERAGE.WEIGHTED(B12:B17, C12:C17)

cat 4: AVERAGE.WEIGHTED(B18:B22, C18:C22)

If you don't always know how many listings there will be for each category:

Set up a small table of two columns with headers, say "CAT | Weighted AVG"

List your categories by exact name from row 2 downward in the first column (in my suggested examples, the "CAT" column):

In row 2 of the "Weighted AVG" column beside that, place this formula:

=ArrayFormula(AVERAGE.WEIGHTED(IF(A2:A=E2,B2:B),IF(A2:A=E2,C2:C)))

Then copy it down so that a version resides next to each of the categories you've listed under "CAT."

  • 👋thanks Erik! This gives me a great start in the right direction... – Kyle Pennell Oct 17 '18 at 1:44
  • 1
    If you want to share more along the way as things take more shape, I'll keep an eye out. – Erik Tyler Oct 17 '18 at 2:21

protected by Community Oct 16 '18 at 21:53

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.