Calculating moments in MS Excel

[loom91]

Hi,

As part of a school project on statistics, I'm trying to calculate some parameters of collected data. I've the marks of 50 randomly sampled students in 6 subjects. I'm trying to calculate their skewness. Since the inbuilt Excel function to calculate skewness uses an estimator I've never seen, I'm using the standard estimator:

$$\frac {\sqrt{n (n-1)}}{n (n-2)} \frac {\Sigma_i (x_i - \bar{x})^3}{s^3}$$

Though this is not unbiased, it's the best I have as I can not assume normality (which is creating a load of problems) because the population is heavily (-)vely skewed .

In Excel, I've written the following formula:

=(SQRT($A51*($A51-1))/(($A51-2)*$A51))*(SUM((D2:D51 - D53)^3)/POWER(D55,3))

where D2:D51 contains the data, $A51 is n, D53 is the mean and D55 is the sd.

Is this correct? It seems a pity Excel has no in-built functions to calculate moments. Also, one of the values is coming out to be -1.099, and I can't remember whether odd-order standardized moments can be less than -1

Thanks for your help. This is really important or me.

Molu

 

[EnumaElish]

From Excel help:

SKEW(number1,number2,...)

Number1, number2 ... are 1 to 30 arguments for which you want to calculate skewness. You can also use a single array or a reference to an array instead of arguments separated by commas.

Remarks

Arguments can either be numbers or names, arrays, or references that contain numbers.
Logical values and text representations of numbers that you type directly into the list of arguments are counted.
If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
Arguments that are error values or text that cannot be translated into numbers cause errors.
If there are fewer than three data points, or the sample standard deviation is zero, SKEW returns the #DIV/0! error value.
The equation for skewness is defined as:
$$\frac {n}{(n-1)(n-2)} \sum_i \left(\frac {x_i - \bar{x}}{s}\right)^3$$

If you have to use your formula, all you have to do is to multiply Excel's result with $$\frac {\sqrt{n (n-1)}}{n (n-2)}\times \frac{(n-1)(n-2)}{n}.$$

 

[Architeuthis Dux]

Hi Molu,

Calculating moments in Excel can be a bit tricky, but it looks like you have the right idea with your formula. The standard estimator you are using is a common method for calculating skewness when the population is heavily skewed, so it should give you a good estimate of the skewness for your data.

As for the negative value for one of the moments, this is possible for odd-order standardized moments. In fact, the skewness coefficient can range from -3 to +3, so a value of -1.099 is within the acceptable range.

It is unfortunate that Excel does not have built-in functions for calculating moments, but with your formula, you should be able to accurately calculate the skewness for your data. Keep in mind that this is just one measure of the distribution of your data, and it is always important to consider other measures and graphical representations to fully understand the characteristics of your data.

Best of luck with your project!

Sincerely,