Number of items per day in chart.

  • Thread starter Thread starter Web master
  • Start date Start date
W

Web master

Hello all, I have a list of dates and times ( listed as 1/18/2006 1:30:10
AM in cell) that indicate a sale that took place. What I am trying to do is
create a chart from this list that indicate the number of sales per day. I
would like the chart to have the days of the month accross the bottom and a
bar indicating how many sales per that day. Can anybody help me with this?
Whenever i try and use the bar chart it shows me a cumulative total
(includes the previous sales from previous days).

Also, I am using Excel 2003.

Any help would be appreciated,

Ian
 
Let's assume you data in A1:500 in the form 1/18/2006 1:30:10

In B1 enter 1/Jan/2006 and in B2 2/Jan/2006 (I am using dates like this to
avoid problems with US v European dates, but you could use 1/1/2006 and
1/2/2006)
Select these two cells and drag the fill handle of B2 (little black square
in lower right corner) down to B31 to give a date for each day of January
(continue down if you have more moths)

In C1 enter the formula =SUMPRODUCT(--(INT($A$1:$A$500)=B1)) This will count
how many cells in A have 1/Jan as date.

Copy this down the C column

Make chart using B1:C31. You can format the axis to suit you needs; I would
make major tick marks every 2 days

best wishes
 
In addition to Bernard's formula approach, you could set up a pivot chart.
For this simple example a pivot table is probably overkill, but it's worth
learning how pivot tables work.

Select the column of dates, and choose Pivot Table and Pivot Chart Reports
from the Data Menu. Go through the dialog, selecting all of the defaults.
You will be faced with a blank pivot table on a new sheet. Drag the Time
field from the Field List to the Row Fields area, then drag it again from
the Field List, this time to the Data Items area. Big deal, a list like you
started with, but with a column of 1's next to it.

Right click on the Time field button in the pivot table. In the pop up menu,
choose Group and Show Detail, then select Group. In the list box, unselect
Months and select Days. Now the pivot table changes to a list of dates and
number of sales for each date. You can make a pivot chart by selecting a
cell in the pivot table and clicking on the Chart Wizard icon. You can also
make a regular chart by selecting a blank cell away from the pivot table and
running the chart wizard; in step one select the chart type, in step two
click on the Series tab, then click on Add, and enter the appropriate
information in the Name, Values, and Category Labels boxes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top