Calculating Daily Averages for Different Products
 

I have a fairly data set of the number of products sold each day for
the past year.

In Column A, I have the days in question by row:
Row1: Monday 1/1/2005
Row2: Tuesday 1/2/2005
Row3: Wednesday 1/3/2005
and so on.

In Column B, I have the number of Product A sold on each day.
In Column C, I have the number of Product B sold on each day.

My Question:
Is there a way I can calculate the average number of products sold on
Mondays over the past year?

Any help would be GREATLY appreciated.

Calculating Daily Averages for Different Products
 

Here is one way.
Change the ending range appropriately, you may need the analysis
toolpak addin checked to get the weekday function.

For column B
=SUMPRODUCT((WEEKDAY(A2:A29)=2)*(B2:B29))/SUMPRODUCT((WEEKDAY(A2:A29)=2)*1)


For column C
=SUMPRODUCT((WEEKDAY(A2:A29)=2)*(C2:C29))/SUMPRODUCT((WEEKDAY(A2:A29)=2)*1)


AP
www.megacrosstab.com

wrote:
I have a fairly data set of the number of products sold each day for
the past year.

In Column A, I have the days in question by row:
Row1: Monday 1/1/2005
Row2: Tuesday 1/2/2005
Row3: Wednesday 1/3/2005
and so on.

In Column B, I have the number of Product A sold on each day.
In Column C, I have the number of Product B sold on each day.

My Question:
Is there a way I can calculate the average number of products sold on
Mondays over the past year?

Any help would be GREATLY appreciated.

Calculating Daily Averages for Different Products
 

Thank you so much for responding!!!
And that does work for the problem I posted.
But I must confess I omitted one detail.

I actually have good sold per day AND TIME.
So my column A actually looks like:
Monay 1/1/2005 12:00 AM
Monday 1/1/2005 1:00 AM
and so on.

My true task is to get the number of goods sold on Monday @ 12:00AM.
I didn't think this detail would be important.
Increadibly sorry. :(

Is there another function I can use?
Or maybe I could create a function to something similar to the weekday
function?
Any ideas?

Thank you again for trying to help me.
--DW

AP wrote:
Here is one way.
Change the ending range appropriately, you may need the analysis
toolpak addin checked to get the weekday function.

For column B
=SUMPRODUCT((WEEKDAY(A2:A29)=2)*(B2:B29))/SUMPRODUCT((WEEKDAY(A2:A29)=2)*1)


For column C
=SUMPRODUCT((WEEKDAY(A2:A29)=2)*(C2:C29))/SUMPRODUCT((WEEKDAY(A2:A29)=2)*1)


AP
www.megacrosstab.com

Calculating Daily Averages for Different Products
 

Try this,

SUMPRODUCT((WEEKDAY(A2:A29)=2)*(B2:B29)*(HOUR(A2:A 29)=1))/SUMPRODUCT((WEEKDAY(A2:A29)=2)*(HOUR(A2:A29)=1)*1)


Place in the appropriate hour as needed.
Where 1 = 1:00 AM
0 = 12:00 AM
13 = 1:00 PM
etc


AP
www.megacrosstab.com

wrote:
Thank you so much for responding!!!
And that does work for the problem I posted.
But I must confess I omitted one detail.

I actually have good sold per day AND TIME.
So my column A actually looks like:
Monay 1/1/2005 12:00 AM
Monday 1/1/2005 1:00 AM
and so on.

My true task is to get the number of goods sold on Monday @ 12:00AM.
I didn't think this detail would be important.
Increadibly sorry. :(

Is there another function I can use?
Or maybe I could create a function to something similar to the weekday
function?
Any ideas?

Thank you again for trying to help me.
--DW

AP wrote:
Here is one way.
Change the ending range appropriately, you may need the analysis
toolpak addin checked to get the weekday function.

For column B
=SUMPRODUCT((WEEKDAY(A2:A29)=2)*(B2:B29))/SUMPRODUCT((WEEKDAY(A2:A29)=2)*1)


For column C
=SUMPRODUCT((WEEKDAY(A2:A29)=2)*(C2:C29))/SUMPRODUCT((WEEKDAY(A2:A29)=2)*1)


AP
www.megacrosstab.com