I have a 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.

8/1/2006 2:17:19 PM

in column d, make all cells =weekday(A1) through =weekday(A10)

then in an empty cell, do =SUMIF(D1: D10,"=2",B1:B10)/COUNTIF(D1: D10,"=2")

you can hide column d if you don't want it to show]

[Edited on August 1, 2006 at 3:04 PM. Reason : 1-10 or however many rows you have]

8/1/2006 2:42:06 PM

If you don't mind, could you walk me through that one?
I'm a little lost.

8/1/2006 2:46:17 PM

Never mind. I see it.
Thanks.
You're right. That does work.
But there is something I didn't say in my first post.
I have output for goods sold at a SPECIFIC TIME.
So my Column A actually looks like:

Monay 1/1/2005 12:00 AM
Monday 1/1/2005 1:00 AM

I was hoping the solution would work without having to explain that far. Sorry

Can you still help me!!??

[Edited on August 1, 2006 at 2:55 PM. Reason : ```]

8/1/2006 2:52:46 PM

first, i would create a column (new column between A and B) and just put the days in there by using the formula
=weekday(A1)
that will return 1-7, corresponding to Sun-Saturday (be sure the column is formatted as "number" for the number to show up). If you want that column to show Sunday, Mon, Tue, etc, then create a small lookup table somewhere like
1 Sunday
2 Monday
3 Wed
etc. then instead of =weekday(A1), use
=VLOOKUP(WEEKDAY(A1),$A$26:$B$32,2) [where A26:B32 is the day lookup table, whereever you happen to put it]

then to get the averages, there are several ways.
The sleekest would be to use 1) a pivot table, or 2) array formulas.

Pivot table - highlight all the cells with the days and the products (columns must have headers) and go to Data > Pivot table. Go through the wizard, and you'll get a blank pivot table. In the Field List, drag "Day" into the "Row Fields", then the product (a or b) into the "data items". By default, it will now show the sum of all products sold for each day. In the upper left corner of the pivot table it will say "sum of a". Double click that, then select "summarize by:" and choose "average". Then it will show you the average of product a. you can copy/paste that data out, then drag "average of a" out o fthe table, and drop "b" in there and do the same.

Pros: very easy and fast to do once you get the hang of pivot tables
cons:
data has to be manually refreshed if changed (Pivot Table > Refresh Data)
can only see one product at a time


Array formula - also create the column with just the day name. Assume for now that the data is in rows 2-9 (not much data....), and the full date is in col A, day only in col B, product a in col C and product b in col D
at the bottom of the data table, make a small table where the results will be stored. In col B, write the days of the week
Sunday
Monday
etc

In col D corresponding to Sunday, type the following formula:
=AVERAGE(SUMIF($B$2:$B$9,"="&$B11,C$2:C$9)/COUNTIF($B$2:$B$9,"="&$B11))

NOW THIS IS IMPORTANT - to make this an "array formula", after you type the formula in, click Control+Shift+Enter.
After you hit enter, and you go back and look inside the fomula, you'll see { } surrounding the formula. This makes it an "array formula" and performns the SUMIF and COUNTIF operation across the whole array. Anytime you double-click in that formula and edit it, you have to press Ctrl+Shift+Enter again.
Now drag that formula over for all the products you have, then down for all 7 days and the table will be populated.

Pros:
data is automatically recalculated dynamically when the table changes.
Can show average of all products at once
Cons: formulas can be cumbersome and error prone

shit, don't need array formulas (i was just itching to use them!). just use the SUMIF and COUNTIF

[Edited on August 1, 2006 at 2:56 PM. Reason : .]

^ the time doesn't matter - the =weekday() formula just strips the day out of the whole date/time string.

[Edited on August 1, 2006 at 2:57 PM. Reason : .]

8/1/2006 2:54:40 PM

^ haha , I like array formulas, I always try to find a way to use them even when it isn't necessary

8/1/2006 3:01:59 PM

array formulas are nifty, but a pain in the ass

Quote :

"the time doesn't matter - the =weekday() formula just strips the day out of the whole date/time string."

[Edited on August 1, 2006 at 3:08 PM. Reason : unless you guys know how to use fill w/ array formulas?]

8/1/2006 3:02:31 PM

agent,

So I'm learning.
But my true task is to find out the number of products sold per day AND TIME throughout the year.
So I need to find out how many products were sold on Mondays at 12:00 AM.
I didn't think this little detail would mater.

So you're 100% right.
But is there a way I can do it with times?
Sorry for not including this sooner.

8/1/2006 3:20:07 PM

ok, then you'll need another column using =hour() to extract the hour from the date string.
and in this case you'll definately want to use a Pivot Table because you'll be dealing with a very large amount of information (7 days * 24 hours). import all 4 columns into the Pivot Table (Day, Hour, A, B, whatever other products you have).
Then in the Row Fields, first drop Days in there, then drop Hours to the right of days, then the products into the Data Fields. Set the Data to calculate Average, and it will group all averages per hour per day


And a great thing about the Pivot Table is that with one Click you can create a Pivot Chart, which will graph all this data for you in a very easy to read format.

[Edited on August 1, 2006 at 3:37 PM. Reason : .]

8/1/2006 3:36:56 PM

It looks like it work perfectly!!

Thanks so much.
But how do I set the Pivot Table to give me averages?

8/1/2006 5:07:38 PM

the upper left corner of the table will show the name of the data in the main data fields. It probably says "Sum of [product]" right now. It kind of looks like a button - double click it, then select "average" in the Summarize By list.
Be sure to click the Pivot Chart button to see what it looks like in a graph

8/1/2006 5:11:05 PM

hmmm. It doesn't look a like a button right now.
Does it matter if I have non-numeric data in my table?
What about blank cells?

8/1/2006 5:45:45 PM

the pivot table should look similar to this



in the upper left is the data field settings. kinda looks like a button... i dunno. double click it to get to the settings.

non-numeric and blank fields in the data columns will be ignored.
In the Row columns (day, time) you can selectively choose which values to include in the table. Click the arrow next to the field name and check/uncheck any values you don't want to show up




using pivot tables is anything but intuitive, but once you get the hang of the basics, just start clicking around, double clicking on different cells in the table, and you'll start to find more features

8/1/2006 10:25:28 PM

use sql and matlab

8/1/2006 10:47:05 PM

It works perfectly!
Thanks for helping me, agent!
And for putting up with my stupid questions.

8/2/2006 5:03:04 AM