Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of items in one column that have a value in another? | Excel Worksheet Functions | |||
"propagating" a chart across a number of data sets | Charts and Charting in Excel | |||
PERMUT function | Excel Worksheet Functions | |||
Chart Legend Items: hide/show | Charts and Charting in Excel | |||
Chart items moving around | Excel Discussion (Misc queries) |