Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Sales Histogram
I am trying to chart the quantity of sales based on the number of
entries for the same date, eg 20/06/06, 20/06/06 would show in a chart as having a value of 2 for the date 20/06/06 and so on... I have tried the Data Analysis Tool and using a histogram, but its not listing all the dates I have nor is it showing them in date format, but as floating point values (which I can change by applying the date format). Also if I wanted to scale this up to say 1 week, 1 month, 1 year how would I do that? Thanks, JB |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Sales Histogram
Hi John,
It is possible to chart this with the histogram data analysis by explicitly setting a bin range that covers the full range of the dates used e.g from min(a:a) to max(a:a) inclusive. To have the bin increase day by day you could add 1 to the minimum. For example, assume the earliest date is 15/06/06 and the latest is 15/09/06, dates in the a column, and that the bin range is in the c column. In c1: =min(a:a) In c2: =c1+1 etc until 15.09.06 reached. To change days to weeks, months, etc. would be (say) in c2: =c1+7 copied down until max reached (or exceeded). The reason for setting the bin range is that if you use automatic bins Excel tries to allocate bins itself (which can lead to parts of days). Hth Anthony " wrote: I am trying to chart the quantity of sales based on the number of entries for the same date, eg 20/06/06, 20/06/06 would show in a chart as having a value of 2 for the date 20/06/06 and so on... I have tried the Data Analysis Tool and using a histogram, but its not listing all the dates I have nor is it showing them in date format, but as floating point values (which I can change by applying the date format). Also if I wanted to scale this up to say 1 week, 1 month, 1 year how would I do that? Thanks, JB |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Sales Histogram
That's perfect it did the trick!
On Oct 19, 12:26 pm, Anthony D wrote: Hi John, It is possible to chart this with the histogram data analysis by explicitly setting a bin range that covers the full range of the dates used e.g from min(a:a) to max(a:a) inclusive. To have the bin increase day by day you could add 1 to the minimum. For example, assume the earliest date is 15/06/06 and the latest is 15/09/06, dates in the a column, and that the bin range is in the c column. In c1: =min(a:a) In c2: =c1+1 etc until 15.09.06 reached. To change days to weeks, months, etc. would be (say) in c2: =c1+7 copied down until max reached (or exceeded). The reason for setting the bin range is that if you use automatic bins Excel tries to allocate bins itself (which can lead to parts of days). Hth Anthony " wrote: I am trying to chart the quantity of sales based on the number of entries for the same date, eg 20/06/06, 20/06/06 would show in a chart as having a value of 2 for the date 20/06/06 and so on... I have tried the Data Analysis Tool and using a histogram, but its not listing all the dates I have nor is it showing them in date format, but as floating point values (which I can change by applying the date format). Also if I wanted to scale this up to say 1 week, 1 month, 1 year how would I do that? Thanks, JB |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Sales Histogram
Glad to be of help, thanks for your feedback.
" wrote: That's perfect it did the trick! On Oct 19, 12:26 pm, Anthony D wrote: Hi John, It is possible to chart this with the histogram data analysis by explicitly setting a bin range that covers the full range of the dates used e.g from min(a:a) to max(a:a) inclusive. To have the bin increase day by day you could add 1 to the minimum. For example, assume the earliest date is 15/06/06 and the latest is 15/09/06, dates in the a column, and that the bin range is in the c column. In c1: =min(a:a) In c2: =c1+1 etc until 15.09.06 reached. To change days to weeks, months, etc. would be (say) in c2: =c1+7 copied down until max reached (or exceeded). The reason for setting the bin range is that if you use automatic bins Excel tries to allocate bins itself (which can lead to parts of days). Hth Anthony " wrote: I am trying to chart the quantity of sales based on the number of entries for the same date, eg 20/06/06, 20/06/06 would show in a chart as having a value of 2 for the date 20/06/06 and so on... I have tried the Data Analysis Tool and using a histogram, but its not listing all the dates I have nor is it showing them in date format, but as floating point values (which I can change by applying the date format). Also if I wanted to scale this up to say 1 week, 1 month, 1 year how would I do that? Thanks, JB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add Sales Goals to Sales Report in Pivot Table | Excel Discussion (Misc queries) | |||
figuring commissions based on gross sales | Excel Worksheet Functions | |||
sales needed to cover costs that increase as sales do | Excel Worksheet Functions | |||
Calculate Total Sales from a Database | Excel Worksheet Functions |