Sorting data based on quarters of the year

[ADE6]

Hi

Not quite sure of the best way to approach this problem

I need to filter the data in the table below into quarters
Jan-Mar
Apr-Jun
Jul-Sept
Oct-Dec

For each quarter i need to find the change in values,i need to subtract the open price in each quarter(Jan,Apr,Jul,Oct) from the closing price at the end of each quarter(Mar,Jun,Sept,Dec)

So for the latest quarter shown at the top of the table Apr-Jun(04),
the open in April would be 4,385.70 which i would like to subtract from the close in June 4,487 , 4,487-4,385.70 = 101.3

So for Apr-Jun(04) the market gained 101.3




Date-----Open-- Close
Jun-04 4,430.70 4,487 JUNE
May-04 4,489.70 4,431 MAY
Apr-04 4,385.70 4,490 APRIL
Mar-04 4,492.20 4,386 MARCH
Feb-04 4,390.70 4,492 FEBRUARY
Jan-04 4,476.90 4,391 JANUARY
Dec-03 4,342.60 4,477 DECEMBER
Nov-03 4,287.60 4,343 NOVEMBER
Oct-03 4,091.30 4,288 OCTOBER
Sep-03 4,161.10 4,091 SEPTEMBER


Thanks for the ideas

Ade

 

[SkipVought]

ADE6,

If your Date column is REALLY a Date (the way that you can tell is to format the column as General and observe if the Dates change to Numbers -- then just UNDO to return to the previous format) AND if EVERY row in the table has a Date, you can use the PivotTable Wizard with Date in either ROW or COLUMN Layout Area and after you [Finish], right click the Date Field Header in the PivotTable and select Group and show detail - select Group - select Year and Quarter.

VOLA!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244