Results 1 to 3 of 3
Like Tree1Thanks
  • 1 Post By romsek

Thread: Standard Deviation from a frequency table in Excel

  1. #1
    Super Member
    Joined
    Oct 2009
    From
    Brisbane
    Posts
    954
    Thanks
    217

    Standard Deviation from a frequency table in Excel

    Is there a way, using the functions in Excel, to find the standard deviation for data in a frequency table? I know the STDEV.P and STDEV.S functions find the pop and sample sds for individual data. How do you tell Excel the second column is frequency? (I know you can calculate it using the formula for sd but just want it quickly.) Thanks in advance for any help.
    Follow Math Help Forum on Facebook and Google+

  2. #2
    MHF Contributor
    Joined
    Nov 2013
    From
    California
    Posts
    6,267
    Thanks
    2674

    Re: Standard Deviation from a frequency table in Excel

    if you've got a list of possible values in column A, and the associated frequencies in column B

    what I did that worked is to create the following

    at the bottom of column have the sum of column B, i.e. sum(B1:B100) for example, this is the total number of data points.

    column c equals column A times column B, i.e. c1 = a1 * b1, etc.

    at the bottom of column C have the total of column C divided by the value at the bottom of column B

    This value at the bottom of column C is the mean of the data.

    Now in column D compute the square of column A minus the data mean at the bottom of column C

    In column E multiply column D by column B

    at the bottom of column E take the sum of column E and divide it by the total number of data points - 1 (or not depending on which sample variance estimate you want)

    This value at the bottom of column E is the sample variance. Take the square root of it for the sample standard deviation.
    Thanks from Debsta
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Super Member
    Joined
    Oct 2009
    From
    Brisbane
    Posts
    954
    Thanks
    217

    Re: Standard Deviation from a frequency table in Excel

    Thanks for your time Romsek. So effectively you are using the formula for sd. I was actually wanting to avoid all that and just get excel to calculate sd as it does for indiv data using STDEV.P or STDEV.S. I don't think it can be done. I don't think there is a built in function where you input data values (eg A1:A100) and frequencies (eg B1:B100), like you can on a graphics calculator. Would be handy! You could record a macro, but I wanted to be just a button pusher!
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. frequency data and standard deviation
    Posted in the Calculators Forum
    Replies: 0
    Last Post: Jan 25th 2013, 02:08 AM
  2. Replies: 9
    Last Post: May 20th 2011, 09:29 PM
  3. Replies: 1
    Last Post: Oct 27th 2010, 04:49 AM
  4. Frequency Table Standard Deviation
    Posted in the Advanced Statistics Forum
    Replies: 9
    Last Post: Dec 12th 2009, 05:00 AM
  5. Idea of a frequency table
    Posted in the Statistics Forum
    Replies: 0
    Last Post: Oct 20th 2009, 05:38 PM

/mathhelpforum @mathhelpforum