Number of events by day and hour

Hi all,

I would like to set up a graph with number of events by hour and by day.
I don't know how set up data on the spredsheet to do it easily.

Formula and or VBA welcome.
If you have some ideas to present such data, it's welcome too.
I also wanted to establish a chronological curve with dates and times that would allow me to see if the same pattern is replicated every day, but don't know to do it ...
excelgraphexpected.JPG
LVL 11
TasmantAsked:
Who is Participating?
 
TommySzalapskiCommented:
I would do it in line charts like in my example spreadsheet. (My graph looks random because my data was, in fact, random)
Events-per-hour-chart.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

see attached for a suggestion.

I've created some dummy data for a few days and times, then built three pivot charts.

One pivot chart (on sheet Chart1) totals all events by day, the other pivot chart (on Chart2) groups by hour. You can select an individual day from the dropdown in the chart, or select all days (or just some days)

The third pivot chart (Chart 3) totals all times into their hour slot, regardless of which day, so you see which hours have the most events in total.

Does that give you a starting point?

cheers, teylyn
Book1.xls
0
 
gbanikCommented:
Check this file.... tell me if you like it.

Fill in the yellow cell with values from 0 to 1 corresponding to 0 to 24 hrs... example, for blocks of 12 hours each type 0.5

Sorry could not post any code... cos there is nothing much. Keep the macros on!

(But I am happy with the output.)
TimeChart.xlsm
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
gbanikCommented:
Do tell me if the above is a good direction...
only one line of macro in the code to reset the chart.

BTW, the line could also be
Target.Worksheet.ChartObjects("PeriodChart").Chart.SetSourceData Source:=Range("Period", "Values")
0
 
gbanikCommented:
An interesting thing I learned today is:
You can have a dynamic range over formulas (not just values) like this
=OFFSET(Sheet1!$F$1,,,COUNTIFS(Sheet1!$F:$F,"<>",Sheet1!$F:$F,">0"),1)
provided F contains numbers.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@Gbanik,

You don't need a macro to make a chart dynamic. You can plug the dynamic range names straight into the series formula.

=SERIES(Sheet1!$G$1,TimeChart.xlsm!Period,TimeChart.xlsm!Values,1)

Also, an alternative for defining the dynamic range is

=Sheet1!$F$2:INDEX(Sheet1!$F:$F,COUNT(Sheet1!$F:$F)+1)

cheers, teylyn
0
 
gbanikCommented:
teylyn thx again :)  .... new learning everyday :)
(but alas... and forgetting a few good ones too!)
0
 
TasmantAuthor Commented:
Thanks guys you're really amazing.
I split points as each solution is very interesting, but for me each of you should have 1000 points !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.