average if
 

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

average if
 

What if there aren't 30 values to average?

Are there any empty cells within the range?

--
Biff
Microsoft Excel MVP


"~slacker~" wrote in message
...
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

average if
 

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.

average if
 

Try this:

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

--
Biff
Microsoft Excel MVP


"~slacker~" wrote in message
...
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.

average if
 

thats perfect thanks!

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

average if
 

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.

--
Biff
Microsoft Excel MVP


"~slacker~" wrote in message
...
thats perfect thanks!

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