Excel formula for standard devation

[sunboy]

Hi

I'm kindly asking for excel formula for STANDARD DEVATION on cell D6.

Thank you.

[MrShorty]

I'm not seeing source data for the average or the standard deviation, so I'm not sure how detailed we can be in our response. I know pivot tables can calculate mean and standard deviation. If this is a "summarizing a database of values" kind of problem, using a pivot table for both mean and standard deviation might be the easiest. If it must be a formula, then you can use the an appropriate STDEV.nn() function (perhaps with a nested IF() array function) to calculate the standard deviation.

From there, a detailed solution will depend on details you have not shared with us. Help us understand exactly what part of calculating a standard deviation you are having trouble with, and we should be able to help further.

[sunboy]

Hi

I can calculate standard deviation using this formula STDEV.S but I want a formula to calculate standard deviation when given mean, so that I summaries my data. I want to start presenting my data from mean stage as shown the worksheet attached.

Thank you.

[MrShorty]

but I want a formula to calculate standard deviation when given mean,

I'm not sure I understand. Are you wanting an Excel formula, or a more generic statistics formula? With my limited knowledge of statistics, I am not aware of any formula or methodology or algorithm that can calculate standard deviation given only the mean. If you can help us understand the statistical formula you want to use, we should be able to help you program that formula into the spreadsheet, but I'm not sure we have the statistical expertise to come up with the statistics formula (which will be needed before we can do the spreadsheet programming).

Or maybe I am completely misunderstanding what you want to do. In either case, help us understand the calculation you are trying to do, and we should be able to help program that into the spreadsheet.

[curiouscat408]

@sunboy.... You want the std dev of __what_data__?!

Do you want the std dev of the 60 numbers in C6:C65?

Or do you want the std dev of the data corresponding to the mean in each row C6, C7 etc?

If the latter, we cannot calculate the std dev given only the mean of some data.

The mathematical formula for the "sample" std dev is:

SQRT( ( (x₁ - u)² + (x₂ - u)² +...+ (x_n - u)² ) / (n -1) )

where x₁, x₂ etc are the individual data, and "u" (Greek mu) is the mean.

In your case, apparently "n" is 43 (C3). But that is of no use without the data for the purpose of calculating the std dev.

[sunboy]

Hi

I get it, thank you.