Anyone good with spreadsheet formulas?

Associate
Joined
6 Jul 2009
Posts
271
Wonder if anyone could help me come up with a formula either in google sheets or open office to work out the mean from grouped data for example.

Score Number of People
0 - 4 0
5 - 9 0
10 - 14 0
15 - 19 2
20 - 24 4
25 - 29 2
30 - 34 10
35 - 39 10
40 - 44 23
45 - 49 25
50 - 54 33
55 - 59 32
60 - 64 41

So to work out the mean score if we take the last line it would be ((60+64)/2)*41 this would then need to be repeated for every row added together and divided by the number of people.

Any help appreciated
 
https://docs.google.com/spreadsheets/d/15kVhwJ-Z9CA3D2G9Yok0zjLLHecZnaTgOAgU58fsSB0/edit?usp=sharing

That's the original file.

I should probably clarify this for an OU course and the answers have already been provided in the question for the 2006 data.

Mean 79.36
Median 75
Standard deviation 26.56

"Find the formulae available in your chosen spreadsheet tool to calculate the mean,median and standard deviation of the fitness scores for both years and insert them in the cell"

My confusion comes from working out the mean from grouped data so I'm assuming I just take the midpoint of each of the ranges and if that is the case is there a formula for working out the midpoint or is it easier just to insert another column?
 
Never mind I'm an idiot I was making it much more complicated than necessary some times it really pays to take a brake an come back to somthing.
 
Back
Top Bottom