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

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

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

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



"Biff" wrote:

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

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

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

--
Don Guillett
SalesAid Software

"Mike B" wrote in message
...
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



"Biff" wrote:

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

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

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

--
Don Guillett
SalesAid Software

"Mike B" wrote in message
...
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



"Biff" wrote:

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

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

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" wrote:

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

--
Don Guillett
SalesAid Software

"Mike B" wrote in message
...
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



"Biff" wrote:

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

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

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.

--
Don Guillett
SalesAid Software

"Mike B" wrote in message
...
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" wrote:

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

--
Don Guillett
SalesAid Software

"Mike B" wrote in message
...
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



"Biff" wrote:

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

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

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" wrote:

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.

--
Don Guillett
SalesAid Software

"Mike B" wrote in message
...
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" wrote:

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

--
Don Guillett
SalesAid Software

"Mike B" wrote in message
...
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



"Biff" wrote:

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

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

right click sheet tabview codeinsert thisname cell row 1 last column cell
var1SAVE
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

--
Don Guillett
SalesAid Software

"Mike B" wrote in message
...
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" wrote:

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.

--
Don Guillett
SalesAid Software

"Mike B" wrote in message
...
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" wrote:

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

--
Don Guillett
SalesAid Software

"Mike B" wrote in message
...
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



"Biff" wrote:

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

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