how to encode statistical formula

[Anastasia Gryaznov]

Hello,
Could you please help me to encode (make it in one function) the following
formula:

[SUM[(X(i) - Mean(X))^4] / ( ((SUM[(X(i) - Mean(X))^2] / N)^2) ] / N

where X(i) is group of numbers stored in cell range A1:A100.

Thanks.

 

[Sheeloo]

Do you want a USER DEFINED FUNCTION for this? I can write that tomorrow... if
you want... Can you send me some sample data to test?

btw what is this formula computing?

If your can live with some manipulatoin then
1. Calcuate average in A101
=Average(A1:A100)
2. Enter in B1 and copy till B100
=(A1-$A$101)^4
3. Enter in C1 and copy till C100
=(A1-$A$101)^2
4. Enter in B101
=Sum(B1:B100) and copy right to C101
5. Finally enter this where you want your result
=(B101/(C101)^2)/100

Hope I got the formula right...

 

[Sheeloo]

Step 5 formula should be
=(B101/(C101/100)^2)/100

Here is the UDF
Function StatFormula(rng As Range, N As Integer) As Double
Dim i As Integer
Dim Avg, Xi, SumXP2, SumXP4 As Double
Dim c As Range
Avg = WorksheetFunction.Average(rng)
For Each c In rng
Xi = c.Value
SumXP2 = SumXP2 + (Xi - Avg) ^ 2
SumXP4 = SumXP4 + (Xi - Avg) ^ 4
Next
StatFormula = (SumXP4 / (SumXP2 / N) ^ 2) / N
End Function

Do you want a USER DEFINED FUNCTION for this? I can write that tomorrow... if
you want... Can you send me some sample data to test?

btw what is this formula computing?

If your can live with some manipulatoin then
1. Calcuate average in A101
=Average(A1:A100)
2. Enter in B1 and copy till B100
=(A1-$A$101)^4
3. Enter in C1 and copy till C100
=(A1-$A$101)^2
4. Enter in B101
=Sum(B1:B100) and copy right to C101
5. Finally enter this where you want your result
=(B101/(C101)^2)/100

Hope I got the formula right...

Hello,
Could you please help me to encode (make it in one function) the following
formula:

[SUM[(X(i) - Mean(X))^4] / ( ((SUM[(X(i) - Mean(X))^2] / N)^2) ] / N

where X(i) is group of numbers stored in cell range A1:A100.

Thanks.

 

[MikhailGr]

Thank you very much.
This formula is for Kurtosis. I know there is Excel's Kurtosis, but the
problem is I need to use a different approach (there are several methods for
calculating Kurtosis).

Step 5 formula should be
=(B101/(C101/100)^2)/100

Here is the UDF
Function StatFormula(rng As Range, N As Integer) As Double
Dim i As Integer
Dim Avg, Xi, SumXP2, SumXP4 As Double
Dim c As Range
Avg = WorksheetFunction.Average(rng)
For Each c In rng
Xi = c.Value
SumXP2 = SumXP2 + (Xi - Avg) ^ 2
SumXP4 = SumXP4 + (Xi - Avg) ^ 4
Next
StatFormula = (SumXP4 / (SumXP2 / N) ^ 2) / N
End Function

Do you want a USER DEFINED FUNCTION for this? I can write that tomorrow... if
you want... Can you send me some sample data to test?

btw what is this formula computing?

If your can live with some manipulatoin then
1. Calcuate average in A101
=Average(A1:A100)
2. Enter in B1 and copy till B100
=(A1-$A$101)^4
3. Enter in C1 and copy till C100
=(A1-$A$101)^2
4. Enter in B101
=Sum(B1:B100) and copy right to C101
5. Finally enter this where you want your result
=(B101/(C101)^2)/100

Hope I got the formula right...

Hello,
Could you please help me to encode (make it in one function) the following
formula:

[SUM[(X(i) - Mean(X))^4] / ( ((SUM[(X(i) - Mean(X))^2] / N)^2) ] / N

where X(i) is group of numbers stored in cell range A1:A100.

Thanks.

 

[Sheeloo]

Ok. I will have to brush up my Statistics.

Did you get what you wanted?

Thank you very much.
This formula is for Kurtosis. I know there is Excel's Kurtosis, but the
problem is I need to use a different approach (there are several methods for
calculating Kurtosis).

Step 5 formula should be
=(B101/(C101/100)^2)/100

Here is the UDF
Function StatFormula(rng As Range, N As Integer) As Double
Dim i As Integer
Dim Avg, Xi, SumXP2, SumXP4 As Double
Dim c As Range
Avg = WorksheetFunction.Average(rng)
For Each c In rng
Xi = c.Value
SumXP2 = SumXP2 + (Xi - Avg) ^ 2
SumXP4 = SumXP4 + (Xi - Avg) ^ 4
Next
StatFormula = (SumXP4 / (SumXP2 / N) ^ 2) / N
End Function

Do you want a USER DEFINED FUNCTION for this? I can write that tomorrow... if
you want... Can you send me some sample data to test?

btw what is this formula computing?

If your can live with some manipulatoin then
1. Calcuate average in A101
=Average(A1:A100)
2. Enter in B1 and copy till B100
=(A1-$A$101)^4
3. Enter in C1 and copy till C100
=(A1-$A$101)^2
4. Enter in B101
=Sum(B1:B100) and copy right to C101
5. Finally enter this where you want your result
=(B101/(C101)^2)/100

Hope I got the formula right...

:

Hello,
Could you please help me to encode (make it in one function) the following
formula:

[SUM[(X(i) - Mean(X))^4] / ( ((SUM[(X(i) - Mean(X))^2] / N)^2) ] / N

where X(i) is group of numbers stored in cell range A1:A100.

Thanks.

 

[Jerry W. Lewis]

Two ways are

=SUM(data-AVERAGE(data))^4)/VARP(data)^2/n
array entered (Ctrl-Shift-Enter), or wrapped in a VBA Evaluate() function.

=(KURT(data)*(n-2)*(n-3)+3*(n-1)^2)/(n-1)/(n-2)

Jerry

 

[MikhailGr]

Yes, I did it "step by step".
Thanks.

Ok. I will have to brush up my Statistics.

Did you get what you wanted?

Thank you very much.
This formula is for Kurtosis. I know there is Excel's Kurtosis, but the
problem is I need to use a different approach (there are several methods for
calculating Kurtosis).

Step 5 formula should be
=(B101/(C101/100)^2)/100

Here is the UDF
Function StatFormula(rng As Range, N As Integer) As Double
Dim i As Integer
Dim Avg, Xi, SumXP2, SumXP4 As Double
Dim c As Range
Avg = WorksheetFunction.Average(rng)
For Each c In rng
Xi = c.Value
SumXP2 = SumXP2 + (Xi - Avg) ^ 2
SumXP4 = SumXP4 + (Xi - Avg) ^ 4
Next
StatFormula = (SumXP4 / (SumXP2 / N) ^ 2) / N
End Function
:

Do you want a USER DEFINED FUNCTION for this? I can write that tomorrow... if
you want... Can you send me some sample data to test?

btw what is this formula computing?

If your can live with some manipulatoin then
1. Calcuate average in A101
=Average(A1:A100)
2. Enter in B1 and copy till B100
=(A1-$A$101)^4
3. Enter in C1 and copy till C100
=(A1-$A$101)^2
4. Enter in B101
=Sum(B1:B100) and copy right to C101
5. Finally enter this where you want your result
=(B101/(C101)^2)/100

Hope I got the formula right...

:

Hello,
Could you please help me to encode (make it in one function) the following
formula:

[SUM[(X(i) - Mean(X))^4] / ( ((SUM[(X(i) - Mean(X))^2] / N)^2) ] / N

where X(i) is group of numbers stored in cell range A1:A100.

Thanks.

 

[MikhailGr]

I have some questions:
1.I understand how SUM(A1:A100) works. But I do not understand how
SUM(A1:A100-C5)works. Excel doesn’t allow me to enter this expression.
At what point should I press Ctrl+Shift+Enter?

2.Are you saying that if I modify Excel’s Kurtosis formula as you suggested,
I will get the formula I want?

Thanks.

Two ways are

=SUM(data-AVERAGE(data))^4)/VARP(data)^2/n
array entered (Ctrl-Shift-Enter), or wrapped in a VBA Evaluate() function.

=(KURT(data)*(n-2)*(n-3)+3*(n-1)^2)/(n-1)/(n-2)

Jerry

Hello,
Could you please help me to encode (make it in one function) the following
formula:

[SUM[(X(i) - Mean(X))^4] / ( ((SUM[(X(i) - Mean(X))^2] / N)^2) ] / N

where X(i) is group of numbers stored in cell range A1:A100.

Thanks.

 

[Jerry W. Lewis]

Sorry, there was a missing parenthesis, the formula should have been
=SUM((data-AVERAGE(data))^4)/VARP(data)^2/n
A slightly simpler array formula is
=AVERAGE((data-AVERAGE(data))^4)/VARP(data)^2
Or you can avoid array entry with
=SUMPRODUCT((data-AVERAGE(data))^4)/VARP(data)^2/n

I also miscopied the final divisor for the second formula, which should be
=(KURT(data)*(n-2)*(n-3)+3*(n-1)^2)/(n-1)/(n+1)

All four formulas should give the same answer. If you use the example data
from Help for KURT
3, 4, 5, 2, 3, 4, 5, 6, 4, 7
then all four formulas return 2.36867899309423

Jerry

I have some questions:
1.I understand how SUM(A1:A100) works. But I do not understand how
SUM(A1:A100-C5)works. Excel doesn’t allow me to enter this expression.
At what point should I press Ctrl+Shift+Enter?

2.Are you saying that if I modify Excel’s Kurtosis formula as you suggested,
I will get the formula I want?

Thanks.

Two ways are

=SUM(data-AVERAGE(data))^4)/VARP(data)^2/n
array entered (Ctrl-Shift-Enter), or wrapped in a VBA Evaluate() function.

=(KURT(data)*(n-2)*(n-3)+3*(n-1)^2)/(n-1)/(n-2)

Jerry

Hello,
Could you please help me to encode (make it in one function) the following
formula:

[SUM[(X(i) - Mean(X))^4] / ( ((SUM[(X(i) - Mean(X))^2] / N)^2) ] / N

where X(i) is group of numbers stored in cell range A1:A100.

Thanks.