Average of non-zero values in a dynamic range

VKat13

New Member
Joined
Jan 14, 2014
Messages
19
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 10
Week 236,090
Week 30
Week 40
Week 50
Week 630,261
Week 721,505
Week 826,489
Week 920,909
Week 1023,863
Week 1123,491
Week 1221,994
Week 1322,687
Week 1423,961
Week 1522,624
Week 1622,785
Week 1730,823
Week 1830,324

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

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
This should give you the last 13 weeks and not include a zero value.
Excel Workbook
ABCDE
1Week #Bananas
2Week 10Last 13 Weeks Average
3Week 236,09024,747.38
4Week 30
5Week 40
6Week 50
7Week 630,261
8Week 721,505
9Week 826,489
10Week 920,909
11Week 1023,863
12Week 1123,491
13Week 1221,994
14Week 1322,687
15Week 1423,961
16Week 1522,624
17Week 1622,785
18Week 1730,823
19Week 1830,324
Sheet
 

VKat13

New Member
Joined
Jan 14, 2014
Messages
19
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)
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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?
 
Last edited:

VKat13

New Member
Joined
Jan 14, 2014
Messages
19
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.
ABCDEF
1 Bananas (unit sales) Bananas (unit sales)
2Avg. Rolling 13 weeks 24,747 Avg. Rolling 13 weeks 25,273
3Variance vs. Avg %45.8% Variance vs. Avg %42.8%
4Current Week DateWeek 2 Current Week DateWeek 2
5Current Week Sales36090 Current Week Sales36090
6
7WeekBananas (unit sales) WeekBananas (unit sales)
8Week 10 Week 10
9Week 236090 Week 236090
10Week 30 Week 30
11Week 40 Week 40
12Week 50 Week 53211113 Week Average = 24,885
13Week 630261 Week 630261
14Week 721505 Week 721505
15Week 826489 Week 826489
16Week 920909 Week 920909
17Week 1023863 Week 1023863
18Week 1123491 Week 1123491
19Week 1221994 Week 1221994
20Week 1322687 Week 1322687
21Week 1423961 Week 1423961
22Week 1522624 Week 1522624
23Week 1622785 Week 1622785
24Week 1730823 Week 1730823
25Week 1830324 Week 1830324

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


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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

New Member
Joined
Jan 14, 2014
Messages
19
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
ABCDEF
1Bananas (unit sales)Bananas (unit sales)
2Avg. Rolling 13 weeks 24,747Avg. Rolling 13 weeks 25,273
3Variance vs. Avg %45.8%Variance vs. Avg %42.8%
4Current Week DateWeek 2Current Week DateWeek 2
5Current Week Sales36090Current Week Sales36090
6
7WeekBananas (unit sales)WeekBananas (unit sales)
8Week 10Week 10
9Week 236090Week 236090
10Week 30Week 30
11Week 40Week 40
12Week 50Week 53211113 Week Average = 24,885
13Week 630261Week 630261
14Week 721505Week 721505
15Week 826489Week 826489
16Week 920909Week 920909
17Week 1023863Week 1023863
18Week 1123491Week 1123491
19Week 1221994Week 1221994
20Week 1322687Week 1322687
21Week 1423961Week 1423961
22Week 1522624Week 1522624
23Week 1622785Week 1622785
24Week 1730823Week 1730823
25Week 1830324Week 1830324

<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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,815
Messages
5,598,259
Members
414,220
Latest member
Nations

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top