Hello all,
I am new here and have often visited for workarounds, but not actually posted.
So, my query...
I am currently wanting to shorten my workstream in relation to averaging. I currently use a 3 pronged method of AVERAGE, GEOMEAN and HARMEAN functions, to calculate an absolute "AVERAGE/MEAN"; iterating until I get them to equal TRUE against each other. However this can often be quite space/time consuming, so I need to work out a workaround.
Is there a way this can be done in such a way that would end up with a custom function like ABSOLUTEMEAN(A2:L2)?
Coding is not my forte, and I know this is asking a lot, but it needs to be cyclic(nested) until the resultant cells all equal TRUE.
Thanks in advance
[Solved] Custom AVERAGE/MEAN function
[Solved] Custom AVERAGE/MEAN function
Last edited by Hagar Delest on Fri Feb 15, 2019 9:21 am, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
Open Office 4.1.6 on Windows 7 N Ultimate / Open Office 4.1.6 on macOS 10.14.3
Re: Custom AVERAGE/MEAN function
You can do some iterationsCoding is not my forte, and I know this is asking a lot, but it needs to be cyclic(nested) until the resultant cells all equal TRUE.
- in a macro code, or
- in the cells by usage the circular references.
You must switch on the iteration feature for the "iteration in cells" in the Menu :
Tools - Options - Open/LibreOffice Calc - Calculate - Iterative references ... Check in the checkbox, and set the maximum number of the iteration Steps, and the desired Minimum change value.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Custom AVERAGE/MEAN function
Can you help me to get some sense out of this? If you want advice how to get a user function calculating something, you should specify that something clearly. If you already have a -probably inefficient- method to get what you want by Calc formulae you can attach a sample sheet demonstrating it. A clear specification independent of any kind of known implementation is better, however, or should at least be tried in addition.nicalex wrote:I currently use a 3 pronged method of AVERAGE, GEOMEAN and HARMEAN functions, to calculate an absolute "AVERAGE/MEAN"; iterating until I get them to equal TRUE against each other. However this can often be quite space/time consuming, so I need to work out a workaround.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Custom AVERAGE/MEAN function
Check Help and the Calc Guide for Goal Seek and Solver. Both can be found under Tools.
LO 6.4.4.2, Windows 10 Home 64 bit
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Re: Custom AVERAGE/MEAN function
I had a simple idea, perhaps correctly understood ...
- Attachments
-
- Test-Mean.ods
- Thought this?
- (16.15 KiB) Downloaded 93 times
LibreOffice 7.6.4.1 on Ubuntu 20.04.4 LTS
- MrProgrammer
- Moderator
- Posts: 4894
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Custom AVERAGE/MEAN function
Hi, and welcome to the forum.
For two numbers, the AGHM will be GEOMEAN(A2;B2). For all positive numbers, I believe we will find:
The rate of convergence for the AGHM is quadratic, so one probably needs just a few, say a dozen, iterations to get convergence. Just perform a fixed number, instead of using a loop, if you insist on using the AGHM.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
I cannot find much literature about the properties of the Arithmetic-Geometric-Harmonic mean (AGHM). That suggests to me that it is not statistically or mathematically useful. If this is your own idea, please consider using standard statistical measures instead. Unless you have a good reason not to, I suggest that you use the average of the data. This has good statistical properties and has been studied for hundreds of years.nicalex wrote:I currently use a 3 pronged method of AVERAGE, GEOMEAN and HARMEAN functions, to calculate an absolute "AVERAGE/MEAN"; iterating until I get them to equal TRUE against each other.
For two numbers, the AGHM will be GEOMEAN(A2;B2). For all positive numbers, I believe we will find:
Harmonic mean ≤ Geometric-Harmonic mean ≤ Arithmetic-Harmonic mean ≤ AGHM
≤ Geometric mean ≤ Arithmetic-Geometric mean ≤ Arrithmetic mean
The rate of convergence for the AGHM is quadratic, so one probably needs just a few, say a dozen, iterations to get convergence. Just perform a fixed number, instead of using a loop, if you insist on using the AGHM.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Last edited by MrProgrammer on Sun Feb 17, 2019 6:44 pm, edited 4 times in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Custom AVERAGE/MEAN function
Can you please define more accurately what you are trying to do.
For example, let us have something like:
I have three values, a, b and c.
I calculate the Average as (a+b+c)/3.
I calculate the Geometric Mean as cube_root(a.b.c)
I calculate the Harmonic Mean as reciprocal((1/a +1/b + 1/c)/3).
I now to adjust ??? such that ...
For example, let us have something like:
I have three values, a, b and c.
I calculate the Average as (a+b+c)/3.
I calculate the Geometric Mean as cube_root(a.b.c)
I calculate the Harmonic Mean as reciprocal((1/a +1/b + 1/c)/3).
I now to adjust ??? such that ...
LO 6.4.4.2, Windows 10 Home 64 bit
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.
Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Re: Custom AVERAGE/MEAN function
Thank you for all the responses folks, it is very much appreciated! But a bigger thanks goes to lader, you did understand correctly
Open Office 4.1.6 on Windows 7 N Ultimate / Open Office 4.1.6 on macOS 10.14.3