Using VLOOKUP with AVERAGE 1

[Ted1]

Is anyone able to help me?

Put simply, I need to combine the VLOOKUP and AVERAGE(OFFSET(range)) functions into one cell formula.

There's a couple of limitations:

1) The interrogating sheet is produced on a daily basis and uses the sheet's date as the key to locating the correct row and cell on a large target sheet. The target sheet contains historical information which is pasted to it on a daily basis. Macros are precluded because each interrogating sheet has a different name (ie. based on its date).
2) It's not a one-off enquiry since any of the daily sheets can be brought up at any time and needs to access the data row relevant to its particular date.

To put the problem in its operational context, each daily sheet contains downloaded share prices for a number of public companies. Against each company, the daily sheet refers back to the large history sheet to find the same company's share price 5 trading days previously and 20 trading days previously. That part operates OK.

The current problem is to also show the AVERAGE trading price for the particular company over the previous 50 trading days.

Currently (I'm ashamed to admit) I've overcome the problem by creating a subsidiary worksheet linked to the historical worksheet. Each cell in each row on the subsidiary sheet refers back to the main sheet using the formula:

=AVERAGE(OFFSET(MainWorksheet!(cell,-50,0):OFFSET(MainWorksheet!,0,0))

In other words, the daily sheet is interrogating the subsidiary sheet for the pre calculated 50 day average using VLOOKUP and the date as the key.

It's a very inelegant solution for an operation which should be able to combine the VLOOKUP and AVERAGE(OFFSET(range)) functions into one without having to create a separate worksheet to perform the second leg of the operation.

In actual fact, the daily sheet shows the company's percentage variation from the current day's prices but that's an easy extension of the basic formula.

 

[CautionMP]

Ted1,
I tried a bunch of different built in functions available in Excel and I don't have an answer for you, but I might have a different direction that may work.

If the data in MainWorksheet is linear (i.e. all entries for stock_a are in rows 3-270, then stock_b in rows 271-403...) what you might be able to do is add a column in MainWorksheet that computes the moving average on a row by row basis. Then your vlookup() function could search this new column to find the moving 50 day average.

If this is not possible you may want to explore writting your own custom function to return your moving average, depending on your VBA skills it would not be to difficult.

I truely hope you find this helpful.

P.S. If each daily worksheet name is governed by a naming convention (i.e. '2005.03.26') you can easily incorporate this into a macro.