1. I reckon that this can be done through a Pivot Table.
2. I have just uploaded a file called:-
Peters48.xlsx
- to:-
www.pierrefondes.com
3. The above file will be item number 33 towards the top of the page.
I have left my rough workings in here as well (and my checks on the Pivot
Table calculations).
Pivot Table starts at cell L 1.
4. Your 2 columns of data start at cell A 9 and are highlighted in yellow.
I have only done last week and last month figures and the numbers in the
Pivot Table agree to my manual calculations.
5. When you first go into Pivot Table you will see:-
35 for January (this number checks out and is correct)
16 for December (again this number checks out and is correct).
6. If you want to see the number for last week take the following action:-
- click in cell M 2 (this has the word Jan in it)
- PivotTable Tools / Options / Group group / Group Field
- Grouping window should launch
- de-select Months (which should be highlighted)
- select Days
- change Starting at: date to read 28/12/2009 (make sure box to left NOT
ticked)
- change Ending at: date to read 03/01/2010 (make sure box to left ticked)
- change Number of days: to 7
- Hit OK.
7. Pivot Table will now change.
In cell N 2 you will get description:-
28/12/2009 – 03/01/2010
- and beneath that you will get the number 31.
This is the 31 average for last week and agrees with my manual calculation
in cell F 43.
Hope that the above has helped you out.
If it has please hit Yes.
Thanks!