scottlockhart
New Member
- Joined
- Feb 24, 2016
- Messages
- 8
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!
<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>
</tbody>
<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>
</tbody>
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!
| 2 | 100.00% | |||||
| 2 | 100.00% | 0 | Day 0 | 9.07% | ||
| 1 | 100.00% | Day 1 | 10.10% | |||
| 1 | 100.00% | 0 | Day 2 | #DIV/0! | ||
| 3 | 100.00% | Day 3 | #DIV/0! | |||
| 3 | 100.00% | 0 | Day 4 | #DIV/0! | ||
| 4 | 100.00% | Day 5 | #DIV/0! | |||
| 4 | 100.00% | 0 | Day 6 | #DIV/0! | ||
| 18 | 100.00% | Day 7 | #DIV/0! | |||
| 11 | 61.11% | 0 | Day 8 | #DIV/0! | ||
| 1 | 5.56% | 20 | Day 9 | #DIV/0! | ||
| 1 | 5.56% | 68 | Day 10 | #DIV/0! | ||
| 1 | 5.56% | 99 | Day 11 | #DIV/0! | ||
| 1 | 5.56% | 118 | Day 12 | #DIV/0! | ||
| 1 | 5.56% | 178 | Day 13 | #DIV/0! | ||
| 1 | 5.56% | 188 | Day 14 | #DIV/0! | ||
| 1 | 5.56% | 194 | Day 15 | #DIV/0! | ||
| 11 | 100.00% | Day 16 | #DIV/0! | |||
| 3 | 27.27% | 0 | Day 17 | #DIV/0! | ||
| 1 | 9.09% | 1 | Day 18 | #DIV/0! | ||
| 1 | 9.09% | 30 | Day 19 | #DIV/0! | ||
| 1 | 9.09% | 58 | Day 20 | 5.56% | ||
| 1 | 9.09% | 60 | Day 21 | #DIV/0! | ||
| 1 | 9.09% | 91 | Day 22 | #DIV/0! | ||
| 1 | 9.09% | 121 | Day 23 | #DIV/0! | ||
| 1 | 9.09% | 152 | Day 24 | #DIV/0! | ||
| 1 | 9.09% | 183 | Day 25 | #DIV/0! | ||
| 5 | 100.00% | Day 26 | #DIV/0! | |||
| 3 | 60.00% | 0 | Day 27 | #DIV/0! | ||
| 1 | 20.00% | 30 | Day 28 | #DIV/0! | ||
| 1 | 20.00% | 60 | Day 29 | #DIV/0! | ||
| 9 | 100.00% | Day 30 | 14.55% | |||
| 8 | 88.89% | 0 | ||||
| 1 | 11.11% | 1 |
<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>
</tbody>
<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> </tbody> |
<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>
</tbody>
