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.

[TABLE="width: 471"]
<colgroup><col span="3"><col><col span="3"></colgroup><tbody>[TR]
[TD] S.L NO
[/TD]
[TD] Model
[/TD]
[TD]Loc
[/TD]
[TD]Dept
[/TD]
[TD]P1
[/TD]
[TD]P2
[/TD]
[TD]P3
[/TD]
[/TR]
[TR]
[TD="align: right"]11111
[/TD]
[TD] AB123
[/TD]
[TD]A
[/TD]
[TD]General Dept[/TD]
[TD="align: right"]1774[/TD]
[TD="align: right"]9400[/TD]
[TD="align: right"]6599[/TD]
[/TR]
[TR]
[TD="align: right"]11113[/TD]
[TD] AB123
[/TD]
[TD]A[/TD]
[TD]General Dept[/TD]
[TD="align: right"]2554[/TD]
[TD="align: right"]4570[/TD]
[TD="align: right"]5444
[/TD]
[/TR]
[TR]
[TD="align: right"] 11115
[/TD]
[TD] AB321
[/TD]
[TD]B[/TD]
[TD]General Dept[/TD]
[TD="align: right"]1455[/TD]
[TD="align: right"]5177[/TD]
[TD="align: right"]214[/TD]
[/TR]
[TR]
[TD="align: right"]11117[/TD]
[TD] AB321
[/TD]
[TD]B[/TD]
[TD]General Dept[/TD]
[TD="align: right"]2446[/TD]
[TD="align: right"]1447[/TD]
[TD="align: right"]2144[/TD]
[/TR]
[TR]
[TD="align: right"]11119[/TD]
[TD] AC123
[/TD]
[TD]C[/TD]
[TD]General Dept[/TD]
[TD="align: right"]615[/TD]
[TD="align: right"]258[/TD]
[TD="align: right"]2102[/TD]
[/TR]
[TR]
[TD="align: right"]11121[/TD]
[TD] AC123
[/TD]
[TD]C[/TD]
[TD]General Dept[/TD]
[TD="align: right"]722[/TD]
[TD="align: right"]1257[/TD]
[TD="align: right"]365
[/TD]
[/TR]
[TR]
[TD="align: right"]11123[/TD]
[TD] AC321
[/TD]
[TD]E[/TD]
[TD]General Dept[/TD]
[TD="align: right"]982[/TD]
[TD="align: right"]4790[/TD]
[TD="align: right"]254[/TD]
[/TR]
[TR]
[TD="align: right"]11125[/TD]
[TD] AC321
[/TD]
[TD]E[/TD]
[TD]General Dept[/TD]
[TD="align: right"]540[/TD]
[TD="align: right"]9470[/TD]
[TD="align: right"]477[/TD]
[/TR]
[TR]
[TD="align: right"]11127[/TD]
[TD] AD123
[/TD]
[TD]D[/TD]
[TD]General Dept[/TD]
[TD="align: right"]1712[/TD]
[TD="align: right"]722[/TD]
[TD="align: right"]452[/TD]
[/TR]
[TR]
[TD="align: right"]11129[/TD]
[TD] AD123
[/TD]
[TD]D[/TD]
[TD]General Dept[/TD]
[TD="align: right"]970[/TD]
[TD="align: right"]1712[/TD]
[TD="align: right"]2167[/TD]
[/TR]
[TR]
[TD="align: right"]11131[/TD]
[TD] AD321
[/TD]
[TD]F[/TD]
[TD]General Dept[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]457[/TD]
[TD="align: right"]2497[/TD]
[/TR]
[TR]
[TD="align: right"]11133[/TD]
[TD] AD321
[/TD]
[TD]F[/TD]
[TD]General Dept[/TD]
[TD="align: right"]7841[/TD]
[TD="align: right"]4417[/TD]
[TD="align: right"]256
[/TD]
[/TR]
[TR]
[TD="align: right"]11135[/TD]
[TD] AD321
[/TD]
[TD]H[/TD]
[TD]General Dept[/TD]
[TD="align: right"]1547[/TD]
[TD="align: right"]451[/TD]
[TD="align: right"]247[/TD]
[/TR]
[TR]
[TD="align: right"]11137
[/TD]
[TD] AF123
[/TD]
[TD]H[/TD]
[TD]General Dept[/TD]
[TD="align: right"]654[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
</tbody>[/TABLE]

Required output (this was done by using Pivot table)

[TABLE="width: 370"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Row Labels
[/TD]
[TD]Average of P1[/TD]
[TD]Average of P2[/TD]
[TD]Average of P3[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD] 2,164[/TD]
[TD] 6,985[/TD]
[TD] 6,022[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD] 1,951[/TD]
[TD] 3,312[/TD]
[TD] 1,179
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD] 669[/TD]
[TD] 758[/TD]
[TD] 1,234
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD] 1,341[/TD]
[TD] 1,217[/TD]
[TD] 1,310
[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD] 761[/TD]
[TD] 7,130[/TD]
[TD] 366[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD] 3,921[/TD]
[TD] 2,437[/TD]
[TD] 1,377[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD] 1,101[/TD]
[TD] 238[/TD]
[TD] 126[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD] 1,701[/TD]
[TD] 3,154[/TD]
[TD] 1,659[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 370"]
<tbody>[TR]
[TD][TABLE="width: 405"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Total Average of P1 - A&B Location[/TD]
[TD]Total Average of P2 A&B Location[/TD]
[TD]Total Average of P3 A&B Location[/TD]
[/TR]
[TR]
[TD]AB123[/TD]
[TD] 2,164.00[/TD]
[TD] 6,985.00[/TD]
[TD] 6,021.50[/TD]
[/TR]
[TR]
[TD]AB321[/TD]
[TD] 1,950.50[/TD]
[TD] 3,312.00[/TD]
[TD] 1,179.00[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD] 2,057.25[/TD]
[TD] 5,148.50[/TD]
[TD] 3,600.25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

Just change the formula to:

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