MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I need to calculate the Median and Standard Deviation for a table of values with counts in Excel, so...
Number of People / Age
10 / 5
20 / 6
15 / 7
10 / 8
5 / 9
...what's the median age, and what's the standard deviation.
I can get the mean myself but I can't work out how to get the others. I don't want to have to type '5' into 10 cells 🙂
Any ideas please?
Did you look at the function help in the help file? I seem to remember there are stats functions in there.
Yep - I can do say STDEV(B2:B9) to get the deviation of a single column, but if row A has counts I can't find a formula to consider them.
I've just manually done std dev by summing the square of the variation from the mean in another couple of columns. I'm sure Excel can already do this and I've just not found how.
Jeez mate is that your list? Just type 5, 6 , 7 whatever in the top cell, copy and paste the values into the required number of cells will take you all of 30 secs, less time than it's taken me to type this.
for the mean create a column c which is col a * col b. Then sum col c and divide that by the sum of col a.
median is trickier - thinking cap is on (but my reading glasses are not - just seen you did the mean)
Erm. Can you not just do =MEDIAN(range1:range2)
Jeez mate is that your list?
Course not! That was just a sample.
I've just realised though (amazing what a night's sleep can do) that the original data from which the totals were produced is available so I can do the stats on that.
My second question was going to be, if that's not a sample, where is the source of that data!?