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:
| A | B | |
|---|---|---|
| 1 | ||
| 2 | Date | Incident Type |
| 3 | 6/4/2014 | A |
| 4 | 6/25/2014 | B |
| 5 | 7/1/2014 | C |
| 6 | 7/4/2014 | A |
| 7 | 7/4/2014 | A |
| 8 | 7/4/2014 | B |
| 9 | 8/8/2014 | C |
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
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
One option
http://joxi.ru/Q2KVjDRFgB43mj
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
How to create a mid function for middle name from the full name??
Hi Francis, this is a tough question without seeing your data. Also, names are not always standard as some people have more than one middle name or no middle name. I would recommend that you check out these links:
https://www.exceldashboardtemplates.com/the-best-way-to-separate-address-text-to-multiple-columns/
https://www.exceldashboardtemplates.com/friday-challenge-answer-part-1/
https://www.exceldashboardtemplates.com/how-to-split-up-email-address-text-with-excel-formulas-part-2/
Hope this helps.
Steve=True
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