I have a long column of dates in yyyymmdd format and sales $ in another. I
wish to find what my average sales are for each day of the week ie. Mon Tue
etc. from the col. of dates that I have. I would prefer to do this in a pivot
table but if that is too complex then within the data sheet is OK .

Hope someone can help.Thanks

hi.
in A6..5/28/06
=TEXT(A6,"dddd")= Monday
=TEXT(A6,"ddd") = Mon
=TEXT(A6,"dd") = 28

regards
FSt1



"Eqa" wrote:

I have a long column of dates in yyyymmdd format and sales $ in another. I
wish to find what my average sales are for each day of the week ie. Mon Tue
etc. from the col. of dates that I have. I would prefer to do this in a pivot
table but if that is too complex then within the data sheet is OK .

Hope someone can help.Thanks

Say we start with data like:

date sales
20070101 £119.00
20070102 £158.00
20070103 £153.00
20070104 £158.00
20070105 £174.00
20070106 £129.00
20070107 £142.00
20070108 £195.00
20070109 £183.00
20070110 £195.00
20070111 £141.00
20070112 £153.00
20070113 £115.00
20070114 £198.00
20070115 £142.00
20070116 £157.00
20070117 £119.00
20070118 £163.00
20070119 £110.00

in cols A & B
In C2 enter:
=TEXT(A2,"ddd") and copy down
In C1 enter:
Day

create the pivot table as the AVERAGE of Sales against Day
to see something like:

Average of sales
day Total
Sun 170
Mon 152
Tue 166
Wed 155.6666667
Thu 154
Fri 145.6666667
Sat 122
(blank)
Grand Total 152.8421053

--
Gary''s Student - gsnu200725


"Eqa" wrote:

I have a long column of dates in yyyymmdd format and sales $ in another. I
wish to find what my average sales are for each day of the week ie. Mon Tue
etc. from the col. of dates that I have. I would prefer to do this in a pivot
table but if that is too complex then within the data sheet is OK .

Hope someone can help.Thanks

Thanks both Gary and FSt1 that is ver helpful. Eqa

"Gary''s Student" wrote:

Say we start with data like:

date sales
20070101 £119.00
20070102 £158.00
20070103 £153.00
20070104 £158.00
20070105 £174.00
20070106 £129.00
20070107 £142.00
20070108 £195.00
20070109 £183.00
20070110 £195.00
20070111 £141.00
20070112 £153.00
20070113 £115.00
20070114 £198.00
20070115 £142.00
20070116 £157.00
20070117 £119.00
20070118 £163.00
20070119 £110.00

in cols A & B
In C2 enter:
=TEXT(A2,"ddd") and copy down
In C1 enter:
Day

create the pivot table as the AVERAGE of Sales against Day
to see something like:

Average of sales
day Total
Sun 170
Mon 152
Tue 166
Wed 155.6666667
Thu 154
Fri 145.6666667
Sat 122
(blank)
Grand Total 152.8421053

--
Gary''s Student - gsnu200725


"Eqa" wrote:

I have a long column of dates in yyyymmdd format and sales $ in another. I
wish to find what my average sales are for each day of the week ie. Mon Tue
etc. from the col. of dates that I have. I would prefer to do this in a pivot
table but if that is too complex then within the data sheet is OK .

Hope someone can help.Thanks