Calculating an AVG from data on another sheet

[Guest]

Let's say I have 2 columns of data on worksheet "Data" - column A is date,
and column B contains numeric values. Using a date found on worksheet "Test"
(in the same workbook), I'd like to lookup that date on "Data" and calculate
the average of the last 100 days of the values in column B.

It's easy enough to get the last of the 100 days using a simple VLOOKUP, but
I thought I might return the row of that cell found in the VLOOKUP and use
that value to construct an AVERAGE. Something like

AVERAGE(R[x-100]C[2]:R[x]C[2])

where x is the row that was found with the VLOOKUP. Does anyone know how to
return the row that results from a VLOOKUP? Or better yet, is there an
easier way to accomplish this task?

Thanks in advance!
Tom

 

[Don Guillett]

One easy way to do this is:
1. in the destination sheet select a cell and type =average(
2. now goto the source sheet>select the range and press enter
3. complete the formula
voila!

 

[Jason Morin]

Try:

=AVERAGE(OFFSET(B1,MATCH(Test!A1,A:A,0)-1,,-100))

where Test!A1 holds the lookup date value. This averages
the last 100 days inclusive based on the date in Test!A1.

HTH
Jason
Atlanta, GA

 

[Guest]

Jason,

You da man! Your suggestion worked perfectly. Thanks much!

Tom

Try:

=AVERAGE(OFFSET(B1,MATCH(Test!A1,A:A,0)-1,,-100))

where Test!A1 holds the lookup date value. This averages
the last 100 days inclusive based on the date in Test!A1.

HTH
Jason
Atlanta, GA

-----Original Message-----
Let's say I have 2 columns of data on worksheet "Data" - column A is date,
and column B contains numeric values. Using a date found on worksheet "Test"
(in the same workbook), I'd like to lookup that date on "Data" and calculate
the average of the last 100 days of the values in column B.

It's easy enough to get the last of the 100 days using a simple VLOOKUP, but
I thought I might return the row of that cell found in the VLOOKUP and use
that value to construct an AVERAGE. Something like

AVERAGE(R[x-100]C[2]:R[x]C[2])

where x is the row that was found with the VLOOKUP. Does anyone know how to
return the row that results from a VLOOKUP? Or better yet, is there an
easier way to accomplish this task?

Thanks in advance!
Tom
.