average if

[~slacker~]

I'm trying to average the last 30 values in a column.
Column A contains a date and Column B contains a value for that date.
A running average of the last 30 days (or 1-month) needs to be kept so
that if new data is entered the average automatically updates.

any ideas?

I was thinking something like
=AVERAGEIFS(B:B,A:A,>LARGE(A:A,30))

but the greater than seems to muck things yup.

thanks

 

[T. Valko]

What if there aren't 30 values to average?

Are there any empty cells within the range?

 

[~slacker~]

no empty cells in the range.

it doesn't take long to get over 30 values but if it were less than
thirty then i'd want to average the 20 or whatever values that there
were.

 

[T. Valko]

Try this:

=IF(COUNT(B:B),AVERAGE(OFFSET(B2,COUNT(B:B)-1,,MAX(-COUNT(B:B),-30))),"")

 

[~slacker~]

thats perfect thanks!

although i don't understand the use of the if. seems it will always
be true and is not needed.

 

[T. Valko]

The IF(COUNT(...)... makes sure there is at least 1 number in the range. If
there were no numbers to average then you'd get a #DIV/0! error.
IF(COUNT(...)... prevents that error. You can remove it if you don't need
it.