ind the max among data
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 |
ind the max among data
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 |
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 |
All times are GMT +1. The time now is 05:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com