ind the max among data
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
|