• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can I get the mean and SD with midpoint and relative frequency?

S

snow

New Member
For example:

Age Group Percentage

0-24 34.0%

25-34 13.1%

35-44 13.9%

45-54 14.5%

55-64 11.2%

65-74 7.1%

75+ 6.3%


Assume the midpoint of 75+ group is 81


Thanks!
 
The mean and SD of what?


Also, I looked at your posts and I have to ask if you are taking a statistics class? :)
 
Assuming you have the midpoint actually calcualted/displayed somewhere (I'll assume in col C), the mean would be:

=SUMPRODUCT(C2:C8,B2:B8)


To get the SD, need to do a little prep work. In D2:

=(C2-C10)^2*B2

Copy this down to C8. Then, in C10, your SD is:

=SQRT(SUM(D2:D8)/99)
 
Hi Luke M,


I do not understand that why using formula (C2-C10)^2*B2 and SQRT(SUM(D2:D8)/99) respevtively?


Thanks!
 
Hi, snow!

The detailed explanation goes beyond Excel scope, it's just a matter or probability theory. Check this in wikipedia, it's a little hard but it's complete:

http://en.wikipedia.org/wiki/Standard_Deviation
 
Snow,

I looked up the definition for STDEV within the XL help file, which is:

=SQRT((SUM((x-x_bar)^2))/(n-1))


The first formula:

(C2-C10)^2*B2

Take care of figuring out all the parts for the summation in the numerator. Then I plug it into:

SQRT(SUM(D2:D8)/99)

To solve. Note that I manually did the n-1 (aka, 100 - 1) to get 99.
 
You must log in or register to reply here.
Back
Top