Previous Story
Friday Challenge – Pass Fail Chart in an Excel Spreadsheet
Hi all and thanks for your patience. I think we are all better now and the hack has been fixed. (Fingers Crossed)
I will get back to the final email break down from a previous Friday Challenge in a post next week.
So in the mean time, I wanted to post this Friday Challenge.
Now don’t take the title as the only solution. I am hoping someone can come up with a better solution than mine. Read on and let me know what you would make. Leave me a comment with your solution or a comment with the email field filled in and I will send you my contact information so that you can submit a spreadsheet. Lets come up with the best one with this cryptic (and yet unsolved) request.
Plotting a line graph to track build health
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sr.No | Build No | Execution Date | Status |
| 2 | 1 | 1 | 8-Aug-13 | Pass |
| 3 | 2 | 1.01 | 9-Aug-13 | Fail |
| 4 | 3 | 1.02 | 10-Aug-13 | Pass |
| 5 | 4 | 1.03 | 11-Aug-13 | Pass |
I am looking for Execution Date on X axis ( which is not a problem) currently. But on Y axis I am looking to plat a Pass/Fail which is getting replaced with a 0/1 and the mapping is also not coming out fine. – Ash
Good luck and I look forward to your awesome solutions. Also, don’t forget to subscribe to my blog so that you get the next post delivered directly to your inbox.
Steve=True
I have submitted a solution with the assumption that the real data set is much larger than the example given. When the individual who posted the question says, “…the mapping is also not coming out fine.”, what issue are they running into?
-Pete
Thanks Pete, it looked great. Can’t wait to share it with the fans. Wish I knew more, but that is all I had to go on.
Steve=True
I have a solution, should I email it to you? Do we want Pass/Fail as the Y-axis?
Step 1: Got the x-axis by making 4 columns with value 0 and position the axis On tick marks.
Step 2: Added 2 Horizontal Lines (series names Pass and Fail) which are constant (Pass=1 Fail=.5) which replace the default gridlines and I add the series names (as labels to the 1st data point. Under Axis Options i set the min, max, major unit to 0,1.5,.5 respectivly and then go to “Number” and create a custom type:
Step 3: Add another line which actualy tracts the status.
Step 4 (Optional if you want to add Build No): I made a pivot table with SR.No for the rows, build no for column labels and sum of trend for values. then I made a regular table based on that with na() where there where blanks, and changing building no to text. From that created a line chart; copied the chart and pasted it into my previous chart (should lead create distinct markers on each point of the trend line). Add series labels to each of those distinct markers!
Hi Brian,
Yes please send me your sample.
Interesting solution. Can’t wait to see it.
Steve=True
I do not know how much I agree with the idea of using a line-graph to display binary results.
I completely agree and thus my comment in the post to look outside the box. 🙂
Good thinking on your part.
Steve=True
I have an extraordinarily similar problem. For the exact same reason. But I want a chart that would plot pass/fail, but with a few tweaks.
5/1/17 5/5/17 5/8/17 5/10/17 5/10/17 5/18/17 5/20/17
Test1 PASS PASS PASS PASS FAIL PASS
Test2 PASS FAIL
Test3 PASS PASS PASS PASS
My x would be date (linear scale)
My Y would be series Test1, 2, and 3
The datapoints would be green * for each pass and red * for each fail with blanks allowed as shown above.
Better yet – 3 horizontal bars. The length being the date of the last status and the color being green for last status pass or red for last status fail. And a * at each actual test (or at least at each fail) plotted on top of the bar.
Thanks for sharing John!
Well… that formatted well. (NOT!) And I don’t know how to embedd a graphic in this so, you should get the idea from my previous post. Spread teh PASS FAIL out randomly for each series and include blanks to make it equal the number of dates and you’ve got the general idea.