Histogram data analysis tool

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Everett Joline - 12 May 2005 18:47 GMT

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

Reply to this Message

Mike Middleton - 12 May 2005 23:37 GMT

E-Jo -

Dates and times are stored internally as serial numbers, like 38597. The
display depends on how the cell is formatted. See Excel's built-in Help for
more explanations. I don't know how smart Excel's Histogram tool is
regarding the date-time-numbers. I have a free Better Histogram add-in that
you could try (from the Better Histogram page at www.treeplan.com), but it's
never been tested for your situation.

To delete a chart, select it (by clicking just inside the outermost border),
and press the Delete key.

- Mike
www.mikemiddleton.com

> 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).
[quoted text clipped - 16 lines]
> Thanks,
> E-Jo

Reply to this Message

Dave Peterson - 12 May 2005 23:43 GMT

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?

> In attempting to use the Data Analysis Tool histogram generator of Excel
> 2003, I can't get
[quoted text clipped - 18 lines]
> Thanks,
> E-Jo

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 13 May 2005 00:12 GMT

I read Histogram as =frequency(). Sorry.

> The easy stuff first.
>
[quoted text clipped - 87 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message

Everett Joline - 13 May 2005 21:28 GMT

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

>I read Histogram as =frequency(). Sorry.
>
[quoted text clipped - 97 lines]
>>
>> Dave Peterson

Reply to this Message

Dave Peterson - 13 May 2005 22:40 GMT

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.

> Well, actually I'm still having a problem, Dave.
> It still puts everything in the first box even though
[quoted text clipped - 122 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Everett Joline - 14 May 2005 00:49 GMT

No they didn't change, but I don't know what you mean by "plain old text".
Is that good or bad?
E-Jo

> The first thing I'd do is format those bin entries to:
> mm/dd/yyyy hh:mm
[quoted text clipped - 132 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 14 May 2005 02:19 GMT

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).

> No they didn't change, but I don't know what you mean by "plain old text".
> Is that good or bad?
[quoted text clipped - 140 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Everett Joline - 14 May 2005 19:23 GMT

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

>I mean that instead of numbers, you have text.
>
[quoted text clipped - 173 lines]
>> >
>> > Dave Peterson

Reply to this Message

Dave Peterson - 14 May 2005 19:38 GMT

Glad you got it working.

> It now seems to be working!
> I think my problem had to do with the fact that when
[quoted text clipped - 194 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Everett Joline - 13 May 2005 19:03 GMT

Thanks very much Mike and Dave for your well stated responses.

With your help I finally got it to work

E-Jo

> In attempting to use the Data Analysis Tool histogram generator of Excel
> 2003, I can't get
[quoted text clipped - 19 lines]
> Thanks,
> E-Jo

Reply to this Message