So i'm running a type of handicapped league.
Im using Open office to quickly determine averages and Associate a handicap to that.
Right now im using a simple =AVERAGEIF(E4:Z4; ">0") to account for People not coming every week.
What i want to incorporate is a weighted average where it takes their Last 3 scores (not including 0s (no show)) and average that against the average of all other scores (again no 0s). in essence making their last 3 scores worth more then the all the rest.
I'm not very skilled with this program i only started using it a few weeks ago. But im willing to learn.
So that being said the more simplistic the better but i'm willing to try anything to make it work.
Thank you for your time!
[Solved] Help with weighted Averaging
[Solved] Help with weighted Averaging
Last edited by MrProgrammer on Thu Dec 31, 2020 5:33 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.5 on Windows 10
Re: Help with weighted Averaging
Quickly I think your best bet would be to use the AVERAGEIFS function, which is similar to the AVERAGEIF function, but does have the parameters in different order.
The AVERAGEIFS function is not in the help file, but will generate a balloon help when entering it, presuming you haven't disabled that feature.
Using your current parameters would be a good start, and I suggest adding a COUNTIF function so that you have the correct number of entries before you get the AVERAGE calculated would be good idea.
The AVERAGEIFS function is not in the help file, but will generate a balloon help when entering it, presuming you haven't disabled that feature.
Using your current parameters would be a good start, and I suggest adding a COUNTIF function so that you have the correct number of entries before you get the AVERAGE calculated would be good idea.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
- MrProgrammer
- Moderator
- Posts: 4906
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Help with weighted Averaging
I don't see how your procedure accomplishes the goal. Say your scores are 100, 20, 20, and 20, with the 20s being the most recent. The "average of all other scores" is 100. So we average 100, 20, 20, and 20 to get 40? This is double any of the "last three scores". Are you sure this procedure is what you want?Tronzo wrote:What i want to incorporate is a weighted average where it takes their Last 3 scores (not including 0s (no show)) and average that against the average of all other scores (again no 0s). in essence making their last 3 scores worth more then the all the rest.
The normal way to give more importance to the last scores is to assign weight one to all but the last three and assign a weight larger than one to those three. I have no idea if you should assign 2, 5.219, or 17. That's your decision.
I would calculate a weighted average as the ratio of two SUMPRODUCT functions, but the details will depend on how you assign weights. The numerator calculates the weighted sum, the denominator calculates the weighted count excluding scores of zero.
[Tutorial] Ten concepts that every Calc user should knowTronzo wrote:I'm not very skilled with this program i only started using it a few weeks ago.
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: Help with weighted Averaging
I have been tinkering around with the program to get more acquainted with Calc Before i came back.
This is Exactly what i want. The Scores generally wont Vary as much as you have indicated.
So i think i've figured out how make the COUNTIF Function to work with the AVERAGEIF function. I just cant seem to find a way to get Calc to Grab the last 3 Values.
Thanks
This is Exactly what i want. The Scores generally wont Vary as much as you have indicated.
For Instance Say the Numbers Were 48,52,56,54,52 I would want The average of the last 3 which would be 54 and the average of the first 2 would be 50. So together they would be 52 overall.he "average of all other scores" is 100. So we average 100, 20, 20, and 20 to get 40? This is double any of the "last three scores". Are you sure this procedure is what you want?
So i think i've figured out how make the COUNTIF Function to work with the AVERAGEIF function. I just cant seem to find a way to get Calc to Grab the last 3 Values.
Thanks
OpenOffice 4.1.5 on Windows 10
- MrProgrammer
- Moderator
- Posts: 4906
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Help with weighted Averaging
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.Tronzo wrote:This is Exactly what i want.
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: Help with weighted Averaging
That's in fact the problem.Tronzo wrote:I just cant seem to find a way to get Calc to Grab the last 3 Values.
For an alternative solution see:
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: Help with weighted Averaging
Tronzo: There are many ways to organize a table of scores such as this. Also, there will often be exceptions where sensible handling may depend on data structure (e.g. new participants, who haven't yet been to 3 events). Could you upload a copy of your file as an attachment here, so we can see how your data is laid out? This makes it easier to suggest useable formulas.
Make a copy of your file, where you mangle the names and any other participant related data to make it impossible to identify real persons from your file (remember that this is a public place).
Make a copy of your file, where you mangle the names and any other participant related data to make it impossible to identify real persons from your file (remember that this is a public place).