Monthly Summary of days of the week ?

[spikemixture]

Greetings all,

I hope someone can help me with this issue.

I have one spreadsheet with worksheets for each month for the last 18
months.

Each worksheet has column A being the date eg 1st -31st January 2009
Col B has the formula to set the actual day of the week - 1/1/09 =
Thursday
Then I have numbers in columns B to G for incomings etc and outgoings
then a net figure

Now I have all sorts of averages per day per month per year but would
like to have an automatic calculation so I know what day of the week
(e.g Tuesday ) is as a percentage of the weeks $$'s
Therefore knowing the best and worst day of the week for sales and for
costs etc etc...
Each day should be about 14.4% of the week (100% /7)

So at the bottom of the sheet I would have the summary

Day = % of incoming - % of outgoings
Mondays, = 14% - 12%
Tuesdays = 16% - 13%
Wednesdays = 12% - 15%
etc =
etc

I hope you can understand what I am saying

Thanks

Spike...

 

[Pete_UK]

Your description is a bit short on detail, apart from telling us that
you have dates in column A and Day of Week in column B. You could do
something like this in K1:

=SUMIF(B:B,J1,C:C)/SUM(C:C)

where J1 contains Monday - this will give you the subtotal of column C
where column B contains Monday and then divide it by the total of
column C (which you can express as a percentage). If you have other
days in J2:J7, then you can copy the formula down to get those
percentages for the other days.

Is this the kind of thing that you want?

Hope this helps.

Pete

 

[Shane Devenshire]

Hi,

Suppose your dates are in column A with one of the values you want results
for in column C, create a summary area as follows ( I will do it in M1:M7)

M
1
2
3
4
5
6
7

Format these cells to the custom format of DDDD. (they will now show Sunday
thru Saturday).
In N1 enter the formula below and copy it down to N7:

=SUMPRODUCT(--(WEEKDAY(A$1:A$100)=M1),C$1:C$100)/SUM(C$1:C$100)

Format these results as percent.

 

[Herbert Seidenberg]

Excel 2007
No formulas needed for PivotTable.
Consolidates data of all month sheets.
Icons to highlight best and worst.
Automatic grouping of weekdays, months, ...
Filter for individual day(s), month(s).
Automatic charts.
Random data provided for testing.
http://www.mediafire.com/file/lyzyzdmgunj/02_23_09.xlsx