Moving Average

cunningAce

Board Regular
Joined
Dec 21, 2017
Messages
86
Hi,

Hoping someone could help me with the following please,

I am looking to find the average of a range of numbers (determined by the variable in the 'weeks average' cell)

So in the below examples, I want the average of the last 5 weeks for each set of numbers.
I want to ignore any zeros after the last weeks figures (week 8 in these examples) but include any zeros within the 5 week average,

Eg,
The first line would calculate an average for weeks 4-8 - (0,10,50,60,100 = 44)
The 3rd line would also calculate weeks 4-8 ignoring anything before, and any zeros after week 8. (5,10,50,60,200 = 65)

Hope this makes sense,


Thanks in advance,


BCDEFGHIJKL
3Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Week 10Average
400001050601000044
500051050601000045
624651050602000065
7
8
9Weeks Average5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

<tbody>
</tbody>

 
Last edited:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,616
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try
Code:
=AVERAGE(OFFSET(B4:INDEX(B4:K4,LARGE(IF(B4:K4<>0,COLUMN(B4:K4)-COLUMN(B4)+1),1)),0,10-$C$9-2,1,$C$9))
 
Last edited:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
Do you know if there is a limit to how many 0's can occupy from the last week on the list until the first week that has a number (<> 0)

E.g. in your example there appears a limit of 2 weeks (Week 9 & Week 10) that show 0's, I have a solution that governs the example but it's very specific to the example. So if you added Week 11 and that was also 0 it would calculate wrongly, unless Week 9 was then populated with some number.

So in L4:

Code:
IF(OFFSET(L4,0,-1)<>0,AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()-5)):INDIRECT(ADDRESS(ROW(),COLUMN()-1))),IF(OFFSET(L4,0,-2)<>0,AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()-6)):INDIRECT(ADDRESS(ROW(),COLUMN()-2))),AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()-7)):INDIRECT(ADDRESS(ROW(),COLUMN()-3)))))

It will average the final week (and the 4 before it) if final week <> 0, else it will average the penultimate week (and the 4 before that) if the penultimate week <> 0, else it will average the weeks (N-6 : N-2) (So N = 10 in example, thus averages weeks 4 to 8)

Sorry it's messy and only works on these conditions... I'm not sure of a better way otherwise! The formula would become way too large
 

cunningAce

Board Regular
Joined
Dec 21, 2017
Messages
86

ADVERTISEMENT

Do you know if there is a limit to how many 0's can occupy from the last week on the list until the first week that has a number (<> 0)

E.g. in your example there appears a limit of 2 weeks (Week 9 & Week 10) that show 0's, I have a solution that governs the example but it's very specific to the example. So if you added Week 11 and that was also 0 it would calculate wrongly, unless Week 9 was then populated with some number.

So in L4:

Code:
IF(OFFSET(L4,0,-1)<>0,AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()-5)):INDIRECT(ADDRESS(ROW(),COLUMN()-1))),IF(OFFSET(L4,0,-2)<>0,AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()-6)):INDIRECT(ADDRESS(ROW(),COLUMN()-2))),AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()-7)):INDIRECT(ADDRESS(ROW(),COLUMN()-3)))))

It will average the final week (and the 4 before it) if final week <> 0, else it will average the penultimate week (and the 4 before that) if the penultimate week <> 0, else it will average the weeks (N-6 : N-2) (So N = 10 in example, thus averages weeks 4 to 8)

Sorry it's messy and only works on these conditions... I'm not sure of a better way otherwise! The formula would become way too large

Hi thanks for this,
Unfortunately the data would extend beyond week 8 with more zeros.
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
780
Office Version
  1. 365
Platform
  1. Windows
OK, that's fine, I've worked on a UDF to assist with this and HOPEFULLY this will work :p

First define the following UDF:

Code:
Function RNONZERO() As Variant
    On Error Resume Next
    Dim FormulaCell As Variant
    Dim i As Integer
    RNONZERO = 1
    FormulaCell = Cells(Application.ThisCell.Row, Application.ThisCell.Column).Address
    For i = 1 To 999
        If Range(FormulaCell).Offset(0, -i).Value = 0 Then
            RNONZERO = RNONZERO + 1
        Else
            RNONZERO = RNONZERO
            Exit For
        End If
    Next i
End Function

Now in cell L4 try this:
Code:
AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()-RNONZERO()-4)):INDIRECT(ADDRESS(ROW(),COLUMN()-RNONZERO())))

Let me know if it works.

EDIT: If you want to link it so it corresponds to your Weeks Average number (5) then do this:

Code:
AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN()-RNONZERO()-($C$9-1))):INDIRECT(ADDRESS(ROW(),COLUMN()-RNONZERO())))

Where I take C9 to be the weeks average figure (5 in example given), updating this figure will reflect in the formula thus making it more dynamic :)
 
Last edited:

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

cunningAce,

Maybe like the following Array formula....

Code:
=AVERAGE(INDEX(B3:K3,1,LOOKUP(9^9,IF(B3:K3=0,"",COLUMN(B3:K3)))-1-($C$9-1)):INDEX(B3:K3,1,LOOKUP(9^9,IF(B3:K3=0,"",COLUMN(B3:K3)))-1))

Extend the references to K3 as suits.

There is some error potential that you might wish to cater for, either by changing formula and or using data validation rules.
Eg It will currently give a wrong result / error if $C$9 is empty, 0, text or a silly value. Or if row data all zeros.

Hope that helps.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
I've just noticed that my formula is referencing row 3 rather than row 4 so for what it's worth, should be..
Code:
=AVERAGE(INDEX(B4:K4,1,LOOKUP(9^9,IF(B4:K4=0,"",COLUMN(B4:K4)))-1-($C$9-1)):INDEX(B4:K4,1,LOOKUP(9^9,IF(B4:K4=0,"",COLUMN(B4:K4)))-1))
and confirmed with Ctrl + Shift + Enter.

I can confirm that Scot T's shorter formula does the same job and is subject to similar error potential.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,940
Messages
5,598,975
Members
414,270
Latest member
skipolmsted

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