distribution of dates via HIST Function
 

I have a single column of dates in this format:e.g.,

9/25/2006 18:06
9/25/2006 18:32
9/26/2006 13:06
9/28/2006 15:45

I used then something like Value (Left(cell,5) to get a numeric value of
38985 for the date of 9/25/2006

All appears to be good at this stage.

I then used the HIST function to get a distribution cout of the dates, and I
get this weird output:


Bin Frequency
38985 26
38985.16667 0
38985.33333 0
38985.5 0
38985.66667 0
38985.83333 0
More 12

Why is the Bin column still showing snippets of the timestamp..?

thanks in advance

distribution of dates via HIST Function
 

Hi Magellan,

Could it be that you are not manually setting the bin numbers but are
letting the histogram function calculate them ?
It seems that with automatic calculation of the bins 'Excel creates a set
of evenly distributed bins between the data's minimum and maximum values' -
although it also looks as if not all values are always included in the
automatic bin range.
Perhaps manual creation of bins will solve this, such as
38984
38985
38986
38987
38988
etc. ?

Anthony


"magellan" wrote:

I have a single column of dates in this format:e.g.,

9/25/2006 18:06
9/25/2006 18:32
9/26/2006 13:06
9/28/2006 15:45

I used then something like Value (Left(cell,5) to get a numeric value of
38985 for the date of 9/25/2006

All appears to be good at this stage.

I then used the HIST function to get a distribution cout of the dates, and I
get this weird output:


Bin Frequency
38985 26
38985.16667 0
38985.33333 0
38985.5 0
38985.66667 0
38985.83333 0
More 12

Why is the Bin column still showing snippets of the timestamp..?

thanks in advance

distribution of dates via HIST Function
 

Anthony...

that solved it, by filling in the BIN column.....thanks so much...:-) You
Rock!

"Anthony D" wrote:

Hi Magellan,

Could it be that you are not manually setting the bin numbers but are
letting the histogram function calculate them ?
It seems that with automatic calculation of the bins 'Excel creates a set
of evenly distributed bins between the data's minimum and maximum values' -
although it also looks as if not all values are always included in the
automatic bin range.
Perhaps manual creation of bins will solve this, such as
38984
38985
38986
38987
38988
etc. ?

Anthony


"magellan" wrote:

I have a single column of dates in this format:e.g.,

9/25/2006 18:06
9/25/2006 18:32
9/26/2006 13:06
9/28/2006 15:45

I used then something like Value (Left(cell,5) to get a numeric value of
38985 for the date of 9/25/2006

All appears to be good at this stage.

I then used the HIST function to get a distribution cout of the dates, and I
get this weird output:


Bin Frequency
38985 26
38985.16667 0
38985.33333 0
38985.5 0
38985.66667 0
38985.83333 0
More 12

Why is the Bin column still showing snippets of the timestamp..?

thanks in advance

distribution of dates via HIST Function
 

Thank you Magellan,
Glad to be of help :-)

"magellan" wrote:

Anthony...

that solved it, by filling in the BIN column.....thanks so much...:-) You
Rock!

"Anthony D" wrote:

Hi Magellan,

Could it be that you are not manually setting the bin numbers but are
letting the histogram function calculate them ?
It seems that with automatic calculation of the bins 'Excel creates a set
of evenly distributed bins between the data's minimum and maximum values' -
although it also looks as if not all values are always included in the
automatic bin range.
Perhaps manual creation of bins will solve this, such as
38984
38985
38986
38987
38988
etc. ?

Anthony


"magellan" wrote:

I have a single column of dates in this format:e.g.,

9/25/2006 18:06
9/25/2006 18:32
9/26/2006 13:06
9/28/2006 15:45

I used then something like Value (Left(cell,5) to get a numeric value of
38985 for the date of 9/25/2006

All appears to be good at this stage.

I then used the HIST function to get a distribution cout of the dates, and I
get this weird output:


Bin Frequency
38985 26
38985.16667 0
38985.33333 0
38985.5 0
38985.66667 0
38985.83333 0
More 12

Why is the Bin column still showing snippets of the timestamp..?

thanks in advance