Moving Average
 

I need to average the last 3 columns that contain data other than 0. Each
month new data is added so I would want to start at column IV and moving
backward to column IU and so on to locate the first column with data then
average that column with the two immediate columns before it. Any ideas
would be appreciated. Thank you.

Moving Average
 

This formula will average the last 3 cells in row 1 that have a value
excluding 0's. Entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=AVERAGE(IF(COLUMN(1:1)=LARGE(IF(1:1,COLUMN(1:1)) ,3),IF(1:1,1:1)))

Biff

"Chuck" wrote in message
...
I need to average the last 3 columns that contain data other than 0. Each
month new data is added so I would want to start at column IV and moving
backward to column IU and so on to locate the first column with data then
average that column with the two immediate columns before it. Any ideas
would be appreciated. Thank you.

Moving Average
 

Thanks for getting back to me. That seems to return the average of the
values in columns IT:IV, but doesn't work if the last column containing data
is HZ for example. I get a result of #DIV/0!.

"T. Valko" wrote:

This formula will average the last 3 cells in row 1 that have a value
excluding 0's. Entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=AVERAGE(IF(COLUMN(1:1)=LARGE(IF(1:1,COLUMN(1:1)) ,3),IF(1:1,1:1)))

Biff

"Chuck" wrote in message
...
I need to average the last 3 columns that contain data other than 0. Each
month new data is added so I would want to start at column IV and moving
backward to column IU and so on to locate the first column with data then
average that column with the two immediate columns before it. Any ideas
would be appreciated. Thank you.

Moving Average
 

I put these values in these cells:

A1 = 100
HO1 = 5
HP1 = 20
HZ1 = 10
IC1 = 0

The formula result was 11.666667 which is correct.

Biff

"Chuck" wrote in message
...
Thanks for getting back to me. That seems to return the average of the
values in columns IT:IV, but doesn't work if the last column containing
data
is HZ for example. I get a result of #DIV/0!.

"T. Valko" wrote:

This formula will average the last 3 cells in row 1 that have a value
excluding 0's. Entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=AVERAGE(IF(COLUMN(1:1)=LARGE(IF(1:1,COLUMN(1:1)) ,3),IF(1:1,1:1)))

Biff

"Chuck" wrote in message
...
I need to average the last 3 columns that contain data other than 0.
Each
month new data is added so I would want to start at column IV and
moving
backward to column IU and so on to locate the first column with data
then
average that column with the two immediate columns before it. Any
ideas
would be appreciated. Thank you.

Moving Average
 

OK. Thank you.

"T. Valko" wrote:

I put these values in these cells:

A1 = 100
HO1 = 5
HP1 = 20
HZ1 = 10
IC1 = 0

The formula result was 11.666667 which is correct.

Biff

"Chuck" wrote in message
...
Thanks for getting back to me. That seems to return the average of the
values in columns IT:IV, but doesn't work if the last column containing
data
is HZ for example. I get a result of #DIV/0!.

"T. Valko" wrote:

This formula will average the last 3 cells in row 1 that have a value
excluding 0's. Entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=AVERAGE(IF(COLUMN(1:1)=LARGE(IF(1:1,COLUMN(1:1)) ,3),IF(1:1,1:1)))

Biff

"Chuck" wrote in message
...
I need to average the last 3 columns that contain data other than 0.
Each
month new data is added so I would want to start at column IV and
moving
backward to column IU and so on to locate the first column with data
then
average that column with the two immediate columns before it. Any
ideas
would be appreciated. Thank you.

Moving Average
 

You're welcome!

Biff

"Chuck" wrote in message
...
OK. Thank you.

"T. Valko" wrote:

I put these values in these cells:

A1 = 100
HO1 = 5
HP1 = 20
HZ1 = 10
IC1 = 0

The formula result was 11.666667 which is correct.

Biff

"Chuck" wrote in message
...
Thanks for getting back to me. That seems to return the average of the
values in columns IT:IV, but doesn't work if the last column containing
data
is HZ for example. I get a result of #DIV/0!.

"T. Valko" wrote:

This formula will average the last 3 cells in row 1 that have a value
excluding 0's. Entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=AVERAGE(IF(COLUMN(1:1)=LARGE(IF(1:1,COLUMN(1:1)) ,3),IF(1:1,1:1)))

Biff

"Chuck" wrote in message
...
I need to average the last 3 columns that contain data other than 0.
Each
month new data is added so I would want to start at column IV and
moving
backward to column IU and so on to locate the first column with data
then
average that column with the two immediate columns before it. Any
ideas
would be appreciated. Thank you.