Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a function that calculates time between 2 dates | Excel Worksheet Functions | |||
Function that compares dates | Excel Worksheet Functions | |||
Using which function to calculatiing 2 set of numbers of days from 3 dates | Excel Discussion (Misc queries) | |||
Using IF function with Dates as parameters | Excel Worksheet Functions | |||
How do I use the excel timeline function with dates in graphs | Excel Worksheet Functions |