Histogram data analysis tool

[Everett Joline]

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

 

[Mike Middleton]

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

 

[Dave Peterson]

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?

 

[Dave Peterson]

I read Histogram as =frequency(). Sorry.

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]

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

With your help I finally got it to work

E-Jo

 

[Everett Joline]

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]

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

 

[Everett Joline]

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]

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?
E-Jo

 

[Everett Joline]

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]

Glad you got it working.

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