Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to encode statistical formula
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to encode statistical formula
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... "Anastasia Gryaznov" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to encode statistical formula
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 "Sheeloo" wrote: 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... "Anastasia Gryaznov" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to encode statistical formula
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). "Sheeloo" wrote: 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 "Sheeloo" wrote: 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... "Anastasia Gryaznov" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to encode statistical formula
Ok. I will have to brush up my Statistics.
Did you get what you wanted? "MikhailGr" wrote: 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). "Sheeloo" wrote: 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 "Sheeloo" wrote: 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... "Anastasia Gryaznov" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to encode statistical formula
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 "Anastasia Gryaznov" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to encode statistical formula
Yes, I did it "step by step".
Thanks. "Sheeloo" wrote: Ok. I will have to brush up my Statistics. Did you get what you wanted? "MikhailGr" wrote: 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). "Sheeloo" wrote: 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 "Sheeloo" wrote: 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... "Anastasia Gryaznov" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to encode statistical formula
I have some questions:
1.I understand how SUM(A1:A100) works. But I do not understand how SUM(A1:A100-C5)works. Excel doesnt allow me to enter this expression. At what point should I press Ctrl+Shift+Enter? 2.Are you saying that if I modify Excels Kurtosis formula as you suggested, I will get the formula I want? Thanks. "Jerry W. Lewis" wrote: 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 "Anastasia Gryaznov" wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to encode statistical formula
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 "MikhailGr" wrote: I have some questions: 1.I understand how SUM(A1:A100) works. But I do not understand how SUM(A1:A100-C5)works. Excel doesnt allow me to enter this expression. At what point should I press Ctrl+Shift+Enter? 2.Are you saying that if I modify Excels Kurtosis formula as you suggested, I will get the formula I want? Thanks. "Jerry W. Lewis" wrote: 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 "Anastasia Gryaznov" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
URL Encode | Excel Worksheet Functions | |||
URL encode a string | Excel Programming | |||
encode in UTF-8 from macro? | Excel Discussion (Misc queries) | |||
A rather difficult statistical search formula needed (Part 2) | Excel Worksheet Functions | |||
A rather difficult & complex statistical search formula needed | Excel Worksheet Functions |