Weighted Average

[goldcomac]

I need to take a price increase on some of my products. I am allowed
to take 4% per year - total. I have some products that do not need
increasing and some that do.

Example:
Item "A" - no increase
Item "B" - $0.25 per unit
Item "C" - $0.18 per unit
Item "D" - no increase
Item "E" - no increase
Item "F" - $0.11 per unit
etc

I am trying to create a spreadsheet which will allow me to put the
current selling price in one column, the proposed selling price in
another column, the percent increase in a third column, and the
weighted average increase in a final column. I'm hoping that this
column will update each time I put in a new price.

Thank you.

 

[Stephane Quenson]

You need a few more columns:
- First one should contain the weight value for each item
- Second one should contain the formula: current price * weight
- Third column should contain the formula: new price * weight

Then, below your range, divide the sum of third column by the sum of second
column, then remove one. Format the result as percentage.

Recommendation: test it seriously with a few items (2 or 3) before relying
on the final result, so many business decisions having been based on excel
sheets with errors in formulas...

Stephane.