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
.