Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculating an AVG from data on another sheet
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 |
#2
|
|||
|
|||
One easy way to do this is:
1. in the destination sheet select a cell and type =average( 2. now goto the source sheetselect the range and press enter 3. complete the formula voila! -- Don Guillett SalesAid Software "flummoxed" wrote in 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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
Jason,
You da man! Your suggestion worked perfectly. Thanks much! Tom "Jason Morin" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfer data from sheet to sheet | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Automatic cell increment with data from sheet 1 to sheet 2 | Excel Worksheet Functions | |||
pull data from sheet two, then fill in the data to sheet one (part | Excel Worksheet Functions | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions |