Hi
Nishant94 has very kindly pulled the following formula together for me (an awesome feat in itself) and I was wondering if someone could assist taking it to the next level.
<thead style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</thead><tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</tbody>
Worksheet Formulas
<thead style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</thead><tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</tbody>
Nishant94 has very kindly pulled the following formula together for me (an awesome feat in itself) and I was wondering if someone could assist taking it to the next level.
Would it be possible to revise the formula below so that it can average over significantly longer periods of time? For example, 120 days or even 365 days rather than just 5 days?
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Day 1 | Day 2 | Day 3 | Day 4 | Day 5 | Day 6 | Day 7 | Day 8 | Day 9 | Day 10 | Day 11 | Day 12 | Day 13 | Day 14 | Day 15 | Day 16 | Day 17 |
2 | 3 | 7 | 13 | 12 | 1 | 0 | 0 | 6 | 8 | 12 | 12 | 9 | 0 | 0 | 8 | 8 | 8 |
3 | Average-> | 7.2 | 7.2 | 7.2 | 7.8 | 8 | 7.8 | 7.8 | 9.4 | 9.4 | 9.4 | 9.8 | 9.8 | 9 |
<thead style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</thead><tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</tbody>
Worksheet Formulas
Cell | Formula |
---|---|
E3 | =SUMPRODUCT(($A2:E2)*(ISNUMBER(MATCH(COLUMN($A3:E3)-COLUMN($A3)+1,AGGREGATE(14,6,(COLUMN($A3:E3)-COLUMN($A3)+1)^(($A2:E2)^0),{5,4,3,2,1}),0))))/5 |
<thead style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</thead><tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</tbody>