Filled graph of usage frequencies

guthrie

New Member
Joined
Jun 14, 2007
Messages
27
I have a list of event times with associated users.

tg0.png


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.

tg1.png


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!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
241
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

New Member
Joined
Jun 14, 2007
Messages
27
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

Board Regular
Joined
Sep 25, 2012
Messages
241
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

New Member
Joined
Jun 14, 2007
Messages
27
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,355
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top