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 a
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

you need to use the DMAX function

"lina" wrote:

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 a
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

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


"lina" wrote:

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 a
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