The excel file has 4 columns Product, Date, Year (calculated based on the
date), Price.
For some Products price appears more than once in a year.
I need the year wise price breakup for each of the products based on the
latest date of the year. E.g: Product X has prices on dates 1/3/2007 and
3/31/2007 I need the price of 3/31/07. When I try creating a pivot, if there
are more than one price in a year, it gets added up whereas I need the latest
price for that year. How do I pick up the price based on latest data in a year

Try this one

product in col A , date in Col B , Year in col C, Price in Col D

In F2 put the name of product which is available in Col A and in G2
put this formula

=INDEX(--MAX((B2:B10)),MATCH(F2,A2:A310,0),0)



On Sep 11, 5:31*am, Satish wrote:
The excel file has 4 columns Product, Date, Year (calculated based on the
date), Price.
For some Products price appears more than once in a year.
I need the year wise price breakup for each of the products based on the
latest date of the year. E.g: Product X has *prices on dates 1/3/2007 and
3/31/2007 I need the price of 3/31/07. When I try creating a pivot, if there
are more than one price in a year, it gets added up whereas I need the latest
price for that year. How do I pick up the price based on latest data in a year

Thanks Muddan.
I tried this formula, I am getting #REF error

"muddan madhu" wrote:

Try this one

product in col A , date in Col B , Year in col C, Price in Col D

In F2 put the name of product which is available in Col A and in G2
put this formula

=INDEX(--MAX((B2:B10)),MATCH(F2,A2:A310,0),0)



On Sep 11, 5:31 am, Satish wrote:
The excel file has 4 columns Product, Date, Year (calculated based on the
date), Price.
For some Products price appears more than once in a year.
I need the year wise price breakup for each of the products based on the
latest date of the year. E.g: Product X has prices on dates 1/3/2007 and
3/31/2007 I need the price of 3/31/07. When I try creating a pivot, if there
are more than one price in a year, it gets added up whereas I need the latest
price for that year. How do I pick up the price based on latest data in a year