Date Formulas to find the month of the year with the highest value 2

[ADE6]

Hi,

I have data for the Dow Jones Stock Index, a sample of which is below and my data goes back over 20 years in total.

In column A, I have the high price achieved each month.

What I would like to do would be to calculate the high price achieved for each completed year. So the last completed year would be 2005. I would like to construct a formula that will find the high achieved between January and December each year and for the formula to appear in column 4, next to the relevant year.

Foe example the high for 2005 would have been 10,805.87,achieved in Nov-05.

Really appreciate the help.

Ade











HIGH DATE YEAR HIGH
11,381.15 Aug-06 2005 10,805.87
11,185.68 Jul-06 2004
11,150.22 Jun-06 2003
11,168.31 May-06 2002
11,367.14 Apr-06 2001
11,109.32 Mar-06 2000
10,993.41 Feb-06 1999
10,864.86 Jan-06 1998
10,717.50 Dec-05 1997
10,805.87 Nov-05 1996
10,440.07 Oct-05 1995
10,568.70 Sep-05 1994
10,481.60 Aug-05 1993
10,640.91 Jul-05 1992
10,274.97 Jun-05 1991
10,467.48 May-05 1990
10,192.51 Apr-05 1989
10,503.76 Mar-05 1988
10,766.23 Feb-05 1987
10,489.94 Jan-05 1986
10,783.01 Dec-04 1985

 

[SkipVought]

Hi,

Please format your DATES as yyyy-mm-dd and repost your example.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

You could use a PivotTable, Group on date by YEAR, aggregate MAX of High.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

On second thought, you did not post sample SOURCE data. That's what we need to see.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

Enter as an Array Formule (Edit: shift+cntr+enter)
[tt]
=MAX((YourDateRange>=TheJanFirstDate)*(YourDateRange<NextYearsJanFirstDate)*(YourHighRange))
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[AnotherHiggins]

I second the Pivot Table idea. When you get to step 3 of the wizard and click on Layout, drag Date to the Row section and drag High to the Data section - leave Column blank.

Double click on SumOfHigh (now in the Data section) and select Max.

Press OK and then Finish.

Right click on any one of the dates in the resulting Pivot Table and choose Group and Outline > Group. Unselect Month and select Years.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.