Average of non-zero values in a dynamic range

[VKat13]

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!

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>

 

[Aladin Akyurek]

All figures from week 13- week 18

Why week 13 to week 18? Are these values given somewhere (in criteria cells outside the data are) so that the formula to be designed can pick them up?

 

[VKat13]

The figure in week 13 is the first non-zero value from where I want the 13 week rolling average to start the calculation.
The figure 36,090 is the current week which should be excluded in the average as I would like to calculate the current week vs. the 13 wk average later on.

 

[Aladin Akyurek]

The figure in week 13 is the first non-zero value from where I want the 13 week rolling average to start the calculation.
The figure 36,090 is the current week which should be excluded in the average as I would like to calculate the current week vs. the 13 wk average later on.

B6, just enter:

=MATCH(B4,A:A,0)+1

where B4 houses the week value corresponding to the first >0 value.

For average of the next 13 >0 values:

Control+shift+enter, not just enter:

=AVERAGE(IF(ROW(INDEX(B:B,B6):B25)<=
  SMALL(IF(ISNUMBER(1/INDEX(B:B,B6):B25),
  ROW(INDEX(B:B,B6):B25)),MIN(COUNT(INDEX(B:B,B6):B25),13)),
  IF(ISNUMBER(1/INDEX(B:B,B6):B25),INDEX(B:B,B6):B25)))

 

[VKat13]

GENIUS!! Works perfectly!
Very, very much appreciate the time you took to help and if I could give you hug I would!!
Thanks again.

 

[Aladin Akyurek]

GENIUS!! Works perfectly!
Very, very much appreciate the time you took to help and if I could give you hug I would!!
Thanks again.

You are welcome. Thanks for the kind feedback.