Array Formula With Sumproduct for Average

scottlockhart · Feb 25, 2016

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]

Aladin Akyurek · Feb 25, 2016

It's hard to interpret the exhibit. What is the range? What is input and what is output in this data exhibit?

AhoyNC · Feb 25, 2016

Is this what you are trying to do?
Not sure how your columns line up so you will need to change ranges to match your data.
I also assume column C in the example is numeric.
In your example shouldn't the last number be .8889 and not .1111?

Copy formulas down as needed.

Excel Workbook

A

B

C

D

E

F

G

H

1

2

100.00%

Average

Wt. Average

2

100.00%

0

Day 0

79.66%

75.58%

3

1

100.00%

Day 1

10.10%

4

1

100.00%

0

Day 2

#DIV/0!

5

3

100.00%

Day 3

#DIV/0!

6

3

100.00%

0

Day 4

#DIV/0!

7

4

100.00%

Day 5

#DIV/0!

8

4

100.00%

0

Day 6

#DIV/0!

9

18

100.00%

Day 7

#DIV/0!

10

11

61.11%

0

Day 8

#DIV/0!

11

1

5.56%

20

Day 9

#DIV/0!

12

1

5.56%

68

Day 10

#DIV/0!

13

1

5.56%

99

Day 11

#DIV/0!

14

1

5.56%

118

Day 12

#DIV/0!

15

1

5.56%

178

Day 13

#DIV/0!

16

1

5.56%

188

Day 14

#DIV/0!

17

1

5.56%

194

Day 15

#DIV/0!

18

11

100.00%

Day 16

#DIV/0!

19

3

27.27%

0

Day 17

#DIV/0!

20

1

9.09%

1

Day 18

#DIV/0!

21

1

9.09%

30

Day 19

#DIV/0!

22

1

9.09%

58

Day 20

5.56%

23

1

9.09%

60

Day 21

#DIV/0!

24

1

9.09%

91

Day 22

#DIV/0!

25

1

9.09%

121

Day 23

#DIV/0!

26

1

9.09%

152

Day 24

#DIV/0!

27

1

9.09%

183

Day 25

#DIV/0!

28

5

100.00%

Day 26

#DIV/0!

29

3

60.00%

0

Day 27

#DIV/0!

30

1

20.00%

30

Day 28

#DIV/0!

31

1

20.00%

60

Day 29

#DIV/0!

32

9

100.00%

Day 30

14.55%

33

8

88.89%

0

34

1

11.11%

1

Sheet

scottlockhart · Feb 25, 2016

You are correct that the last number is .8889 and not .1111, that was my mistake.

Sorry if this is confusing it's difficult without uploading a spreadsheet! I will test out these formulas and let you know if I have any success. Thanks!

AhoyNC said:
Is this what you are trying to do?
Not sure how your columns line up so you will need to change ranges to match your data.
I also assume column C in the example is numeric.
In your example shouldn't the last number be .8889 and not .1111?

Copy formulas down as needed.

* A B C D E F G H
* * * *
* * Day 0
* * * Day 1
* * Day 2 #DIV/0! #DIV/0!
* * * Day 3 #DIV/0! #DIV/0!
* * Day 4 #DIV/0! #DIV/0!
* * * Day 5 #DIV/0! #DIV/0!
* * Day 6 #DIV/0! #DIV/0!
* * * Day 7 #DIV/0! #DIV/0!
* * Day 8 #DIV/0! #DIV/0!
* * Day 9 #DIV/0! #DIV/0!
* * Day 10 #DIV/0! #DIV/0!
* * Day 11 #DIV/0! #DIV/0!
* * Day 12 #DIV/0! #DIV/0!
* * Day 13 #DIV/0! #DIV/0!
* * Day 14 #DIV/0! #DIV/0!
* * Day 15 #DIV/0! #DIV/0!
* * * Day 16 #DIV/0! #DIV/0!
* * Day 17 #DIV/0! #DIV/0!
* * Day 18 #DIV/0! #DIV/0!
* * Day 19 #DIV/0! #DIV/0!
* * Day 20
* * Day 21 #DIV/0! #DIV/0!
* * Day 22 #DIV/0! #DIV/0!
* * Day 23 #DIV/0! #DIV/0!
* * Day 24 #DIV/0! #DIV/0!
* * Day 25 #DIV/0! #DIV/0!
* * * Day 26 #DIV/0! #DIV/0!
* * Day 27 #DIV/0! #DIV/0!
* * Day 28 #DIV/0! #DIV/0!
* * Day 29 #DIV/0! #DIV/0!
* * * Day 30
* * * * *
* * * * *

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:44px;"><col style="width:33px;"><col style="width:64px;"><col style="width:84px;"><col style="width:107px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #c0c0c0, align: center"]Average[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Wt. Average[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #ffff00, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]79.66%[/TD]
[TD="align: right"]75.58%[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100.00%[/TD]

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

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #ffff00, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #ffff00, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #ffff00, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]11[/TD]
[TD="bgcolor: #ffff00, align: right"]61.11%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]68[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]99[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]118[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]178[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]188[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]194[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #ffff00, align: right"]27.27%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]58[/TD]

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

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]60[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]91[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]121[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]152[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]183[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #ffff00, align: right"]60.00%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]60[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]100.00%[/TD]

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

[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="align: right"]8[/TD]
[TD="bgcolor: #ffff00, align: right"]88.89%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11.11%[/TD]
[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula
G2 {=AVERAGE(IF($C$1:$C$34<>"",IF($C$1:$C$34=MID(F2,FIND("y",F2)+1,5)*1,$B$1:$B$34)))}
H2 =SUMPRODUCT(--($C$1:$C$34=MID(F2,FIND("y",F2)+1,5)*1),--($C$1:$C$34<>""),$A$1:$A$34,$B$1:$B$34)/SUMPRODUCT(--($C$1:$C$34=MID(F2,FIND("y",F2)+1,5)*1),--($C$1:$C$34<>""),$A$1:$A$34)

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

scottlockhart · Feb 25, 2016

I'm getting the #Value! error but I probably just need to go back and see what I'm putting in wrong. However, is there a simpler way by chance to write the formula if I remove "day" from column F and relocate to column E and just have numbers only in column F? It may help by being able to remove the MID and FIND functions. Thanks!

Aladin Akyurek · Feb 25, 2016

scottlockhart said:
I'm getting the #Value! error but I probably just need to go back and see what I'm putting in wrong. However, is there a simpler way by chance to write the formula if I remove "day" from column F and relocate to column E and just have numbers only in column F? It may help by being able to remove the MID and FIND functions. Thanks!

Try to repost the sample. Indeed, "day" hinders rather than eases...

scottlockhart · Feb 25, 2016

The table AhoyNC created shows it best. I put "day" in column E for the first few to show what I mean. To find weighted average with SUMPRODUCT with just the number in cell F is probably a much simpler equation, and I can easily structure the spreadsheet in that way.

A	B	C	D	E	F	G	H

Day	0
	Day	1
Day	2	#DIV/0!	#DIV/0!
		Day 3	#DIV/0!	#DIV/0!
	Day 4	#DIV/0!	#DIV/0!
		Day 5	#DIV/0!	#DIV/0!
	Day 6	#DIV/0!	#DIV/0!
		Day 7	#DIV/0!	#DIV/0!
	Day 8	#DIV/0!	#DIV/0!
	Day 9	#DIV/0!	#DIV/0!
	Day 10	#DIV/0!	#DIV/0!
	Day 11	#DIV/0!	#DIV/0!
	Day 12	#DIV/0!	#DIV/0!
	Day 13	#DIV/0!	#DIV/0!
	Day 14	#DIV/0!	#DIV/0!
	Day 15	#DIV/0!	#DIV/0!
		Day 16	#DIV/0!	#DIV/0!
	Day 17	#DIV/0!	#DIV/0!
	Day 18	#DIV/0!	#DIV/0!
	Day 19	#DIV/0!	#DIV/0!
	Day 20
	Day 21	#DIV/0!	#DIV/0!
	Day 22	#DIV/0!	#DIV/0!
	Day 23	#DIV/0!	#DIV/0!
	Day 24	#DIV/0!	#DIV/0!
	Day 25	#DIV/0!	#DIV/0!
		Day 26	#DIV/0!	#DIV/0!
	Day 27	#DIV/0!	#DIV/0!
	Day 28	#DIV/0!	#DIV/0!
	Day 29	#DIV/0!	#DIV/0!
		Day 30

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #C0C0C0, align: center"]Average[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Wt. Average[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #FFFF00, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]79.66%[/TD]
[TD="align: right"]75.58%[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100.00%[/TD]

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

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #FFFF00, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #FFFF00, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #FFFF00, align: right"]100.00%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: right"]11[/TD]
[TD="bgcolor: #FFFF00, align: right"]61.11%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]20[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]68[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]99[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]118[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]178[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]188[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.56%[/TD]
[TD="align: right"]194[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #CACACA, align: center"]19[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #FFFF00, align: right"]27.27%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]21[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: #CACACA, align: center"]22[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]58[/TD]

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

[TD="bgcolor: #CACACA, align: center"]23[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]60[/TD]

[TD="bgcolor: #CACACA, align: center"]24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]91[/TD]

[TD="bgcolor: #CACACA, align: center"]25[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]121[/TD]

[TD="bgcolor: #CACACA, align: center"]26[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]152[/TD]

[TD="bgcolor: #CACACA, align: center"]27[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.09%[/TD]
[TD="align: right"]183[/TD]

[TD="bgcolor: #CACACA, align: center"]28[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]100.00%[/TD]

[TD="bgcolor: #CACACA, align: center"]29[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #FFFF00, align: right"]60.00%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]30[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: #CACACA, align: center"]31[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]60[/TD]

[TD="bgcolor: #CACACA, align: center"]32[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]100.00%[/TD]

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

[TD="bgcolor: #CACACA, align: center"]33[/TD]
[TD="align: right"]8[/TD]
[TD="bgcolor: #FFFF00, align: right"]88.89%[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]34[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11.11%[/TD]
[TD="align: right"]1[/TD]

</tbody>

Aladin Akyurek · Feb 25, 2016

What does mean 2 in A1? In F it seems we have day numbers, right? In G we seem to have conditional day averages of B, right? It seems you need daily weighted averages. If so, where are the weights?

scottlockhart · Feb 25, 2016

Aladin Akyurek said:
What does mean 2 in A1? In F it seems we have day numbers, right? In G we seem to have conditional day averages of B, right? It seems you need daily weighted averages. If so, where are the weights?

The weights are column A.

Aladin Akyurek · Feb 25, 2016

scottlockhart said:
The weights are column A.

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

Array Formula With Sumproduct for Average

scottlockhart

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

AhoyNC

Well-known Member

scottlockhart

New Member

scottlockhart

New Member

Aladin Akyurek

MrExcel MVP

scottlockhart

New Member

Aladin Akyurek

MrExcel MVP

scottlockhart

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Array Formula With Sumproduct for Average

New Member

Excel Facts

MrExcel MVP

Well-known Member

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock