Anyone good with spreadsheet formulas?

Bobie8

Bobie8

Bobie8

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
 
So you want the next column to be your code, then a total of it at the bottom, then another column with the value being the value to the left divided by the grand total?

Or have I read that wrong?
 
do you not have the raw data? would be much easier to work with as you're not really producing a mean there
 
You can't work out the mean for the I am presuming ages from what you have there. If you want to work out the mean range then that is a different thing but really it will be a meaningless and in all likelihood useless figure. You need to get the data from which you placed the people into the age ranges and use that. Same would apply if it a pure test score and not a age. Let me put this this way to explain it better:

All the kids sit a GCSE - can you work out the mean percentage result by adding up people in all the grade ranges? You can't you don't know where in that range they actually fell from the range. You can't have a mean of halfway between a C and D that would be just odd. You can however pool all their results and say the mean grade was 50% and then equated to a C.

But to answer your question specifically I would;

Column A Score eg 0-4
Column B (Hidden) Score Low End eg 0
Column C (Hidden) Score High End eg 4
Column D Number eg 0
Column E Mean that won't be the mean eg Column (B+C*0.5)*D
 
Last edited:
If all the groups are banded in groups of 4, the crude and simple way would be to just create a new column, start with 2, then 4, then drag it down to the end to give you the midpoints - multiply that by the number of people then have a couple sum entries at the bottom to play with to get the figure you are after :p
 
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?
 
Oh, in that case I'd use the ungrouped data from columns B and M, and the formulas are...

Mean is =AVERAGE(B4:B503)
Median is =MEDIAN(B4:B503)
Standard deviation is =STDEV(B4:B503)

You don't need to manipulate the data at all, or use the grouped ones.
 
if you're having issues then you should contact your tutor... sounds like you partly need help understanding what the question is asking you to do - your OP is very confused - you've got the raw data there but for some reason you're seemingly trying to use the totals in each group instead...

if this is for a TMA then you really shouldn't be posting any of it on here in the first place - either way your tutor is the person to speak to not random people on an internet forum
 
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.
 
You must log in or register to reply here.
Back
Top Bottom