Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
Web master
 
Posts: n/a
Default Number of items per day in chart.

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


  #2   Report Post  
Posted to microsoft.public.excel.charting
Bernard Liengme
 
Posts: n/a
Default Number of items per day in chart.

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


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Web master" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.charting
Jon Peltier
 
Posts: n/a
Default Number of items per day in chart.

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/
_______

"Bernard Liengme" wrote in message
...
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


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Web master" wrote in message
...
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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count number of items in one column that have a value in another? onthefritz Excel Worksheet Functions 5 December 10th 05 05:19 PM
"propagating" a chart across a number of data sets rgraham1 Charts and Charting in Excel 5 October 23rd 05 04:10 PM
PERMUT function Celeste Excel Worksheet Functions 2 October 3rd 05 08:11 PM
Chart Legend Items: hide/show Richard Ahlvin Charts and Charting in Excel 5 September 5th 05 03:04 PM
Chart items moving around Marcus Langell Excel Discussion (Misc queries) 3 August 12th 05 04:10 PM


All times are GMT +1. The time now is 07:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"