Moving average

[Mike B]

I would like to enter a formula for a 3 point moving average on a spreadsheet
that ignors blank cells in the row. Is there a function or combination of
functions that will do this? Or must it be done using a macro, and if so how?
On charts you can add a moving average and even projections, but I can't
find a function that does the same on a spreadsheet. Can anyone assist??

Thanks

 

[Biff]

Hi!

What is a 3 point moving average?

Does that mean you want to average the last 3 values in a
row?

Try this entered as an array with the key combo of
CTRL,SHIFT,ENTER:

=AVERAGE(IV1:INDEX(1:1,LARGE(IF(1:1<>"",COLUMN(1:1)),3)))

This will average the last 3 numeric values entered in row
1, A1:IV1.

Biff

 

[Mike B]

Hi Biff,

Thanks for the reply, it is the average of the last three values entered in
the row that I'm after. Unfortunately the entry gives me a formula error
message and having not used an array before and not being familiar with the
formula format I cannot work out where the error is. I will keep trying.
My data entries won't go past column Z, so I would like to put the formula
in column AA and then copy it down to average each row of the table.

Thanks,
Mike B

 

[Don Guillett]

try
=AVERAGE(OFFSET($D$1,COUNTA(D)-1,0,-3))

 

[Don Guillett]

Based on the workbook you sent to ignoring the text in col A and allowing
for variable in aa1 named var

enter in aa2 and copy down. enter new columns before column Z
=AVERAGE(OFFSET(B2,0,MATCH(1E+300,B2:Z2)-INDIRECT("var"),1,INDIRECT("var")))

 

[Mike B]

Hi Don,

You have lost me. My first entry into the newsgroup and I'm plainly out of
my depth and no nearer to an answer. Thanks anyway.

Regards,
Mike

 

[Don Guillett]

I thought the answer was self-explanatory
1. copy/paste my formula to cell aa2
2. copy down
3. put your variable in cell aa1
4. if you add more columns do so before or at col Z
If all else fails I sent you a workbook.

 

[Mike B]

Hi again,

Getting very close now. I need the formula to ignor rather than compensate
for the blanks, so that it takes the average on the last three numeric
entries.

Thanks.

 

[Don Guillett]

right click sheet tab>view code>insert this>name cell row 1 last column cell
var1>SAVE
changing a value on a row will fire the change macro and changing the var1
cell will change all.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("var1").Address Then doall
Application.EnableEvents = False
lc = [var1].Column
limit = [var1]
mr = activecell.Row
lca = Cells(mr, lc - 1).End(xlToLeft).Column
On Error Resume Next
For i = lca To 2 Step -1
If Not IsEmpty(Cells(mr, i)) And IsNumeric(Cells(mr, i)) Then
mc = mc + 1
mysum = mysum + Cells(mr, i)
If mc = limit Then Cells(mr, lc) = mysum / limit
End If
Next
Application.EnableEvents = True
End Sub

Sub doall()
lr = Cells(Rows.Count, "a").End(xlUp).Row
lc = [var1].Column
Range(Cells(2, lc), Cells(lr, lc)).ClearContents
limit = [var1]
For Each c In Range("b2:b" & lr)
mc = 0
mysum = 0
mr = c.Row
lca = Cells(mr, lc - 1).End(xlToLeft).Column
For i = lca To 2 Step -1
If Not IsEmpty(Cells(mr, i)) And IsNumeric(Cells(mr, i)) Then
mc = mc + 1
mysum = mysum + Cells(mr, i)
If mc = limit Then Cells(mr, lc) = mysum / limit
End If
Next i
Next c
End Sub