Excel formula help?

[Spudders]

Right you clever people, I have an Excel formula problem, so thought one of you genius-like people might be able to help me out here.

 

On the image below you can see that some ratings have been given in columns B to F (these can basically equate to 5 star down to 1 star. So for row 5, out of the 12 people that voted, 11 people voted 5 star, 1 person voted 2 star.

In columns M to Q I’ve converted to the percentage of people who voted, so for row 5, 5 star got 92% of the vote.

However I want to come up with a formula that tells me the average star rating. So for row 5, 11 out of 12 votes went for the 5 star option, so common sense tells me this will be something like 4.9 on average.

Then in Row 6, as 6 people went for 5 star rating, 1 person went for 4 star, 4 people for 3 star and 1 person for 2 star, I know roughly this will be around 4 star on average.

What formula do I need to do, to work this out/show this in Excel?

 

Cheers.

 

Edited 1 November, 2010 by Spudders

[OttawaSaint]

I am sure there is a more elegant way, but would this work?

 

=SUM(SUM(B5)*5+(SUM(C5)*4)+(SUM(D5)*3)+(SUM(E5)*2)+(SUM(F5)*1))/12

 

=4.75

[angelman]

think that you should for a start replace the 12 dividing the sum at the end by G5

[robbsaints]

Hi,

 

Similiar to above but I would put Put 5 in B1, 4 in C1, 3 in D1, 2 in E1 and 1 in F1 and then use the following formula for Row 5

 

=+(B5*$B$1+C5*$C$1+D5*$D$1+E5*$E$1+F5*$F$1)/G5 which equals 4.75

 

The formula can then by copied down for each row and will automaticly update every time you add a new vote.

 

Rob

[georgeweahscousin]

=average((sum(b6)*5)+(sum(c6)*4)+(sum(d6)*3)+(sum(e6)*2)+(sum(f6)*1))/12

 

= 4

[Spudders]

I am sure there is a more elegant way, but would this work?

 

=SUM(SUM(B5)*5+(SUM(C5)*4)+(SUM(D5)*3)+(SUM(E5)*2)+(SUM(F5)*1))/12

 

=4.75

 

Ottawa, that is fantastic. I've changed the last figure in the formula from 12 to the cell G5 (as the number of people voting changes on different lines) and this has worked a treat.

 

For all the negative comments this forum has had recently, it's amazing the range of knowledge people have on here, meaning that so many different 'random' questions get answered.

 

Thanks so much!!!!!!!

[Spudders]

Hi,

 

Similiar to above but I would put Put 5 in B1, 4 in C1, 3 in D1, 2 in E1 and 1 in F1 and then use the following formula for Row 5

 

=+(B5*$B$1+C5*$C$1+D5*$D$1+E5*$E$1+F5*$F$1)/G5 which equals 4.75

 

The formula can then by copied down for each row and will automaticly update every time you add a new vote.

 

Rob

 

 

Nice, earlier I had no formula, now I have two ways of doing it! Cheers Rob as well.

[OttawaSaint]

think that you should for a start replace the 12 dividing the sum at the end by G5

 

Doh, well spotted, I only looked at the first 2 examples, didn't realise there were different numbers of total voters on other lines.

[Deano6]

I am sure there is a more elegant way, but would this work?

 

=SUM(SUM(B5)*5+(SUM(C5)*4)+(SUM(D5)*3)+(SUM(E5)*2)+(SUM(F5)*1))/12

 

=4.75

 

Absolutely no need for the Sum( ) around the individual cells.

 

Hi,

 

Similiar to above but I would put Put 5 in B1, 4 in C1, 3 in D1, 2 in E1 and 1 in F1 and then use the following formula for Row 5

 

=+(B5*$B$1+C5*$C$1+D5*$D$1+E5*$E$1+F5*$F$1)/G5 which equals 4.75

 

The formula can then by copied down for each row and will automaticly update every time you add a new vote.

 

Rob

 

This.

[saint si]

And just to put the finishing touches on, I would wrap the whole thing in an if statement so that you don't get division by zero errors. i.e.:

 

=if(G$5=0,"No votes", ###insert the above formula here### )

[saint si]

Sorry, should be $G5 ...

[OttawaSaint]

Absolutely no need for the Sum( ) around the individual cells.

 

Thanks! I will bear that in mind for the next time I have to make formulas on excel.

[Robsk II]

This.

 

Credit.

[bolo]

I know i am late (living half way across the world does that to you) but what version excel are you using? I would do a modified version of robbsaints version, which is cleaner. In B2 to F2 I would just type in the number (5,4,3,2,1). I would then format the cells (put in a custom format of 0"*") and then use this row rather than use row 1. The output should be the same are above, but instead excel will see the cell as a number, rather than text. Saves the need for writing 1,2,3,4,5 twice!. Also consider using the sumproduct function for a cleaner formula.... ie = sumproduct($B$2:$F$2,$B5:$F5)/$G5

 

If you are using excel 2007 use the iferror function catch errors.