ind the max among data

[Guest]

I want to create a report that shows for a list of stocks the maximum price
of each stock for a period of time and the date it occured. The data I've
collected are:
Stock Date Price
A 1/1/2005 10
A 2/1/2005 8
A 3/1/2005 5
B 1/1/2005 2
B 2/1/2005 1
B 3/1/2005 3
C 1/1/2005 10
C 2/1/2005 11
C 3/1/2005 7


I've used a pivot table but I only could generate the maximum stock price
and not the date it occured. Same with using subtotals
Any idea? I have data for 5 years or over 300 stocks, so any help could
prove really valuable

 

[Guest]

you need to use the DMAX function

 

[Guest]

Another suggestion, with stocks in ColA, dates in ColB, price in ColC, and
the stock you are looking for in E1, you could try:

=MAX((A2:A10=E1)*(C2:C10))
to get the amount, and

=MAX((A2:A10=E1)*(B2:B10)*(C2:C10=MAX((A2:A10=E1)*(C2:C10))))
to get the date.

Both are entered with Ctrl+Shift+Enter