Array Formula With Sumproduct for Average

[scottlockhart]

I have two questions. First, I'm using an array formula in my last column (H) to average the percentages in column B, based on the day in column C. (the data is truncated FIY)
Ex: {=AVERAGE(IF$C$5:$C34=E5,$B$5:$B$34))}

My first question is: how do I embed SUMPRODUCT into the formula (replacing AVERAGE I assume) to base the averages from column B off of the corresponding weights from column A?

Second question: My Day 0 array formula is not working properly. I'm assuming it has to do with the "0", but the AVERAGE function shouldn't be picking up blank cells anyway, should it? Based on the data it should be 100+100+100+100+61.11+27.27+60.00+11.11/8 which equals 69.94%, not 9.07%.

I appreciate anyone's help!
[TABLE="width: 448"]
<colgroup><col width="64" style="width:48pt"> <col width="64" style="width:48pt"> <col width="64" span="4" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 0[/TD]
[TD="class: xl65, align: right"]9.07%[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 1[/TD]
[TD="class: xl65, align: right"]10.10%[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 2[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 3[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 4[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 5[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 6[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 7[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="class: xl65, align: right"]61.11%[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 8[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]5.56%[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 9[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]5.56%[/TD]
[TD="align: right"]68[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 10[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]5.56%[/TD]
[TD="align: right"]99[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 11[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]5.56%[/TD]
[TD="align: right"]118[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 12[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]5.56%[/TD]
[TD="align: right"]178[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 13[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]5.56%[/TD]
[TD="align: right"]188[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 14[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]5.56%[/TD]
[TD="align: right"]194[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 15[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 16[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]27.27%[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 17[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]9.09%[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 18[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]9.09%[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 19[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]9.09%[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 20[/TD]
[TD="class: xl65, align: right"]5.56%[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]9.09%[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 21[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]9.09%[/TD]
[TD="align: right"]91[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 22[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]9.09%[/TD]
[TD="align: right"]121[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 23[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]9.09%[/TD]
[TD="align: right"]152[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 24[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]9.09%[/TD]
[TD="align: right"]183[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 25[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 26[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]60.00%[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 27[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]20.00%[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 28[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]20.00%[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 29[/TD]
[TD="class: xl65, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="class: xl65, align: right"]100.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl66"][/TD]
[TD]Day 30[/TD]
[TD="class: xl65, align: right"]14.55%[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="class: xl65, align: right"]88.89%[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]11.11%[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 448"]
<colgroup><col width="64" style="width:48pt"> <col width="64" style="width:48pt"> <col width="64" span="4" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 448"]
<colgroup><col width="64" style="width:48pt"> <col width="64" style="width:48pt"> <col width="64" span="4" style="width:48pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl67, width: 64"][/TD]
[TD="class: xl68, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl65, width: 64, align: right"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[scottlockhart]

What do we have in C? What do we correlated day 0 with the data in A:C?

Column C are days that correspond with days in column F.

Ex: cells C20 and C34 match up with F1, and the corresponding percentages in column B are averaged into column G & H (one is weighted, one is not).

 

[AhoyNC]

See if this is what you want.
If all the days in F will match up with a day in column C we could do away with the IF part of the formula.
Formula in G2 is an array formula and must be entered with CTRL-SHIFT-ENTER.

Also what ver. of Excel are you using?

Excel Workbook
	A	B	C	D	E	F	G	H
1	Weight	Percent	Day			Criteria (Day)	Average	Wt. Average
2	2	100.00%			Day	0	92.22%	79.63%
3	2	100.00%	0		Day	1
4	1	100.00%			Day	2
5	1	100.00%	0		Day	3
6	3	100.00%	5		Day	4
7	3	100.00%	0		Day	5	72.50%	68.57%
8	4	45.00%	5		Day	6
9	4	100.00%	0		Day	7
10	18	100.00%			Day	8
11	11	61.11%	0		Day	9
12	1	5.56%	10		Day	10	5.56%	5.56%
Sheet

 

[Aladin Akyurek]

Column C are days that correspond with days in column F.

Ex: cells C20 and C34 match up with F1, and the corresponding percentages in column B are averaged into column G & H (one is weighted, one is not).

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]

2​

[/td][td]

100.00%​

[/td][td] [/td][td] [/td][td]

Average​

[/td][td]

Wt. Average​

[/td][/tr]


[tr][td]

2​

[/td][td]

2​

[/td][td]

100.00%​

[/td][td]

0​

[/td][td] 0[/td][td]

79.66%​

[/td][td]

227.00%​

[/td][/tr]


[tr][td]

3​

[/td][td]

1​

[/td][td]

100.00%​

[/td][td] [/td][td] 1[/td][td]

10.10%​

[/td][td]

10.10%​

[/td][/tr]


[tr][td]

4​

[/td][td]

1​

[/td][td]

100.00%​

[/td][td]

0​

[/td][td] 2[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

5​

[/td][td]

3​

[/td][td]

100.00%​

[/td][td] [/td][td] 3[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

6​

[/td][td]

3​

[/td][td]

100.00%​

[/td][td]

0​

[/td][td] 4[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

7​

[/td][td]

4​

[/td][td]

100.00%​

[/td][td] [/td][td] 5[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

8​

[/td][td]

4​

[/td][td]

100.00%​

[/td][td]

0​

[/td][td] 6[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

9​

[/td][td]

18​

[/td][td]

100.00%​

[/td][td] [/td][td] 7[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

10​

[/td][td]

11​

[/td][td]

61.11%​

[/td][td]

0​

[/td][td] 8[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

11​

[/td][td]

1​

[/td][td]

5.56%​

[/td][td]

20​

[/td][td] 9[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

12​

[/td][td]

1​

[/td][td]

5.56%​

[/td][td]

68​

[/td][td] 10[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

13​

[/td][td]

1​

[/td][td]

5.56%​

[/td][td]

99​

[/td][td] 11[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

14​

[/td][td]

1​

[/td][td]

5.56%​

[/td][td]

118​

[/td][td] 12[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

15​

[/td][td]

1​

[/td][td]

5.56%​

[/td][td]

178​

[/td][td] 13[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

16​

[/td][td]

1​

[/td][td]

5.56%​

[/td][td]

188​

[/td][td] 14[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

17​

[/td][td]

1​

[/td][td]

5.56%​

[/td][td]

194​

[/td][td] 15[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

18​

[/td][td]

11​

[/td][td]

100.00%​

[/td][td] [/td][td] 16[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

19​

[/td][td]

3​

[/td][td]

27.27%​

[/td][td]

0​

[/td][td] 17[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

20​

[/td][td]

1​

[/td][td]

9.09%​

[/td][td]

1​

[/td][td] 18[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

21​

[/td][td]

1​

[/td][td]

9.09%​

[/td][td]

30​

[/td][td] 19[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

22​

[/td][td]

1​

[/td][td]

9.09%​

[/td][td]

58​

[/td][td] 20[/td][td]

5.56%​

[/td][td]

5.56%​

[/td][/tr]


[tr][td]

23​

[/td][td]

1​

[/td][td]

9.09%​

[/td][td]

60​

[/td][td] 21[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

24​

[/td][td]

1​

[/td][td]

9.09%​

[/td][td]

91​

[/td][td] 22[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

25​

[/td][td]

1​

[/td][td]

9.09%​

[/td][td]

121​

[/td][td] 23[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

26​

[/td][td]

1​

[/td][td]

9.09%​

[/td][td]

152​

[/td][td] 24[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

27​

[/td][td]

1​

[/td][td]

9.09%​

[/td][td]

183​

[/td][td] 25[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

28​

[/td][td]

5​

[/td][td]

100.00%​

[/td][td] [/td][td] 26[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

29​

[/td][td]

3​

[/td][td]

60.00%​

[/td][td]

0​

[/td][td] 27[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

30​

[/td][td]

1​

[/td][td]

20.00%​

[/td][td]

30​

[/td][td] 28[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

31​

[/td][td]

1​

[/td][td]

20.00%​

[/td][td]

60​

[/td][td] 29[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

32​

[/td][td]

9​

[/td][td]

100.00%​

[/td][td] [/td][td] 30[/td][td]

14.55%​

[/td][td]

14.55%​

[/td][/tr]


[tr][td]

33​

[/td][td]

8​

[/td][td]

88.89%​

[/td][td]

0​

[/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

34​

[/td][td]

1​

[/td][td]

11.11%​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

In G2 just enter and copy down:

=IF(ISNUMBER(MATCH(F2,$C$1:$C$34,0)),AVERAGEIFS($B$1:$B$34,$C$1:$C$34,$F2),"")

In H2 just enter and copy down:

=IF(ISNUMBER($G2),SUMPRODUCT(--($C$1:$C$34=$F2),$B$1:$B$34,$A$1:$A$34)/SUMIFS($A$1:$A$34,$C$1:$C$34,$F2),"")

Is the foregoing what you are after?