Re: Histogram data analysis tool

It now seems to be working!
I think my problem had to do with the fact that when
I formatted a date-time with Format it changed
09/02/2005 01:36

to
2/9/05 1:36

Anyway I did an identical reformat on all the applicable data based
on your suggestion and it is now OK. (But it is still confusing
to me to deal with all those different formats. In one case Month is in
the first place, and in another case it is in the second place. However,
I guess the base quantity remains the same in both cases.)

Thanks Dave for hanging in with such a dumbo!
E-Jo

"Dave Peterson" <ec35720@xxxxxxxxxxxxxxxxx> wrote in message
news:42855237.449F07D1@xxxxxxxxxxxxxxxxxxxx
>I mean that instead of numbers, you have text.
>
> Kind of the difference between '123 (with the leading quote) and 123 (as a
> number).
>
> Try selecting the range and then
> edit|replace
> what: / (slash)
> with: / (slash)
> replace all.
>
> When I did it, excel reevaluated the string and made it real numbers--I
> didn't
> have any leading/trailing spaces in the cell before I did it, too.
>
> Another option:
> Since your "date/times" are all in one column.
> Select the range
> Data|text to columns|Finish
>
> That converted the strings to dates/times for me, too.
>
> And if worse comes to worse, try typing in the date/times again (with the
> cells
> formatted as nice dates/times).
>
> Everett Joline wrote:
>>
>> No they didn't change, but I don't know what you mean by "plain old
>> text".
>> Is that good or bad?
>> E-Jo
>>
>> "Dave Peterson" <ec35720@xxxxxxxxxxxxxxxxx> wrote in message
>> news:42851ED9.D5640D3F@xxxxxxxxxxxxxxxxxxxx
>> > The first thing I'd do is format those bin entries to:
>> > mm/dd/yyyy hh:mm
>> >
>> > If they don't change, then your date/time entries are just plain old
>> > text.
>> >
>> > Everett Joline wrote:
>> >>
>> >> Well, actually I'm still having a problem, Dave.
>> >> It still puts everything in the first box even though
>> >> my bin entries are like:
>> >>
>> >> 9/2/05 0:00
>> >> 9/2/05 1:00
>> >> 9/2/05 2:00
>> >> etc.
>> >>
>> >> And the data is like:
>> >> 09/02/2005 00:12
>> >> 09/02/2005 01:54
>> >> 09/02/2005 03:17
>> >> etc.
>> >>
>> >> 1. Is there anything wrong with this data?
>> >> 2. Does your method work when you bring up the Histogram
>> >> dialog and enter the numbers in that?
>> >> Thanks,
>> >> E-Jo
>> >>
>> >> "Dave Peterson" <ec35720@xxxxxxxxxxxxxxxxx> wrote in message
>> >> news:4283E2F0.7E393AAE@xxxxxxxxxxxxxxxxxxxx
>> >> >I read Histogram as =frequency(). Sorry.
>> >> >
>> >> > Dave Peterson wrote:
>> >> >>
>> >> >> The easy stuff first.
>> >> >>
>> >> >> Put 38597 into an empty cell and format it as a date. You'll see
>> >> >> how
>> >> >> excel
>> >> >> works with dates--just a number of days from a base date (usually
>> >> >> 12/31/1899 for
>> >> >> wintel users).
>> >> >>
>> >> >> And 38597 formatted as a date will show Sept 2, 2005.
>> >> >>
>> >> >> I put this in A1:A96:
>> >> >> 09/02/2005 00:00:00
>> >> >> 09/02/2005 00:15:00
>> >> >> 09/02/2005 00:30:00
>> >> >> 09/02/2005 00:45:00
>> >> >> 09/02/2005 01:00:00
>> >> >> 09/02/2005 01:15:00
>> >> >> 09/02/2005 01:30:00
>> >> >> 09/02/2005 01:45:00
>> >> >> 09/02/2005 02:00:00
>> >> >> ...
>> >> >> 09/02/2005 22:00:00
>> >> >> 09/02/2005 22:15:00
>> >> >> 09/02/2005 22:30:00
>> >> >> 09/02/2005 22:45:00
>> >> >> 09/02/2005 23:00:00
>> >> >> 09/02/2005 23:15:00
>> >> >> 09/02/2005 23:30:00
>> >> >> 09/02/2005 23:45:00
>> >> >>
>> >> >> (96 entries)
>> >> >>
>> >> >> I put this in B1:B13:
>> >> >> 09/02/2005 00:00
>> >> >> 09/02/2005 02:00
>> >> >> 09/02/2005 04:00
>> >> >> 09/02/2005 06:00
>> >> >> 09/02/2005 08:00
>> >> >> 09/02/2005 10:00
>> >> >> 09/02/2005 12:00
>> >> >> 09/02/2005 14:00
>> >> >> 09/02/2005 16:00
>> >> >> 09/02/2005 18:00
>> >> >> 09/02/2005 20:00
>> >> >> 09/02/2005 22:00
>> >> >> 09/03/2005 00:00
>> >> >>
>> >> >> I array entered this in C1:C13:
>> >> >> =FREQUENCY(A1:A96,B1:B12)
>> >> >>
>> >> >> I got this result in C1:C13:
>> >> >> 1
>> >> >> 8
>> >> >> 8
>> >> >> 8
>> >> >> 8
>> >> >> 8
>> >> >> 8
>> >> >> 8
>> >> >> 8
>> >> >> 8
>> >> >> 8
>> >> >> 8
>> >> >> 7
>> >> >>
>> >> >> It seemed to work ok for me. Were you putting date and time in
>> >> >> your
>> >> >> cells?
>> >> >>
>> >> >> Everett Joline wrote:
>> >> >> >
>> >> >> > In attempting to use the Data Analysis Tool histogram generator
>> >> >> > of
>> >> >> > Excel
>> >> >> > 2003, I can't get
>> >> >> > it to work right (i.e., the way I want it to work).
>> >> >> >
>> >> >> > I suspect that I have some sort of DATE HHMMSS problem, and one
>> >> >> > confusing
>> >> >> > thing is that the value
>> >> >> > displayed on the fx input bar is not the same format as the cell
>> >> >> > I'm
>> >> >> > trying
>> >> >> > to edit.
>> >> >> > For instance when the cell contains 9/2/2005 1:00 the fx bar
>> >> >> > displays
>> >> >> > 9/2/2005 1:00:00 AM.
>> >> >> >
>> >> >> > So when I enter a series of times on 9/2/2005, 30 mins apart in
>> >> >> > the
>> >> >> > bin
>> >> >> > range all of the data
>> >> >> > is counted as being in the first 30 mins, rather than distributed
>> >> >> > over
>> >> >> > the
>> >> >> > day (as it actually is).
>> >> >> >
>> >> >> > Also, a mysterious number 38597 shows up at the top of the echoed
>> >> >> > bin
>> >> >> > range.
>> >> >> >
>> >> >> > Finally, how do I delete all the incorrect diagrams I have
>> >> >> > generated?
>> >> >> >
>> >> >> > Thanks,
>> >> >> > E-Jo
>> >> >>
>> >> >> --
>> >> >>
>> >> >> Dave Peterson
>> >> >
>> >> > --
>> >> >
>> >> > Dave Peterson
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson


.

Relevant Pages

  • Excel 07 - text displays as pound signs
    ... Excel 07 - text displays as pound signs ... "Dave Peterson" wrote: ... any given cell will suddenly appear as all pound signs. ... changed the format to a date. ...
    (microsoft.public.excel.newusers)
  • Re: Excel 07 - text displays as pound signs
    ... If I typed a number in that cell, ... surprised seeing the scientific format. ... Excel 07 - text displays as pound signs ... "Dave Peterson" wrote: ...
    (microsoft.public.excel.newusers)
  • Re: Convert date to serial number
    ... What's the numberformat for that cell that gets the value? ... MsgBox "datecode with shift is " & DateAndShift ... the MsgBox gives the date and shift code in the format I ... "Dave Peterson" wrote: ...
    (microsoft.public.excel.programming)
  • Re: Convert the date to day of the week.
    ... "Dave Peterson" wrote: ... You could record a macro to select the column and then data|text to columns. ... date format that you want. ... There has to be a better way to do a conversion. ...
    (microsoft.public.excel.programming)
  • Re: Import data into seperate columns
    ... "Dave Peterson" wrote: ... utilizing the WEBMD_NSF Format. ... currently work in Patient Check-in. ... By presenting this alert when the patient is chosen on the check in ...
    (microsoft.public.excel.misc)