Tasmant
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
An interesting thing I learned today is:
You can have a dynamic range over formulas (not just values) like this
=OFFSET(Sheet1!$F$1,,,COUN TIFS(Sheet 1!$F:$F,"< >",Sheet1! $F:$F,">0" ),1)
provided F contains numbers.
You can have a dynamic range over formulas (not just values) like this
=OFFSET(Sheet1!$F$1,,,COUN
provided F contains numbers.
@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,TimeCh art.xlsm!P eriod,Time Chart.xlsm !Values,1)
Also, an alternative for defining the dynamic range is
=Sheet1!$F$2:INDEX(Sheet1! $F:$F,COUN T(Sheet1!$ F:$F)+1)
cheers, teylyn
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,TimeCh
Also, an alternative for defining the dynamic range is
=Sheet1!$F$2:INDEX(Sheet1!
cheers, teylyn
teylyn thx again :) .... new learning everyday :)
(but alas... and forgetting a few good ones too!)
(but alas... and forgetting a few good ones too!)
ASKER
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 !
I split points as each solution is very interesting, but for me each of you should have 1000 points !
only one line of macro in the code to reset the chart.
BTW, the line could also be
Target.Worksheet.ChartObje