Moving Average

[Guest]

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.

 

[T. Valko]

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

 

[Guest]

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]

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

 

[T. Valko]

You're welcome!

Biff

OK. Thank you.