mercoledì 11 dicembre 2013

WEIGHTED AVERAGE with EXCEL, as it is estimated

How do you calculate the weighted average with EXCEL. 

With EXCEL is very easy to calculate the weighted average of a series of prices for a number of quantities. 


1) We have a table where different prices are quoted for quantities of materials, so the first thing we have to multiply all prices for all quantities (yellow boxes for green boxes), then we copy the formula down to do all the calculations. 
2) At the bottom of the column, we sum of the costs with the SUM function. 

3) Under the column of quantities, instead do directly the sum of all quantities. 

4) In the brown do a simple division between the total cost (price x quantity) divided by the total amount. 
In this way we obtained the weighted average of (5.72). 
I mean is the average price weighted to the quantities, or rather how much it costs, on average, a single piece. 
This result is very different from the simple average price that does not take into account the quantity, that fact alone would result in the average price (6.09).