Averaging Prices of a specific product by Date range

[ralexander2209]

I am having trouble averaging the price of a specific product for a specific date range.

Attached is a sample of the excel sheet I'm working with. Of course, the sheet is much bigger but I feel that simplicity will help find the best solution. I use a 4 letter tag number to identify the products.

I want to be able to pull the prices for that specific tag number and at the same time pull the prices of the product during a time period.

The first sheet is showing a sample of what I am currently seeing with this formula:

=AVERAGEIFS(Prices,DATES,DATES>=StartDate,DATES,DATES<=EndDate,OFFSET(DATES,,MATCH(AD2,PRICES,0)-1),)

PRICES: ALL of the prices in the price table.
DATES: All of the dates from 2002-Today
Start Date: self-explainatory
AD2: the cell with the tag that I am looking to pull the TAG (four letter code)

https://onedrive.live.com/edit.aspx...relation=f1a6100e-e75f-44a0-a31f-c29fa35e89d6

 

[anthonya2369]

<code>=AVERAGEIFS(Prices,DATES,DATES>=StartDate,DATES,DATES<=EndDate,OFFSET(DATES,,MATCH(AD2,PRICES,0)-1),)</code>

I can't get to your spreadsheet here at work but have you tried removing the "DATES" in the criteria piece and putting quotes around the <= and >=?

<code>=AVERAGEIFS(Prices,DATES,">="&StartDate,DATES,"<="&EndDate,OFFSET(DATES,,MATCH(AD2,PRICES,0)-1),)</code>

 

[ralexander2209]

Well, "DATES" aren't actually the name range DATES because it comes from a range of data that is on another workbook and when I put the " " it says there is an error in the formula. It doesn't seem to work.

 

[anthonya2369]

did you try:


<code>=AVERAGEIFS(Prices,DATES,">="&StartDate,DATES,"<="&EndDate,OFFSET(DATES,,MATCH(AD2,PRICES,0)-1),) </code>