7.ods (43.7 KB)
How can I calculated the weighted average on a column based on the conditions from the G and H columns?
Thank you.
The AI says to use the sumproduct function, but i don’t know how to adapt it to my situation.
https://search.brave.com/search?q=libreoffice+calc+weighted+average&summary=1&conversation=0927f6ab87fb71c5682a0bb583b7d6fccddb
Better ignore what "AI says", and make definitely clear for yourself in what sense you mean “weighted average” in your specific case.
I suppose you learned something about averages in school. If not, you can’t reasonably chat with any AI about the topic. Humans may correctly guess what you mean in some cases. Concerning my own guess, I don’t feel sure.
The attached example is without any guarantee!
SUMPRODUCT() with two arguments can return something like a "weighted sum" in your case. To get a weighted average you still need to divide by the number of items then.
disask_135353_ExampleRe.ods (47.7 KB)
By weighted average i mean that more recent numbers have a higher impact on the result than older numbers.
So basically i have to assign a weight for every day.
So far I think I understood. But there’s more about it. Otherwise you wouldn’t have had to ask.
Did you understand and accept the solution I suggested and exemplified?
For what reason did you define the weights in steps of 30 days?
IMO the Average of the Weights must be 1, otherwise:
=AVERAGE(values * weights)/AVERAGE( weights )
Because it is a pretty good number to estimate a month.
The solution is pretty good; the problem is that in my case i have 3 columns with good results and another 3 columns with bad results.
8.ods (66.3 KB)
I want to see if progress is made, but the problem is that more recent results don’t change too much, that’s why i thought that a weighted average might solve the problem.
Edit:
Do you see anything wrong with this? I don’t understand why it calculates for the first row, but all the others have an error.
8.1.ods (64.6 KB)
@karolus
I only tried to work along the OQ’s example.
A more general definition of Weighted Average is
WA = SUM(Products(VALUE*WEIGHT))/SUM(Weights)
In a spreadsheet you can enter this explicitly for array evaluation or use SUMPRODUCT(VALUES;WEIGHTS)/SUM(WEIGHTS)
and rely on the ForceArray clause for the arguments of SUMPRODUCT().
In the first case you need to be cautious when filling to avoid the creation of a locked output range with constant values.
I would also consider to replace the discrete table of weights with lookup by a continuous weighting function.
disask_135353_WeightedAverage_ExampleExtendedRe.ods (78.0 KB)
1 Like