I think I need a pivot table but not sure.

Cross-posted at https://www.mrexcel.com/board/thread...lysis.1242649/

Hi All,

I run a small business and record my income on an Excel 2007 speadsheet which I've attached (edited version)

The essential info is date the job was completed and the associated income.

At the end of the week (Saturday) I manually total the weeks income together with number of transactions and the average value.

The speadsheet has several worksheets with include prospects and current. Once a job is complete and paid for I transfer it from the Current to the Income sheet with the newest transaction at the top of the list.

In a perfect world I'd like to have a new worksheet that automaticly displays:

Daily total
Weekly total
Monthly total
Moving 7/14/28 averages

It would also be nice to have this info in graphical form too.

I'm guessing I need pivot table but not sure where to start.

Many thanks.

Trying to tailor this for the 2007 version of Excel.
As to the Daily and pivot table:
1. Fill column B with the customer number IF there is a income >0 in column E
2. Pull Date Out into the Rows area and Income into the Values area
As to the Weekly pivot table:
1. Fill column B with the customer number IF there is a income >0 in column E
2. Pull Date Out into the Rows area and Income into the Values area and then group Date out by Days (7)
As to the Monthly pivot table:
1. Fill column B with the customer number IF there is a income >0 in column E
2. Pull Date Out into the Rows area and Income into the Values area and then group Date out by Month
If you want income by customer as a pivot table:
1. Fill column B with the customer number IF there is a income >0 in column E
2. Select B7:L64 as the data for the pivot table
3. Pull the customer field into the Rows area and the Income and Details fields into the Values area
4. The average is placed next to the pivot table using: =GETPIVOTDATA("Sum of Income",$N$8,"Customer",N9)/GETPIVOTDATA("Count of Details",$N$8,"Customer",N9)
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.