# average/weighted average within Excel

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?

-
Let $n$ number of $5$ starts are needed So, the average will be $$=\frac{128+17+10+2+14+128\cdot n}{n+5}$$ – lab bhattacharjee Jul 8 '13 at 15:28
@labbhattacharjee thanks, I need this to return a number like 4.42 for a visual aid, this doesn't give me an indication of what the current average star is when I change variables, this is very close though – CQM Jul 8 '13 at 15:34
@labbhattacharjee: No, the average is not that. – Ross Millikan Jul 8 '13 at 15:56
@RossMillikan, it depends on the context. – lab bhattacharjee Jul 8 '13 at 16:09
@labbhattacharjee: the $128$ votes need to be weighted by $5$ etc. – Ross Millikan Jul 8 '13 at 16:30

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'}$

-
yes, this worked for me, thank you – CQM Jul 8 '13 at 16:44