Neeed Formula to get averges based on multiple conditions (Similar like Pivot summary)

[harinsh]

Hi Team,

I'm looking one typical formula to get the required information this can be done by using Pivot Table but looking in formula how can we integrate this requirement.

Please look into below table and also out put table and I need to get the average of P1, P2 & P3 based on model & location.

S.L NO	Model	Loc	Dept	P1	P2	P3
11111	AB123	A	General Dept	1774	9400	6599
11113	AB123	A	General Dept	2554	4570	5444
11115	AB321	B	General Dept	1455	5177	214
11117	AB321	B	General Dept	2446	1447	2144
11119	AC123	C	General Dept	615	258	2102
11121	AC123	C	General Dept	722	1257	365
11123	AC321	E	General Dept	982	4790	254
11125	AC321	E	General Dept	540	9470	477
11127	AD123	D	General Dept	1712	722	452
11129	AD123	D	General Dept	970	1712	2167
11131	AD321	F	General Dept	0	457	2497
11133	AD321	F	General Dept	7841	4417	256
11135	AD321	H	General Dept	1547	451	247
11137	AF123	H	General Dept	654	25	5

<colgroup><col span="3"><col><col span="3"></colgroup><tbody>
</tbody>

Required output (this was done by using Pivot table)

Row Labels	Average of P1	Average of P2	Average of P3
A	2,164	6,985	6,022
B	1,951	3,312	1,179
C	669	758	1,234
D	1,341	1,217	1,310
E	761	7,130	366
F	3,921	2,437	1,377
H	1,101	238	126
Grand Total	1,701	3,154	1,659

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

Please help with formula and this would help me lot in order to include in macro.

Thanks,

 

[Andrew Poulsom]

What version of Excel do you have? In Excel 2007 and above there is an AVERAGEIF function.

 

[harinsh]

Thanks for your reply.....I'm using Excel 2007 but I tried that function not able to get the output.

Please help me out with this formula.

Thanks,

 

[Andrew Poulsom]

With your first table in the range A1:G15 and your second table in the range I1:L8, in J2 copied down and across:

=AVERAGEIF($C$2:$C$15,$I2,E$2:E$15)

 

[harinsh]

sorry, let me put in different way...actually I did not update the right output in the above.

By using avergeif function I will get it but need to customize little more I would like average based on multiple location (user define location) because I have various locations whereas I need to show it by combining the two or more location averages with help of model of the product like below table.

Row Labels Total Average of P1 - A&B Location Total Average of P2 A&B Location Total Average of P3 A&B Location AB123 2,164.00 6,985.00 6,021.50 AB321 1,950.50 3,312.00 1,179.00 Grand Total 2,057.25 5,148.50 3,600.25 <colgroup><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[Andrew Poulsom]

Just change the formula to:

=AVERAGEIF($B$2:$B$15,$I2,E$2:E$15)