Weighted Average

[#1]

Hello,

does someone would know how to calculate an average in a total according to others records?

Let say columns A is a sum(quantity), column B is already an average result of the quantity  (price)

Qty     Price

100        2 €

150        3 €

200       2,5€

------------------

450      avg(price) sould be   ((100 * 2) + (150 * 3) + (200 * 2.5) ) / 450

How can I get this?

best regards

Chris

[nirav_bhimani]

HI,

You want to do Avg of sum then try the following exp.

Avg ( Sum (Quantity* Price) ) / sum(Qunatity)

Regards,

Nirav Bhimani.

[#3]

Sorry, but you can't write this expression

I tried with an avg(aggr (sum(Q*P, Dim) / sum(Q)

but it doesn't give a good result either

christian

[jonathandienst · Accepted solution]

Christian

You are almost there. I think you need this:

Sum(Aggr(Sum(Q * P, Dim), Dim)) / Sum(TOTAL Q)

Regards

Jonathan

[#5]

Thank you John,

it matches with the result I got whan I compute in the script.

best regards

christian

[hic]

Jonathans formula calculates the correct answer, but... it is unnecessarily complicated. The Aggr() function is not needed. Just do

Sum(Price*Qty)/Sum(Qty)

HIC

[jonathandienst]

Well spotted Henric!