Chart to show event frequency

[kristoh]

I have a chart with event timestamps (2008-06-09 14:48:15.973) and two event types (A and B) with about 12000 events in total. I want to show a chart (probably line chart) that plots the frequency of events on a time axis (e.g. very many event A last night, not so many today, etc). I figure two time series with A and B independently so user can compare visually. But I dont know how to efficiently convert the events into a counter/frequency of events variable/column. Ive looked into DSUM, DCOUNT & FREQUENCY functions to make a new column that shows how many events have happened in the previous 10 minutes without success. DCOUNT/DSUM problem: some of the criteria fields are computed by formula (date column minus ten minutes), so i dont know how to make them contain '<', '='.
FREQUENCY problem: I need one result pr column for each of the 12000 events, how to get the array results from frequency presented in columns in stead of rows?

Any suggestions/alternative solutions/functions are welcome!

Thanks
<table x:str="" style="border-collapse: collapse; width: 115pt;" border="0" cellpadding="0" cellspacing="0" width="153"><col style="width: 115pt;" width="153"><col> <tbody></tbody><tbody><tr style="height: 12.75pt;" height="17"></tr></tbody></table>

 

[Serg811]

Hi,
if you making a chart i don't see the difference between holding data in columns or rows, anyway you can use the TRANSPOSE function for transforming data from columns to rows or vice versa.

 

[kristoh]

As far as I know, the max number of columns in excel is 256. This means I cant have my 12000 entries as columns in stead of rows.

 

[Serg811]

true, i don't understand whats the problem with frequency, it should do exacly what you need

 

[kristoh]

Thanks for the input!

As far as I understand, frequency() outputs an array of results in rows underneath the column with the formula after clicking on F2 and ctrl-shift-enter. These results are thus in rows underneath each other in the same column. As I want to do this computation for each row in my dataset, the results of row 1 would be hidden by the formula for row 2 etc. I guess an alternative would be to spread out the rows with empty rows in between to allow for the results of the above. But I would end up with only every thrid or fourth row giving a result I am interested in, and wouldn't I also have to do the F2 + ctrl-shift-enter procedure for each of the 11000 rows? I tried quickly on a dummy sheet, and selecting the whole column before the key-combination doesnt give the right result.

 

[Serg811]

FREQUENCY() essentially takes 2 arguments: a) data array b) bin array. For example if the data array consists of "1,2,3,4,5" and bin array consists of "2,4" then the output would be the array: "2,2,1" (because there are 2 elements below or equal to 2 in the data array, 2 elements larger than 2 but smaller or equal to 4 in the data array and only 1 element larger than 4 in the data array). The location of the output array can be anywhere you decide. Look at excel help, there's a very good example there.

 

[kristoh]

Thanks again.

You say the array can be placed wherever: Is it correct that the formula output doesnt have to be given in the cells directly beneath the cell containing the 'frequency' formula? E.g. cell B10 holds the frequency formula (and therefore result 1), while cells C12:D12 the next two results/cells in the array?

How to have several of these calculations without having to select the formula - press f2 - press ctrl+shift+enter - for each formula. I would need 11000 instances of this formula as the criteria would be different for each instance (10 min before the timestamp of this instance).

 

[Serg811]

Lets assume that the data array (the timestamps, ie: 01:03:00,01:07:34 etc) is in range A1:A12000.
You need to set up a second column, B (bin array), which will hold the "border" values. Since you intrested in 10 min periods the column should have values of 01:00,01:10,01:20... and so on. Lets assume that the range of these values is B1:B120 (The "bin array" column will be shorter than the data column). Then go to column C, select the range C1:C121 (range needs to be longer by 1 than the bin array range otherwise you'll miss one final result - number of events which occured after the last timestamp stated in column B), press F2, type =FREQUENCY(A1:A12000,B1:B120) and then finish with CTRL-SHIFT-ENTER. The formula will run on all the records in column A, compare them to the periods in column B and put the appropriate count numbers in column C.
One additional note, i don't know in what format are the timestamps in your sheet. If they are in text format you might have to convert them to time&date format for the formula to work.
HTH

 

[kristoh]

Thanks alot Serg811. Your solution works perfectly fine. It wasnt exactly what I had in mind, but it works like a dream

 

[Serg811]

you're welcome