# Thread: Standard Deviation from a frequency table in Excel

1. ## 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.

2. ## 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.

3. ## 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!