Solved

Number of events by day and hour

Posted on 2010-11-11
8
413 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:Tasmant
8 Comments
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 167 total points
Comment Utility
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
 
LVL 50

Assisted Solution

by:teylyn
teylyn earned 167 total points
Comment Utility
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
 
LVL 13

Assisted Solution

by:gbanik
gbanik earned 166 total points
Comment Utility
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
 
LVL 13

Expert Comment

by:gbanik
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Expert Comment

by:gbanik
Comment Utility
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
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
@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
 
LVL 13

Expert Comment

by:gbanik
Comment Utility
teylyn thx again :)  .... new learning everyday :)
(but alas... and forgetting a few good ones too!)
0
 
LVL 11

Author Closing Comment

by:Tasmant
Comment Utility
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now