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.
<colgroup><col span="3"><col><col span="3"></colgroup><tbody>
</tbody>
Required output (this was done by using Pivot table)
<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,
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,