How do I compute this:
Third central and non-central moment of a column of real valued data in Libre Office ?
What I need would be a macro computing
k-th central and non-central moment between A2 to A21 in this shape, analogously as is written an ordinary average:
=AVERAGE(A2:A21)
I have tried roughly this
=AVERAGE((A2:A21)-1)
but syntax error has occurred.
The Math formula editor does not compute anything. It just format formula nicely. You probably mean you want to compute these statistical moments in a spreadsheet.
Then please retag to replace math by calc.
I’ve tried to retag but I cannot do that.
The trick is to press Enter twice. I’ll do it for you.
and what about my question, will you do that as well ?
You find listed the implemented statistical functions if you open the formula wizard and select the ‘Statistical’ category.
If a function you want to use isn’t listed there, you may create a formula, probably based on the result from helper cells.
Anyway you need to know the related facts from statistics to reliably use the moments you talk of.
I wouldn’t expect you get a statistical tutorial here by someone.
@max2: I won’t answer your question because my field of competence is Writer and Math. I got an automatic notification of your question because you tagged math. This illustrates the importance of relevant tagging though the feature is mysterious to newcomers.
From the definition of moments, you need auxiliary cells for the higher powers of you data, e.g. B1:B21 for squares, C1:C21 for cubes, etc. Once done, you simply compute averages of these area and combine them linearly.
(Being back:)
Depending on your knowledge you may know what to expect the mentioned statistical functions to do based on their names. The formula wizard and the help files may give a bit more insight.
However, the mandatory specifications of the functions are found in chapter 6 of Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format .
I thought that there would be a formula computing higher moments without auxiliary cells ?
The most common “moments” in usual statistical spreadsheet (understand: business) are mean and standard deviation. Others are considered more “scientific” or “math” oriented. Therefore you must create formulas for them. Since they involve higher powers of samples (minus the mean), the more “comfortable” way is to split the math formula into simpler factors and combine them in the end.
Just in addition:
Using the ordinary way of speaking, the standard deviation isn’t a moment. Variance is.
The normalized (with respect to σ) third moment, the skewness, is also implemented by standard functions: SKEWP() to use if the complete population is passed to the function, SKEW() as an estimator if only a sample was drawn.
Higher moments or their normalized versions, and absolute moments aren’t implemented.
OK. Is there a hand created macro computing higher moments from the cells between A2 to A21 without using auxiliary cells ?
-1- You still didn’t make clear if you want to calculate actual moments for known populations or estimator values based on samples.
-2- I personally can implement one of the formulas I find for the topic by user code, but having not done anything in the field of “higher statistics” for decades now, I cannot take any responsibility concerning the applicability under specific conditions. Not having means to at least check results of higher order than 3 (skewness) there should, of course, also implementation errors be expected.
-3- Since I don’t feel sure you can reliably decide what’s needed to get correct and meaningful results, I should not make tools for you. They might tempt you to use them without the sharp criticism they deserve.
This said, I yet post a demo document containing a user function for the purpose which I made in a playful mood.
ask275423HigherStatisticalMoments.ods
Be careful! There’s surely enough bad (evil?) statistics in the world.
===Edit 2020-11-08 about 21:40 UTC===
If this wasn’t about statistical moments, the use-case should have been clearly explained from the beginning.
The code below will calculate moments in the most primitive way. If central moments are required, the pAxis parameter must get passed the AVERAGE of the sequence.
Function simpleGeneralMoment(pAxis As Double, pOrder As Long, pSequence)
REM No centralization, no normalization, no anythingation! No statistics at all.
REM pSequence must be data from a contiguous part of a single column.
fa = CreateUnoService("com.sun.star.sheet.FunctionAccess")
N = Ubound(pSequence)
count = fa.callFunction ("COUNT", Array(pSequence))
If count<N Then
simpleGeneralMoment = "numbers only!"
Exit Function
End If
For j = 1 To N
pSequence(j, 1) = (pSequence(j, 1)-pAxis)^pOrder
Next j
simpleGeneralMoment = fa.callFunction("AVERAGE", Array(pSequence))
End Function
The formula =SUM(($A$2:$A$21 - pAxis)^3) / COUNT($A$2:$A$21) entered for array-evaluation would also return the third moment for your example. I just don’t know by what value (for pAxis) you want to replace the center.
There are missing items in your spreadsheet:
There are items "#NAME? in A2 etc say:
What should I do?
You didn’t tell your LibO version. The non-volatile variants of RAND() and RANDBETWEEN() were only recently implemented. I used them to avoid permanent recalculations of the example. You should use your own test data, of course.
the CENTRAL moment is this macro: =CENTRALNORMALIZEDMOMENT(A$2:A$31; C6)
What about NONCENTRAL ONE ?
Please I need the NONCENTRAL MOMENT yet. How can I do that, Are You There ? Thank you.
There are mainly three fields related to this topc: Mathematical statistics, programming (less), and (least) spreadsheets. I didn’t get the impression you are sufficiently familiar with them.
I myself neither used higher moments nor non-central moments (if not mechanical) ever in my life, and I’m all but sure that I got that right. Thus I have a bad feeling about a probably erroneous function I give to you. This the more as you probably may not find the errors, and misuse a clearly unproven function to get results someone may take for “professionally” produced. Again: Be careful!
ask275423HigherStatisticalMoments2.ods
You have just saved me! My English is too poor to grasp everything what you have written above but I’m happy with your GENERALIZEDNORMALIZEDMOMENT. Now, I would like to get rid off all NORMALIZED stuff in all of your moments. I would like to have NOT NORMALIZED MOMENTS. Is that possible ? What theory I have read about MOMENTS is this: Understanding Moments
BTW, WHERE have you defined your macros =CENTRALNORMALIZEDMOMENT(A$2:A$31; C13) ? I cannot find it in your 2 spreadsheets.
Basic code delivered as working code with a document must always be contained in a module of the Standard Basic library of the respective document.
Code you want to work with any document should be moved to the Standard library of the local LibreOffice. (You shouldn’t use a chainsaw without having read a few pages in the manual.)