Weighted average price formula

[gnaga]

Hi,

I need a formula to get an weighted average price of each item in a separate sheet. For Example please refer the below data. I have three different sheets where each item is marked with its qty and price. I have to make a new sheet where the weighted average price of each item is calculated. I need a formula for the Result Sheet4 to populate the Unique Item Code, Total Qty and its Weighted Average Price.

Sheet 1				Sheet 2				Sheet 3				Result Sheet4
Code	Qty	Price		Code	Qty	Price		Code	Qty	Price		Code	Tot.Q	Wt.Av.P
A	10	205.5		H	100	23.0		B	74	580.0		A	502	108.16
A	20	102.0		B	200	32.0		B	21	600.0		B	320	201.59
B	25	103.5		D	150	45.0		H	35	235.0		C	23	851.20
C	23	851.2		E	200	56.0		W	28	125.0		D	171	79.42
D	21	325.3		A	300	58.0		X	26	120.0		E	231	56.94
E	20	56.3		A	125	96.0		U	27	105.0		F	102	96.31
E	11	75.4		F	100	87.0		U	29	110.0		G	141	145.52
F	2	562.0		R	200	81.0		A	24	100.0		H	135	77.96
G	5	750.0		R	120	21.0		A	23	800.0		L	525	42.46
G	78	23.0		L	225	24.0		G	15	600.0		R	320	58.50
G	25	59.0		L	300	56.3		G	18	250.0		U	56	107.59
												W	28	125.00
												X	26	120.00

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Sheet 1				Sheet 2				Sheet 3							Formula
2	Code	Qty	Price		Code	Qty	Price		Code	Qty	Price		Code	Tot.Q	Wt.Av.P
3	A	10	205.5		H	100	23		B	74	580		A	502	108.16	502.00	108.16
4	A	20	102		B	200	32		B	21	600		B	320	201.59	320.00	201.59
5	B	25	103.5		D	150	45		H	35	235		C	23	851.2	23.00	851.20
6	C	23	851.2		E	200	56		W	28	125		D	171	79.42	171.00	79.42
7	D	21	325.3		A	300	58		X	26	120		E	231	56.94	231.00	56.95
8	E	20	56.3		A	125	96		U	27	105		F	102	96.31	102.00	96.31
9	E	11	75.4		F	100	87		U	29	110		G	141	145.52	141.00	145.52
10	F	2	562		R	200	81		A	24	100		H	135	77.96	135.00	77.96
11	G	5	750		R	120	21		A	23	800		L	525	42.46	525.00	42.46
12	G	78	23		L	225	24		G	15	600		R	320	58.5	320.00	58.50
13	G	25	59		L	300	56.3		G	18	250		U	56	107.59	56.00	107.59
14													W	28	125	28.00	125.00
15													X	26	120	26.00	120.00
16
Sheet2

Cell Formulas
Range	Formula
P3	P3	=SUM(IF(CHOOSE({1,2,3},$A$3:$A$13,$E$3:$E$13,$I$3:$I$13)=$M3,CHOOSE({1,2,3},B$3:B$13,F$3:F$13,J$3:J$13)))
Q3:Q15	Q3	=SUM(IF(CHOOSE({1,2,3},$A$3:$A$13,$E$3:$E$13,$I$3:$I$13)=$M3,CHOOSE({1,2,3},$C$3:$C$13*$B$3:$B$13,$G$3:$G$13*$F$3:$F$13,$K$3:$K$13*J$3:J$13)))/SUM(IF(CHOOSE({1,2,3},$A$3:$A$13,$E$3:$E$13,$I$3:$I$13)=$M3,CHOOSE({1,2,3},B$3:B$13,F$3:F$13,J$3:J$13)))
P4:P15	P4	=SUM(IF(CHOOSE({1,2,3},$A$3:$A$13,$E$3:$E$13,$I$3:$I$13)=M4,CHOOSE({1,2,3},$B$3:$B$13,$F$3:$F$13,$J$3:$J$13)))

 

[CA_Punit]

I just complicated things using choose out of curosity. Any using sumifs will be much easier.

 

[gnaga]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Sheet 1				Sheet 2				Sheet 3							Formula
2	Code	Qty	Price		Code	Qty	Price		Code	Qty	Price		Code	Tot.Q	Wt.Av.P
3	A	10	205.5		H	100	23		B	74	580		A	502	108.16	502.00	108.16
4	A	20	102		B	200	32		B	21	600		B	320	201.59	320.00	201.59
5	B	25	103.5		D	150	45		H	35	235		C	23	851.2	23.00	851.20
6	C	23	851.2		E	200	56		W	28	125		D	171	79.42	171.00	79.42
7	D	21	325.3		A	300	58		X	26	120		E	231	56.94	231.00	56.95
8	E	20	56.3		A	125	96		U	27	105		F	102	96.31	102.00	96.31
9	E	11	75.4		F	100	87		U	29	110		G	141	145.52	141.00	145.52
10	F	2	562		R	200	81		A	24	100		H	135	77.96	135.00	77.96
11	G	5	750		R	120	21		A	23	800		L	525	42.46	525.00	42.46
12	G	78	23		L	225	24		G	15	600		R	320	58.5	320.00	58.50
13	G	25	59		L	300	56.3		G	18	250		U	56	107.59	56.00	107.59
14													W	28	125	28.00	125.00
15													X	26	120	26.00	120.00
16
Sheet2

Cell Formulas
Range	Formula
P3	P3	=SUM(IF(CHOOSE({1,2,3},$A$3:$A$13,$E$3:$E$13,$I$3:$I$13)=$M3,CHOOSE({1,2,3},B$3:B$13,F$3:F$13,J$3:J$13)))
Q3:Q15	Q3	=SUM(IF(CHOOSE({1,2,3},$A$3:$A$13,$E$3:$E$13,$I$3:$I$13)=$M3,CHOOSE({1,2,3},$C$3:$C$13*$B$3:$B$13,$G$3:$G$13*$F$3:$F$13,$K$3:$K$13*J$3:J$13)))/SUM(IF(CHOOSE({1,2,3},$A$3:$A$13,$E$3:$E$13,$I$3:$I$13)=$M3,CHOOSE({1,2,3},B$3:B$13,F$3:F$13,J$3:J$13)))
P4:P15	P4	=SUM(IF(CHOOSE({1,2,3},$A$3:$A$13,$E$3:$E$13,$I$3:$I$13)=M4,CHOOSE({1,2,3},$B$3:$B$13,$F$3:$F$13,$J$3:$J$13)))

Thanks CA_Punit,

It does what I need for Total Quantity and Weighted Average Price. Could you please explain what for the P4 Formula? Also I need to pick the item code from the three sheets to populate COLUMN "M" in. Could you please help me?

 

[CA_Punit]

Ignore the P4 formula. I suppose i forgot to edit it.

Consolidating Item code : It can be consolidated using Pivot table. Please copy in one sheet and use pivot to consolidate. Meanwhile i am trying to find out a formula solution.

 

[gnaga]

Sure I will try your suggestion and let you know. Thanks for your effort and time.