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]

What would be the expected result for the sample you posted?

 

[AhoyNC]

This should give you the last 13 weeks and not include a zero value.

Excel Workbook
	A	B	C	D	E
1	Week #	Bananas
2	Week 1	0		Last 13 Weeks Average
3	Week 2	36,090		24,747.38
4	Week 3	0
5	Week 4	0
6	Week 5	0
7	Week 6	30,261
8	Week 7	21,505
9	Week 8	26,489
10	Week 9	20,909
11	Week 10	23,863
12	Week 11	23,491
13	Week 12	21,994
14	Week 13	22,687
15	Week 14	23,961
16	Week 15	22,624
17	Week 16	22,785
18	Week 17	30,823
19	Week 18	30,324
Sheet

 

[VKat13]

What would be the expected result for the sample you posted?

the expected result is as per AhoyNC stated, 24,747 however, as my offset is set to look for non-zero values in the next 16 rows, it picks up more than the 13 weeks when I have data post week 2's value

EG. If I have the value 32,111 against week 5, the formula re-calculates and gives me the result 25,273 instead of 24,885 which would be the average of the next 13 weeks.

Sorry, and my MATCH figure is the 36,090 from Week 2 (as the first non-zero value)

 

[Aladin Akyurek]

the expected result is as per AhoyNC stated, 24,747 however, as my offset is set to look for non-zero values in the next 16 rows, it picks up more than the 13 weeks when I have data post week 2's value

EG. If I have the value 32,111 against week 5, the formula re-calculates and gives me the result 25,273 instead of 24,885 which would be the average of the next 13 weeks.

Sorry, and my MATCH figure is the 36,090 from Week 2 (as the first non-zero value)

If you want to have the last 13 weeks and the data is in A:B, the following picks out the > 0 values from that window...

=AVERAGEIF(OFFSET(INDEX(B:B,MATCH(REPT("z",255),A:A)),0,0,-13),">0")

where column A houses week numbers (text) and column B corresponding numeric data.

Doesn't this satisfy your requirement or one based on B like the suggestion by AhoyNC?

 

[VKat13]

Thanks for the help guys but neither formulae seems to be working. I've changed the variables in both to match my spreadsheet but the figures don't seem to be calculating correctly when new data is entered (into the "0" cells).

I need the formula to only pick up the next 13 non-zero values after the first non-zero value is found (which would be the cell ref C5 - which has already picked out via INDEX/MATCH what the first non-zero value is effectively making it the "current week" of sales.

	A	B	C	D	E	F
1		Bananas (unit sales)			Bananas (unit sales)
2	Avg. Rolling 13 weeks	24,747		Avg. Rolling 13 weeks	25,273
3	Variance vs. Avg %	45.8%		Variance vs. Avg %	42.8%
4	Current Week Date	Week 2		Current Week Date	Week 2
5	Current Week Sales	36090		Current Week Sales	36090
6
7	Week	Bananas (unit sales)		Week	Bananas (unit sales)
8	Week 1	0		Week 1	0
9	Week 2	36090		Week 2	36090
10	Week 3	0		Week 3	0
11	Week 4	0		Week 4	0
12	Week 5	0		Week 5	32111	13 Week Average = 24,885
13	Week 6	30261		Week 6	30261
14	Week 7	21505		Week 7	21505
15	Week 8	26489		Week 8	26489
16	Week 9	20909		Week 9	20909
17	Week 10	23863		Week 10	23863
18	Week 11	23491		Week 11	23491
19	Week 12	21994		Week 12	21994
20	Week 13	22687		Week 13	22687
21	Week 14	23961		Week 14	23961
22	Week 15	22624		Week 15	22624
23	Week 16	22785		Week 16	22785
24	Week 17	30823		Week 17	30823
25	Week 18	30324		Week 18	30324

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[Aladin Akyurek]

Given:

0

3

0

0

5

7

<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>
</TBODY>

a) Average of the last 3 cells where 0's are excluded would be the average of 7, 5, and 0 where 0 excluded: 6.

b) Average of the last 3 non-zero cells (where 0's are excluded) would be the average of 7, 5, 0, 0, and 3 where 0 excluded: 5.

Which do you have in mind?

 

[VKat13]

Neither.
Using your numbers above, 3 would be excluded as it would count as the current week and should not be included in a prior rolling average. The formula should then move to the next "non-zero" value, in this case 5 and start the rolling average from there as opposed to looking for the last 3 values.

Apologies if I'm confusing you -what makes sense in my head doesn't always make sense in others.

I've seen it done in my previous role but using indirect and tables.

 

[Aladin Akyurek]

Neither.
Using your numbers above, 3 would be excluded as it would count as the current week and should not be included in a prior rolling average. The formula should then move to the next "non-zero" value, in this case 5 and start the rolling average from there as opposed to looking for the last 3 values.

Apologies if I'm confusing you -what makes sense in my head doesn't always make sense in others.

I've seen it done in my previous role but using indirect and tables.

Thanks for the help guys but neither formulae seems to be working. I've changed the variables in both to match my spreadsheet but the figures don't seem to be calculating correctly when new data is entered (into the "0" cells).

I need the formula to only pick up the next 13 non-zero values after the first non-zero value is found (which would be the cell ref C5 - which has already picked out via INDEX/MATCH what the first non-zero value is effectively making it the "current week" of sales.

	A	B	C	D	E	F
1		Bananas (unit sales)			Bananas (unit sales)
2	Avg. Rolling 13 weeks	24,747		Avg. Rolling 13 weeks	25,273
3	Variance vs. Avg %	45.8%		Variance vs. Avg %	42.8%
4	Current Week Date	Week 2		Current Week Date	Week 2
5	Current Week Sales	36090		Current Week Sales	36090
6
7	Week	Bananas (unit sales)		Week	Bananas (unit sales)
8	Week 1	0		Week 1	0
9	Week 2	36090		Week 2	36090
10	Week 3	0		Week 3	0
11	Week 4	0		Week 4	0
12	Week 5	0		Week 5	32111	13 Week Average = 24,885
13	Week 6	30261		Week 6	30261
14	Week 7	21505		Week 7	21505
15	Week 8	26489		Week 8	26489
16	Week 9	20909		Week 9	20909
17	Week 10	23863		Week 10	23863
18	Week 11	23491		Week 11	23491
19	Week 12	21994		Week 12	21994
20	Week 13	22687		Week 13	22687
21	Week 14	23961		Week 14	23961
22	Week 15	22624		Week 15	22624
23	Week 16	22785		Week 16	22785
24	Week 17	30823		Week 17	30823
25	Week 18	30324		Week 18	30324

<tbody>
</tbody>

<tbody>
</tbody>

Ok, back to your longer exhibit.

Which numbers did you include from C to obtain the average of 24,747? I get a different figure, hence the question...

 

[VKat13]

All figures from week 13- week 18