Each Item's Most Recent/Month/Half Year Average Price

[jo3c]

I have a Table like this

Please Login or Register  to view this content.

The same ITEM may have multiple different price at different time
For example

ITEM
668CY9YX56Y
has different price shown as below

Please Login or Register  to view this content.

How do I calculate below: ??

Each Item's Most Recent Price Average
Each Item's Past one Month Price Average
Each Item's Past 6 Months Price Average

Thank you so much

Example File is here
http://dl.dropbox.com/u/23447837/ExcelForumA.xls

[Roger Govier]

Hi

Take a look at the attached workbook.

You can just use Autofilter, with the Subtotal formula to get the average for the selection that you filter.

I have also shown how to calculate the figures using Sumproduct

From your header it looks as though you are using XL2003, hence the use of Sumproduct.

If you were using XL2007 or 2010 you could use the AVERAGEIFS() function

[jo3c]

Thank you so much Roger