Friday Challenge Answers – Cumulative Events Over Time

In our recent Friday Challenge,

Friday Challenge – Creating an Excel Graph of Cumulative Events Over Time

where a user wanted the following:

 

Creating a graph of cumulative events over time





I currently have a table of various events that occurred and the associated date. How can I go about plotting a graph of the cumulative incidence of specific events over time?

For example my table looks something like this:

A B
1 6/4/2014 A
2 6/25/2014 B
3 7/1/2014 C
4 7/4/2014 A
5 7/4/2014 A
6 7/4/2014 B
7 8/8/2014 C

How do I create separated line graphs of the cumulative incidences of A, B and C over time ?

 

I received several great submissions.



This one from Crazy Dog 64 is the exact chart that I created when first trying to solve this problem.  Here is what their final chart looked like:CrazyDogsSolution

Only issue with this chart versus the original request is that the line progresses from Zero to 1 in a sloped/trend type of format, but the incidents were zero right up until the first incident.  So I feel that this chart should be more of a step type chart, but this is a really close solution.

You can learn more about step charts here:

Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

Understanding How-to Make a Step Chart in Excel Using Index and Match Functions

How-To Create an Excel Step Chart Formula Using the Small Function





How-to Create a Step Chart in Excel with 3 Quick Steps

How-to Easily Create a Step Chart in Excel

 

I also receive this great submission from Magrifa:





If you found the website and tutorials helpful, please consider donating to keep the lights on.

Donate with PayPal here:





Magrifa-s Solution

This is a real great visual for incidents by date.  Only issue that I see is that the user wanted a line chart to show the data and that they wanted cumulative incidents by date.  This shows number of incidents, not a cumulative value, but I think that I like really this graphic representation of the data.  What do you think?



I believe that Magrifa plotted the data points in a line graph, added labels and then hid the line.  Then Magrifa used drop lines to connect the data to the date in the horizontal access.  Great job Magrifa!

Next I also received the following solution from Piotr from Poland:

PiotrFromPolandsSolution

I too like this format.  Piotr is representing the data in a Step-Area type of format.  I think that he is using a column chart and closing the gap.  Also, Piotr has stacked the charts on top of each other in a Vertical Panel chart type format.  Only issue is that this isn’t the line chart that the user requested, but I also really like this one too.  Thanks Piotr!





If you want to learn more about panel charts, check out this post:

How-to Make a Wall Street Journal Horizontal Panel Chart in Excel

 

Finally, here is the solution that I created:

creating a graph over time of cumulative events



It represents the data in a line chart and it is also cumulative data.  It looks like a step chart but I had to significantly change the formulas so that would create a cumulative value of all incidents by type.  Check out the download file to see how the formula was updated for these issues.  I of course am most partial to my version, but I want to know what you think?  Which chart do you like best?  Let me know in the comments below!

 

Video Demonstration:

 

Free Excel Sample Download File:





Friday-Challenge-Answers-cumulative-eventes-over-time.xlsx

 

Steve=True





Want to Receive the Next Post?
Join My Newsletter
Subscribe
Give it a try, you can unsubscribe anytime.
Privacy Policy

2 COMMENTS

  1. Dear all,
    Another idea here:
    A B C D E F …
    1
    2 Sorted Incident Types
    3 Date Type Dates A B …
    4 1/1/15 A =COUNTIFS($B$4:$B$184,E$3?$A$4:$A$184,”<="&$D4)

    NB: Dates in column D should be sorted
    1 Formula in E4 to be copied and pasted

    The chart is easy to build then, isn't it?

    Regards

    • Thanks for the comment DJP. Not quite sure what you describe. I will send you an email and perhaps you can send me a sample file. Thanks Steve=True

LEAVE A REPLY

Please enter your comment!
Please enter your name here