FREQUENCY and dates

[sean.winship]

Hi,

I'm doing some performance analysis and have a large number of
timestamp (formatted as dd/mm/yyyy hh:mm:ss) / transaction duration
pairs in a spreadsheet. I'm trying to create a histogram showing
number of transactions each hour, but when I use the FREQUENCY function
I get very small numbers associated with each timestamp "bin".

Am I missing something fundamental about the way Excel handles dates?

After that I need a similar plot of average duration each hour or 30
minutes. I don't see any way to force FREQUENCY to do that.

Related to this, I tried selecting the two columns of data and
generating several different kinds of charts, including scatter plots.
Excel did not behave as I would expect, the plots were nearly entirely
black with strange values like 01/01/1900 on the axes.

I'm a programmer, not a spreadsheet wizards, so please excuse me if I'm
missing something obvious.

Thanks,

Sean

 

[sean.winship]

I'm doing some performance analysis and have a large number of

timestamp (formatted as dd/mm/yyyy hh:mm:ss) / transaction duration
pairs in a spreadsheet. I'm trying to create a histogram showing
number of transactions each hour, but when I use the FREQUENCY function
I get very small numbers associated with each timestamp "bin".

Naturally, I figured out my problem right after posting. Debugging by
public embarassment works. I am still having problems with the
following, though:

After that I need a similar plot of average duration each hour or 30
minutes. I don't see any way to force FREQUENCY to do that.

Any thoughts?

Thanks,

Sean

 

[Pete_UK]

I assume you have a start date and time in column A and an end date and
time in column B (or the other way round), so you can obtain a duration
for each by subtracting one from the other and formatting as [h]:mm:ss.
By "average duration", do you mean that you want to add up all the
durations which started in a particular half hour slot and take the
average of those? Then repeat this for all the half hour slots in a
day?

Pete

 

[sean.winship]

I assume you have a start date and time in column A and an end date and
time in column B (or the other way round), so you can obtain a duration
for each by subtracting one from the other and formatting as [h]:mm:ss.
By "average duration", do you mean that you want to add up all the
durations which started in a particular half hour slot and take the
average of those? Then repeat this for all the half hour slots in a
day?

Exactly. Thanks for restating it more clearly.

Regards,

Sean

 

[sean.winship]

I assume you have a start date and time in column A and an end date and
time in column B (or the other way round), so you can obtain a duration
for each by subtracting one from the other and formatting as [h]:mm:ss.
By "average duration", do you mean that you want to add up all the
durations which started in a particular half hour slot and take the
average of those? Then repeat this for all the half hour slots in a
day?

Exactly. Thanks for restating it more clearly.

The solution I came up with is:

{=AVERAGE(IF($A$2$A$12256<H12,IF($A$2:$A$12256>H11,$C$2:$C$12256)))}

where the timestamps are in column A, the durations in column C, and my
frequency bins in column H.

This is more than I wanted to know about Excel.

Sean