Hi everyone,
I'm trying to calculate the prior rolling 13 week average (excluding all zero values) and have tried using a nested AVERAGEIFS, OFFSET, INDEX and MATCH formula which seems to work for the below example, but doesn't when I have a non-zero value in Week 4.
My formula is a little convoluted, and my data is as per below.
=AVERAGEIFS(D11:OFFSET(INDEX(D$11:D$28,MATCH(D5,D$11:D$28,0)),16,0),D11:OFFSET(INDEX(D$11:D$28,MATCH(D5,D$11:D$28,0)),16,0),">0",D11:OFFSET(INDEX(D$11:D$28,MATCH(D5,D$11:D$28,0)),16,0),"<>"&D5)
I'm pretty sure I need to use an INDIRECT function instead given that my range will continuously change, but I'm not sure how to write it.
Apologies if I don't make sense. I've been racking my brain for the last few hours trying to get this to work.
Thanks in advance for your help!
<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>
I'm trying to calculate the prior rolling 13 week average (excluding all zero values) and have tried using a nested AVERAGEIFS, OFFSET, INDEX and MATCH formula which seems to work for the below example, but doesn't when I have a non-zero value in Week 4.
My formula is a little convoluted, and my data is as per below.
=AVERAGEIFS(D11:OFFSET(INDEX(D$11:D$28,MATCH(D5,D$11:D$28,0)),16,0),D11:OFFSET(INDEX(D$11:D$28,MATCH(D5,D$11:D$28,0)),16,0),">0",D11:OFFSET(INDEX(D$11:D$28,MATCH(D5,D$11:D$28,0)),16,0),"<>"&D5)
I'm pretty sure I need to use an INDIRECT function instead given that my range will continuously change, but I'm not sure how to write it.
Apologies if I don't make sense. I've been racking my brain for the last few hours trying to get this to work.
Thanks in advance for your help!
Week # | Bananas |
Week 1 | 0 |
Week 2 | 36,090 |
Week 3 | 0 |
Week 4 | 0 |
Week 5 | 0 |
Week 6 | 30,261 |
Week 7 | 21,505 |
Week 8 | 26,489 |
Week 9 | 20,909 |
Week 10 | 23,863 |
Week 11 | 23,491 |
Week 12 | 21,994 |
Week 13 | 22,687 |
Week 14 | 23,961 |
Week 15 | 22,624 |
Week 16 | 22,785 |
Week 17 | 30,823 |
Week 18 | 30,324 |
<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>