Formula: Weighted Average

[Alejandro Rodriguez]

.
Hello,

I need a formula to calculate the unit cost based on the weighted average method.

I enclose an example with the results required (column color yellow).
(Unit Cost)

Appreciate any help.

Excel 2003
	A	B	C	D	E
1
2					RESULT
3					REQUIRED
4
5	PRICE	IN	OUT	BALANCE	UNIT COST
6	15.00	100		100	15.00
7			50	50	15.00
8	20.00	200		250	19.00
9			200	50	19.00
10			30	20	19.00
11	10.00	1,000		1,020	10.18
12	18.00	200		1,220	11.46
13			70	1,150	11.46
14			150	1,000	11.46
15	30.00	400		1,400	16.76
16			200	1,200	16.76
17			500	700	16.76
18			350	350	16.76
19	19.00	300		650	17.79
20			60	590	17.79
21	22.00	50		640	18.12
22
stock

 

[Andrew Poulsom]

Formula in E6 copied down:

Excel 2010
	A	B	C	D	E
1
2					RESULT
3					REQUIRED
4
5	PRICE	IN	OUT	BALANCE	UNIT COST
6	15	100		100	15
7			50	50	15
8	20	200		250	19
9			200	50	19
10			30	20	19
11	10	1,000		1,020	10.18
12	18	200		1,220	11.46
13			70	1,150	11.46
14			150	1,000	11.46
15	30	400		1,400	16.76
16			200	1,200	16.76
17			500	700	16.76
18			350	350	16.76
19	19	300		650	17.79
20			60	590	17.79
21	22	50		640	18.12
Sheet1

Cell Formulas
Range	Formula
E6	=ROUND(IF(ISNUMBER(A6),((A6*B6)+IF(ISNUMBER(D5),(D5*E5),0))/D6,E5),2)

 

[Alejandro Rodriguez]

.
Thank you, Andrew.
The formula works very well. Thank you.

One question. Just for curiosity.
It could be a way to not include cell E5 in your formula?
If not or impossible, no problem.

Again, I highly appreciate your help.
.

 

[Andrew Poulsom]

How could you do it without taking the previous average price into account?

 

[Alejandro Rodriguez]

.
I think I cannot do it.
Once again. Thank you Andrew.
Problem solved.
.