Weighted Average calc for multiple sheets

[walkingcow]

I'm trying to calculate a weighted average for 1) the team and 2) for individuals across multiple sheets. The multiple sheet represents an individuals (see below and the data it contains) and for the team summary it is found in sheet 1. In row 8 below on sheet 1, I'm trying to calulate the weighted average by quarter ie Q1, Q2, Q3, Q4 for the entire team (Z1, Z2, Z3, Z4) and for the year (Z5). the unique identifier is the combination of year and quarter. The weight is simply the number of deals done. What formula or set of formula can I use for across multiple sheets to calculated the weighted average? Thx

By changing the 2016 number on sheet 1, all the numbers should update.

Sheet 1 – Summary sheet
Current Year: 2016[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Annual Avg[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name of person 1[/TD]
[TD]75%[/TD]
[TD]85%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name of person 2[/TD]
[TD]x1[/TD]
[TD]x2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name of person 3[/TD]
[TD]y1[/TD]
[TD]y2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD].[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD].[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD].[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Team Weighted Avg[/TD]
[TD]Z1[/TD]
[TD]Z2[/TD]
[TD]Z3[/TD]
[TD]Z4[/TD]
[TD]Z5[/TD]
[/TR]
</tbody>[/TABLE]

---

Sheet 2 – Name of Person 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]9[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Deal[/TD]
[TD]Year[/TD]
[TD]Date[/TD]
[TD]Qtr[/TD]
[TD]Percentage[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1[/TD]
[TD]2016[/TD]
[TD]Feb 1[/TD]
[TD]Q1[/TD]
[TD]70%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[TD]2016[/TD]
[TD]Mar 1[/TD]
[TD]Q1[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]3[/TD]
[TD]2016[/TD]
[TD]Apr 1[/TD]
[TD]Q2[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]4[/TD]
[TD]2016[/TD]
[TD]May 1[/TD]
[TD]Q2[/TD]
[TD]90%[/TD]
[/TR]
</tbody>[/TABLE]

---

Sheet 3 – Name of Person 2
Repeat of the above (sheet 1) in terms of how data is organized

 

[Eric W]

Welcome to the forum.

With copious use of the INDIRECT function, it can be done. For example, with the sheets defined like this:

	A	B	C	D	E
Deal	Year	Date	Qtr	Percentage
Q1
Q1
Q2
Q2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1-Feb[/TD]

[TD="align: right"]70.00%[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1-Mar[/TD]

[TD="align: right"]80.00%[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1-Apr[/TD]

[TD="align: right"]80.00%[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1-May[/TD]

[TD="align: right"]90.00%[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Amy​

	A	B	C	D	E
Deal	Year	Date	Qtr	Percentage
Q1
Q1
Q2
Q2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1-Feb[/TD]

[TD="align: right"]71.00%[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1-Mar[/TD]

[TD="align: right"]82.00%[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1-Apr[/TD]

[TD="align: right"]65.00%[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1-May[/TD]

[TD="align: right"]92.00%[/TD]

</tbody>

Bob​

	A	B	C	D	E	F
Current Year:
Q1	Q2	Q3	Q4	Annual Avg
Amy
Bob

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]77%[/TD]
[TD="align: right"]86%[/TD]

[TD="align: right"]83%[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]76%[/TD]
[TD="align: right"]74%[/TD]

[TD="align: right"]75%[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=IFERROR(SUMPRODUCT((INDIRECT($A4&"!B11:B100")=$B$1)*(INDIRECT($A4&"!D11:D100")=B$3)*INDIRECT($A4&"!A11:A100")*INDIRECT($A4&"!E11:E100"))/SUMIFS(INDIRECT($A4&"!A11:A100"),INDIRECT($A4&"!B11:B100"),$B$1,INDIRECT($A4&"!D11:D100"),B$3),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]=IFERROR(SUMPRODUCT((INDIRECT($A4&"!B11:B100")=$B$1)*INDIRECT($A4&"!A11:A100")*INDIRECT($A4&"!E11:E100"))/SUMIFS(INDIRECT($A4&"!A11:A100"),INDIRECT($A4&"!B11:B100"),$B$1),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Enter the B4 formula as shown, then you can copy it to C4:E4. Then copy it down the columns as far as needed. Then enter the F4 formula and paste it down the column as far as needed. The INDIRECT uses the name in column A to look up the data on a sheet with the same name. Note that the formulas use a defined range of A11:A100, you should make sure that you put in the maximum row of your data. It's not a good idea to search the entire column, as that would slow down your sheet considerably.

Let me know if this works for you.