average/weighted average within Excel

Question

5 star    128
4 star    17
3 star    10
2 star    2
1 star    14

Average stars 4.42

How many more 5 stars would I need to get Average Stars to 4.45 or 4.47 etc.

So what I tried was using SUMPRODUCT in Excel but I am approaching this wrong. First of all I cannot achieve the current number 4.42 because of my incorrect approach.

I would like to be able to change the variables at my discretion and see the outcome

how would I continue?

Answer 1

Define $S=128 \cdot 5 + 17 \cdot 4 + 10 \cdot 3 + 2 \cdot 2 + 14 \cdot 1$ and $N=128+17+10+2+14$ Your current average is $A=\frac SN=\frac {128 \cdot 5 + 17 \cdot 4 + 10 \cdot 3 + 2 \cdot 2 + 14 \cdot 1}{128+17+10+2+14}$. If you want your average to be $A'$ from the addition of $n\ 5$'s, you need $A'=\frac {S+5n}{N+n}$. You can solve this for $n$ as all the other values are given. $A'(N+n)=S+5n, n=\frac {A'N-S}{5-A'}$