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

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