Help with pulling data based on latest date for a year

S

Satish

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
 
M

muddan madhu

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Latest date for each of the year 3
Calc Date Based on Year 3
Minimum date per month 1
Calculate next year in different cell 10
Use cell_ref in place of an item in get pivot data function 2
sum a range based on Qtr and Year 2
Can i drop the year from dd/mm/yyyy for graph 2
Query to show latest price based on dates 2

Top