Filled graph of usage frequencies

[guthrie]

I have a list of event times with associated users.

I want to make a graph which plots per minute the number of users with each count category of events (1,2,3,4,5+).

The result would be a graph like this, with 5 levels per minute.

I am trying a sample test now, but the actual data is huge (between 300k-800k).

One approach is to make a big auxiliary table, for every minute (m), and rows of users (*n), and count events in that minute for that user, and then form the 5 summary columns.

But this is huge, and seems overly explicit.

Any ideas on a better (formulaic) solution?

(And VBA may be the best idea, but I don't know that domain!)

And maybe this is just too big for exel without VBA, and I should do externally and import the results for plotting!

 

[petertenthije]

You could create a pivot table on the data, and use the pivot table for the graph.

But with the amount of records you have, you might want to consider using Access?

 

[guthrie]

Thanks.
I'll have to research how a pivot table would solve this.
Yes big data, but this is a one-time thing so I don't care if it runs overnight! I made a one-week sample of the annual data, it is only 34k, am trying to prototype on that.
I will also look into a programmatic approach; I am not familiar with Access. The data actually comes from a SQL query, maybe there is a solution in that also - not my domain.
The big data is an issue. I had a dynamic range setup for it, and a counta() for size, and those were enough to freeze Excel for an hour or more, and I had to kill the process! (I simplified and replaced the dynamic range.. made the sample data feasible)

 

[petertenthije]

It's really quite simple.

Select all your data, and then go to the INSERT tab and select pivot table.

Drag the columnheader with the dates (column A) into the field named ROWS.
Drag the columnheader with the data (column B) into the field named COLUMNS. Drag the data also in the field named VALUES.

By default the VALUES field will show the SUM of your data. Click on it and select VALUE FIELD SETTINGS. From this screen you can change the settings from SUM to COUNT.

This will show you the data in pivot table form. If you now go to the tab ANALYZE, you can select the option to createa a PivotChart. This will give you the graph you are looking for.

 

[guthrie]

Thanks - will try this. It turns our there is some more aggregated data inthe database, and I can work from that and it is a lot simpler!