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

Here is a Friday Challenge that a lot of Excel users may run into.

Essentially, you are given a date and incident type that occurred on that date.  However, the user wants to take those columns of data and create a chart of cumulative incidents by date.

 

Here is the exact request:

 





“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:

AB
1
2DateIncident Type
36/4/2014A
46/25/2014B
57/1/2014C
67/4/2014A
77/4/2014A
87/4/2014B
98/8/2014C

 

The Challenge

1) Copy the starting data from the table above to your Excel Worksheet and then create a Chart Data Range for the Cumulative Incidents by Date.

2) Create a Formula that can be used to to Easily create the cumulative values for new dates and new incidence types that may come our way.



3) Create a Chart that Best presents a) what the user wants and b) the chart data.

 

Good luck and send me a Contact Form request below if you want to share your success!

 

Steve=True









8 COMMENTS

  1. Most likely to complicated but I added 3 more columns to the right of the data. 1 for each Incident being counted. For each column I added a =COUNTIF($B$2:B2,”A”) for the “A” column, =COUNTIF($B$2:B2,”B”) for the “B” column etc. and copied it down the column. Then I made a basic line chart on the A, C, D, E columns. Looks like this http://i.imgur.com/vbrGYAF.png

    • Thanks Chad. That sounds like a very good approach. Sounds less complicated than mine 🙂 One thought to consider: Since we are talking about discrete events that happen on a particular day, your line slowly increasing from one data point to another may seem confusing. Thanks again for your solution/comment. Steve=True

    • Thanks Magrifa, it is a neat approach to the problem. Only change I would make is that the user wanted to have a cumulative value by date. So the B value on the second date should be 2 instead of 1. Thanks for the submission. Steve=True

  2. A possible solution (does not cover every case) is that if you have a table set up with a Full Name column and a Middle name column, the formula for the Middle name column can be: =IFERROR(MID([@[Full Name]],FIND(” “,[@[Full Name]])+1,FIND(” “,[@[Full Name]],FIND(” “,[@[Full Name]])+1)-FIND(” “,[@[Full Name]])-1),”No Middle Name”)

    This will extract the middle name (only the first middle name if there are more than one) based on having a space between first, middle, and last names. If there is no middle name, based on spaces between names, it will return “No Middle Name”.

    I hope this helps.

    -Pete

    • Thanks Pete. Hopefully the name format isn’t “True, Steve Equals” LOL so many different ways to do it. 🙂 Thanks for the assist! Steve=True

LEAVE A REPLY

Please enter your comment!
Please enter your name here