Moving Average

[cunningAce]

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,

	B	C	D	E	F	G	H	I	J	K	L

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: center"]Week 1[/TD]
[TD="align: center"]Week 2[/TD]
[TD="align: center"]Week 3[/TD]
[TD="align: center"]Week 4[/TD]
[TD="align: center"]Week 5[/TD]
[TD="align: center"]Week 6[/TD]
[TD="align: center"]Week 7[/TD]
[TD="align: center"]Week 8[/TD]
[TD="align: center"]Week 9[/TD]
[TD="align: center"]Week 10[/TD]
[TD="align: center"]Average[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]44[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]45[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]65[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]Weeks Average[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Try

=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))

 

[tyija1995]

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:

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]

Hi Scott, this prodcudes a #Value error

 

[cunningAce]

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:

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]

OK, that's fine, I've worked on a UDF to assist with this and HOPEFULLY this will work

First define the following UDF:

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:

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:

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

 

[Snakehips]

cunningAce,

Maybe like the following Array formula....

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

 

[Scott T]

Hi Scott, this prodcudes a #Value error

the formula needs to be committed by CONTROL+SHIFT+ENTER If done properly you will see {} around the formula in the formula bar

 

[Snakehips]

I've just noticed that my formula is referencing row 3 rather than row 4 so for what it's worth, should be..

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

 

[cunningAce]

This works great, thank you Snakehips
@Scott T - your formula works unless i extend beyond week 8.