Saturday, February 25, 2012

yogi_Compute Weighted Average For An Attribute Given Values Over Several Set Of Numbers

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #444

user mikem0123-bt said:
I have a business with a product in which the margin changes each time I order it. It's important for us to get an accurate average for the margins and the process we are using now, creating a new line in the spreadsheet for each new margin, is time consuming and I know there can be a better way.
Here is an example of a product and it's margins over a month:
Product X:
December 1-6: .52 (or 52% margin)
December 7-18: .54
December 19-31: .59
The quick-and-dirty route would be to take the three and just average them, but I end up with an inaccurate number because each margin is weighted differently based on how many days it's being used.
Any help or suggestions will be greatly appreciated!
following is a solution to the problem: