Moving Average Formula Problem

B

Bob

I have a workbook with a date on worksheet 1 that indicates data has
been entered for that month.

On worksheet 2 is are columns of numbers with an average for each
column at the bottom. I would like the average to use the date on
worksheet 1 to calculate the average.

The start of the range is known, but the end of the range is found only
by looking up the date on worksheet 1.

I have tried to use something like: =AVERAGE(B10:ADDRESS(VLOOKUP(date,
range, 1, TRUE). This doesn't work and I can't find the functions
needed to make it successful.

Thanks in advance for any help!
 
R

Rajah

Hello, Bob,
I suggest you use the DAVERAGE function, which lets you retrieve the
average of certain numbers, based on given criteria.

For example, I set up a spreadsheet like this:
a1 contains "date"
b1 contains "num"
a2 contains "<1/10/2006"
a3 contains "date"
b3 contains "num"
a4 through a17 have dates 1/1/2006 through 1/14/2006.
b4 through b17 have numbers that I want to average.

A 19 contains the formula =DAVERAGE(A3:B17,"num",A1:B2)
which means "Give me the average of the num column where the condition
A1:B2 holds. (The condition is date <1/10/2006).

Hope that helps.
 
K

kletcho

Another option is to use a dynamic range. A dynamic range is a named
range that uses the offset formula. So you create a named range that
uses this formula to determine the range:

=Offset($B$10,0,0,VLOOKUP(date, range, 1, TRUE), 1)

Let's say we named the range Bob. Then your average formula would be:

=Average(Bob)

This assumes that you are average contiguous cells. If they are not
all right next to each other then you will need to use Rajah's advice
on DAVERAGE or look into array formulas.
 
Top