Using Pivot Tables to sort time periods

[ADE6]

Hi,

I am new to Pivot Tables so I would really appreciate any ideas.


Below I have some data showing closing levels of The Ftse 100 share index, only trading days are shown in then left hand column.

I want to sort my data by time periods such as weeks,months,years etc, which I have managed to do.



For example, the close price for August would involve finding the date of the last trading day within that month and then any formula would have to refer to the close level in that row and show that in a new column, so the close in the dummy data below for August would occur on the

20/09/06 AUGUST 5,866.20

so I just want a new column called Period Close to show 5,866.20 on the August row





Date month Close
06/10/06 SEPTEMBER 6,001.20
05/10/06 SEPTEMBER 6,004.50
04/10/06 SEPTEMBER 5,966.50
03/10/06 SEPTEMBER 5,937.10
02/10/06 SEPTEMBER 5,957.80
29/09/06 SEPTEMBER 5,960.80
28/09/06 SEPTEMBER 5,971.30
27/09/06 SEPTEMBER 5,930.10
26/09/06 SEPTEMBER 5,873.60
25/09/06 SEPTEMBER 5,798.30
22/09/06 SEPTEMBER 5,822.30
21/09/06 SEPTEMBER 5,896.70
20/09/06 AUGUST 5,866.20
19/09/06 AUGUST 5,831.80
18/09/06 AUGUST 5,890.20
15/09/06 AUGUST 5,877.00
14/09/06 AUGUST 5,877.20
13/09/06 AUGUST 5,892.20
12/09/06 AUGUST 5,895.50
11/09/06 AUGUST 5,850.80
08/09/06 AUGUST 5,879.30
07/09/06 AUGUST 5,858.10
06/09/06 AUGUST 5,929.30
05/09/06 AUGUST 5,981.70
04/09/06 AUGUST 5,986.60
01/09/06 AUGUST 5,949.10
31/08/06 AUGUST 5,906.10


Thanks for the ideas and excuse my ignorance.

Ade

 

[SkipVought]

Hi,

I don't understand your MONTH logic.

What you need is another column that tests the MONTH value. When MONTH changes, then use that row's CLOSE value. Otherwise ZERO.
[tt]
d2: =if(b1=b2,0,c2)
[/tt]
BTW, I would NOT use STRING month values. Rather, I would use 8/1/2006 & 9/1/2006 for August & September respectively and use the Group & Outline feature to DISPLAY the MONTH & YEAR in the PivotTable. STRINGS will not sort in the way that you want.

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