Standard Deviation of a Frequency Distribution Table...

[EtherBoo]

Hello,

I'm in Statistics and we are doing standard deviations of a frequency distribution table. I have just a few questions.

1) I've managed to figure out how to do this using a very long formula. It has to be specific to the amount of rows. Is there anyway for me to tell the formula to ignore certain computations if the cells are blank or if the total equals 0.

2) Is there any built in functions for excel to do this on its own, or any known add-ins that I can download and install (I don't mind paying a small fee) that will help me doing this operation.

I've attatched what I have so far, I'd like to be able to set a range of variables like you can with averages (eg, =SUM(A1:A20)) to do what is shown in D1, so if I have a table with 5 rows, or 10 rows, I can easily put the numbers into the table without needing to add or remove from the forumula everytime I gain a row or lose a row.

SDev.xls
	A	B	C	D
1	1	10	StandardDeviation=	0.479463
2	2	20
Sheet1

Thanks for any help in advance.

 

[PaddyD]

Hi

" Is there anyway for me to tell the formula to ignore certain computations if the cells are blank or if the total equals 0. "

In general, excel can calculate conditionally - details will depend on the conditions

"Is there any built in functions for excel to do this on its own, "

Without upacking your formula (stats not really being my thing), have you reviewed the excel help file entries for the statistical functions?
(eg stdev(), stdevp() etc)??

Post back with details of what you're trying to do if you can't get it sorted...

 

[tusharm]

Use an intermediate cell for the average. It will dramatically simplify your life.

Suppose you name the range containing the values as Values (Insert | Name > Define...) and the range containing the frequencies as Frequencies.

Then, the average is =SUMPRODUCT(Values,Frequencies)/SUM(Frequencies) Suppose you name this cell as Avg.

Then, the variance is
=SUMPRODUCT((Values-Avg)^2,Frequencies)/(SUM(Frequencies)-1)

 

[EtherBoo]

Wow...

Thank you very much.

The people here never seem to let me down and always have a solution, thanks!

 

[tusharm]

You are welcome.

 

[bill_ohio]

I have a follow up question to this thread:

I thought this was what I was looking for, but not quite. I had already figured out how to find the standard deviation of a frequency distribution table just by having Excel do the grunt work calculations. (I wasn't looking for anything fancy.)

However, there is a built-in function, STDEV.P which I am trying to use.

The function works fine with a simple list of numbers, but I cannot find any help anywhere on how to use that function on a frequency distribution table.

Is this at all possible? I am looking for a non-VBA solution if there is one.

Thanks in advance - this message board never lets me down!!