[Solved] Help with weighted Averaging

[Tronzo]

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!

[RusselB]

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.

[MrProgrammer]

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 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?

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.

I'm not very skilled with this program i only started using it a few weeks ago.

[Tutorial] Ten concepts that every Calc user should know

[Tronzo]

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.

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?

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.

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

[MrProgrammer]

This is Exactly what i want.

201807181517.ods

(11.28 KiB) Downloaded 87 times

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.

[Lupp]

I just cant seem to find a way to get Calc to Grab the last 3 Values.

That's in fact the problem.
For an alternative solution see:

aoo94209AverageLastThree_1.ods

(18.56 KiB) Downloaded 89 times

[keme]

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).