Grouping Dates in a PivotTable

[Duncs]

I have a spreadsheet of data, that shows:

Action Date
Cash Value 1
Cash Value 2
Valid (Y/N)
Cash Value 3
Cash Value 4

What I want to do is create a PivotTable that will show me:

Action Date, grouped by a week at a time
A Sum of all Cash Value 1's, where the Action Date is not null
A Sum of all Cash Value 2's, where the Action Date is not null
A count of all Valid entries, i.e. where the field contains 'Yes'
A Sum of all Cash Value 3's, where the Valid field if 'Yes'
A Sum of all Cash Value 4's, where the Valid field if 'Yes'
An Average of all Cash Value 4's, where the Valid field if 'Yes'

I can get most of it working, apart from grouping the dates by week
and the average.

Your help is, as always, appreciated.

TIA

Duncs

 

[Herbert Seidenberg]

Excel 2007
http://www.mediafire.com/file/yndhigi2zmn/12_16_08a.xlsx

 

[Socko]

To group a date field by week, right-click the Date field button...
Then, choose Group and Show Detail | Group... In grouping dialog box,
select days from the 'By' list.

Now the trick is to select 'Number of days', you may select 7 (7 days
of a week)...
The range of a week would be determined by date in the 'Starting at'
box, you may adjust if this is necessary.

For detailed example have a look at
A SITE BY Debra Dalgleish (MVP)
http://www.contextures.com/xlPivot07.html#Week ...

A sample file at the following location may also help you...
http://www.contextures.com/PivotSales.zip


Selva V Pasupathy
For more on Excel, VBA, & other resources,
Please visit http://socko.wordpress.com