Avatar of Tasmant
TasmantFlag for France asked on

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
Microsoft Excel

Avatar of undefined
Last Comment
Tasmant

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
TommySzalapski

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gbanik

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")
gbanik

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.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

@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
Your help has saved me hundreds of hours of internet surfing.
fblack61
gbanik

teylyn thx again :)  .... new learning everyday :)
(but alas... and forgetting a few good ones too!)
ASKER
Tasmant

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 !