Sales Histogram

[john.bradshaw]

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

 

[Anthony D]

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

 

[john.bradshaw]

That's perfect it did the trick!

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

 

[Anthony D]

Glad to be of help, thanks for your feedback.

That's perfect it did the trick!