3 Excel ninjas chart

[cacos]

Would love to make this same chart but using a month-over-month dynamic, instead of weekly. I've been playing with the file for a while but couldn't understand it enough to change this.


Any kind soul that knows of a simple way of plotting the data this way?


Thanks!

 

[Luke M]

Hi cacos!


First, for other readers, reference here

On the calculations sheet, check out cell C4. The formula is doing a COUNTIFS based on two criteria.

=COUNTIFS(tblPosts[poster_id],C$2,tblPosts[post_time],">="&(C$3+($B4-1)*7),tblPosts[post_time],"<"&(C$3+($B4)*7))


Looking at the criteira, it's taking the week number and multiplying by 7 (7 days to a week) to generate the correct count. To adapt to months, and without having to redesign everything, let's instead think of col B as being "months after" rather than weeks after. For this, using the EOMONTH function will help us out, as it generates a date given start date and # of months to progress. New formula then is:

=COUNTIFS(tblPosts[poster_id],C$2,tblPosts[post_time],">="&EOMONTH(C$3,$B4-1),tblPosts[post_time],"<"&EOMONTH(C$3,$B4))


Similarly, in cell O5, change formula form:

=IF(C$3+$B4*7>dumpDate,NA(),O4+C5)

to this:

=IF(EOMONTH(C$3,$B4)>dumpDate,NA(),O4+C5)


After that, copy both formula to the right and down as appropriate.

 

[cacos]

Thank you Luke! I'll try it out and let you know

 

[cacos]

And is there a quick easy way to just have data that has x values for 12 months, and see the progression based on that? No weeks, just month over month.


e.g.;


Month Sales

Jan 20

Feb 30

Mar 10

Apr 5

May 60

 

[Luke M]

You could probably add it as a criteria in the first formula, as another COUNTIFS criteria, if I'm understanding correctly.

 

[cacos]

I mean if the data is only that, just the 12 months and sales for each month, no complex weak over weak calculation, no posters, just those 2 columns. Making the code plot dynamically on the chat as on Chandoo's file

 

[NARAYANK991]

Hi ,


If I have understood you correctly , you want to know how the chart plots the points dynamically ?


This is done by defining the series in terms of a continuously increasing number of rows ; for example , if we take one of the series , say SrsHui , its definition is as follows :


=OFFSET(calculations!$O$4,,,calculations!$O$1,1)


In this definition , the parameter calculations!$O$1 is the height of the range in number of rows.


To manipulate this value , the following named range is defined :


valCounter referring to : =calculations!$O$1


The rest of the work is done by two macros :

Sub resetChart()
[valCounter] = 1
End Sub
initializes the value to 1 , so that the series starts at the origin.

Sub updateChart()
'dynamic chart macro
Dim cntr As Integer

For cntr = 1 To 140
[valCounter] = cntr
DoEvents
Next cntr

End Sub

increments the value by 1 at a periodic frequency , which animates the end of the series as it traces its way from a single point till all the 140 points are plotted.


Just BTW , there was an earlier post on how to use this technique to plot a sine wave ; you can see this at :


http://chandoo.org/forums/topic/generate-a-sine-wave-xy-scatter-chart


Narayan

 

[cacos]

Got it! It's great thanks a lot.


Now I'm only wondering, since my chart will only have 12 months, is there a way of making it plot the data a bit slower?

 

[cacos]

I mean on Chandoo's chart

 

[Luke M]

Could try changing the one code to add in a delay timer. Would look like this

Sub updateChart()
'dynamic chart macro
Dim cntr As Integer
Dim newHour As Double
Dim newMinute As Double
Dim newSecond As Double
Dim waitTime As Double

For cntr = 1 To 12
'Begin setting up timer
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1 '1 sec delay
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
'End of timer code
[valCounter] = cntr
DoEvents
Next cntr

End Sub

 

[cacos]

That worked perfectly, thank you both you really helped me out