| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Chart
I have a data set in the form as below and need to show the top 5 issues per
month in a chart. I'm baffled as to how and what type of chart to best show this information. I appreciate any direction someone could send me. YY is year , MM is month etc... Thanks Paul YY MM DEFECT c_1000 2007 10 Porosity-Rear Case 2.18 2007 10 Seal Leaks-Rear O/P ID 1.71 2007 10 Porosity-Rear Retainer 1.40 2007 10 Assembly Issues-High Bolt - Case 1.32 2007 10 Assembly Issues-Tight Mainshaft Spline 1.09 2007 11 Seal Leaks-Rear O/P ID 2.64 2007 11 Porosity-Rear Retainer 2.56 2007 11 Assembly Issues-Shafts Do Not Rotate 1.92 2007 11 Leak-No Leak Found 1.52 2007 11 Assembly Issues-Tight Mainshaft Spline 1.28 2007 11 Assembly Issues-Misbuild 1.28 2007 12 Damaged-Motor 3.78 2007 12 Assembly Issues-Motor - Damaged 2.92 2007 12 Seal Leaks-Rear O/P ID 2.27 2007 12 Leak-No Leak Found 1.62 2007 12 Noise-Shift Motor Noisy During Shift 1.51 2008 1 Seal Leaks-Rear O/P ID 2.70 2008 1 Damaged-Motor 1.96 2008 1 Assembly Issues-Shafts Do Not Rotate 1.59 2008 1 Hick-All Gears 1.35 2008 1 Leak-No Leak Found 1.10 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Chart
The top five... not totally sure there... I know there is a method somewhere
in the formulae available in Excel, but I have very little experience with that part. But either way, I think the type of chart you want will be a pareto chart. Here are a couple of links to help with this issue. http://faculty.pepperdine.edu/fmadji...ts/pareto.html http://peltiertech.com/Excel/Charts/Histograms.html Let me know if I can help out any further. Mark Ivey "Paul Ilacqua" wrote in message ... I have a data set in the form as below and need to show the top 5 issues per month in a chart. I'm baffled as to how and what type of chart to best show this information. I appreciate any direction someone could send me. YY is year , MM is month etc... Thanks Paul YY MM DEFECT c_1000 2007 10 Porosity-Rear Case 2.18 2007 10 Seal Leaks-Rear O/P ID 1.71 2007 10 Porosity-Rear Retainer 1.40 2007 10 Assembly Issues-High Bolt - Case 1.32 2007 10 Assembly Issues-Tight Mainshaft Spline 1.09 2007 11 Seal Leaks-Rear O/P ID 2.64 2007 11 Porosity-Rear Retainer 2.56 2007 11 Assembly Issues-Shafts Do Not Rotate 1.92 2007 11 Leak-No Leak Found 1.52 2007 11 Assembly Issues-Tight Mainshaft Spline 1.28 2007 11 Assembly Issues-Misbuild 1.28 2007 12 Damaged-Motor 3.78 2007 12 Assembly Issues-Motor - Damaged 2.92 2007 12 Seal Leaks-Rear O/P ID 2.27 2007 12 Leak-No Leak Found 1.62 2007 12 Noise-Shift Motor Noisy During Shift 1.51 2008 1 Seal Leaks-Rear O/P ID 2.70 2008 1 Damaged-Motor 1.96 2008 1 Assembly Issues-Shafts Do Not Rotate 1.59 2008 1 Hick-All Gears 1.35 2008 1 Leak-No Leak Found 1.10 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Chart
How many possible issues are there? If it's less than a dozen or so, I'd
make a line chart and show them all (not just the five worst, but perhaps the ones which are ever fifth worst), with incidence as the Y values and month & year as X. This way you can see the trends over time, and eyeball the five worst each month. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Paul Ilacqua" wrote in message ... I have a data set in the form as below and need to show the top 5 issues per month in a chart. I'm baffled as to how and what type of chart to best show this information. I appreciate any direction someone could send me. YY is year , MM is month etc... Thanks Paul YY MM DEFECT c_1000 2007 10 Porosity-Rear Case 2.18 2007 10 Seal Leaks-Rear O/P ID 1.71 2007 10 Porosity-Rear Retainer 1.40 2007 10 Assembly Issues-High Bolt - Case 1.32 2007 10 Assembly Issues-Tight Mainshaft Spline 1.09 2007 11 Seal Leaks-Rear O/P ID 2.64 2007 11 Porosity-Rear Retainer 2.56 2007 11 Assembly Issues-Shafts Do Not Rotate 1.92 2007 11 Leak-No Leak Found 1.52 2007 11 Assembly Issues-Tight Mainshaft Spline 1.28 2007 11 Assembly Issues-Misbuild 1.28 2007 12 Damaged-Motor 3.78 2007 12 Assembly Issues-Motor - Damaged 2.92 2007 12 Seal Leaks-Rear O/P ID 2.27 2007 12 Leak-No Leak Found 1.62 2007 12 Noise-Shift Motor Noisy During Shift 1.51 2008 1 Seal Leaks-Rear O/P ID 2.70 2008 1 Damaged-Motor 1.96 2008 1 Assembly Issues-Shafts Do Not Rotate 1.59 2008 1 Hick-All Gears 1.35 2008 1 Leak-No Leak Found 1.10 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Chart
I may have confused the issue. What I want to see is of the distinct issues,
what was thier place in 10-07, 11-07, 12-07 etc. As in a stacked bar with the bar containing different colors for each month. As you look at the chart you can see that it was a problem in NOV-07, went away in 1-08 and was reduced in 2-08. Does that explain it better? YY MM DEFECT c_1000 ==================================== 2007 10 Porosity-Rear Case 2.18 2007 10 Seal Leaks-Rear O/P ID 1.71 2007 10 Porosity-Rear Retainer 1.40 2007 10 Assembly Issues-High Bolt - Case 1.32 2007 10 Assembly Issues-Tight Mainshaft Spline 1.09 2007 11 Seal Leaks-Rear O/P ID 2.64 2007 11 Porosity-Rear Retainer 2.56 2007 11 Assembly Issues-Shafts Do Not Rotate 1.92 2007 11 Leak-No Leak Found 1.52 2007 11 Assembly Issues-Tight Mainshaft Spline 1.28 2007 11 Assembly Issues-Misbuild 1.28 2007 12 Damaged-Motor 3.78 2007 12 Assembly Issues-Motor - Damaged 2.92 2007 12 Seal Leaks-Rear O/P ID 2.27 2007 12 Leak-No Leak Found 1.62 2007 12 Noise-Shift Motor Noisy During Shift 1.51 2008 1 Seal Leaks-Rear O/P ID 2.70 2008 1 Damaged-Motor 1.96 2008 1 Assembly Issues-Shafts Do Not Rotate 1.59 2008 1 Hick-All Gears 1.35 2008 1 Leak-No Leak Found 1.10 "Jon Peltier" wrote in message ... How many possible issues are there? If it's less than a dozen or so, I'd make a line chart and show them all (not just the five worst, but perhaps the ones which are ever fifth worst), with incidence as the Y values and month & year as X. This way you can see the trends over time, and eyeball the five worst each month. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Paul Ilacqua" wrote in message ... I have a data set in the form as below and need to show the top 5 issues per month in a chart. I'm baffled as to how and what type of chart to best show this information. I appreciate any direction someone could send me. YY is year , MM is month etc... Thanks Paul YY MM DEFECT c_1000 2007 10 Porosity-Rear Case 2.18 2007 10 Seal Leaks-Rear O/P ID 1.71 2007 10 Porosity-Rear Retainer 1.40 2007 10 Assembly Issues-High Bolt - Case 1.32 2007 10 Assembly Issues-Tight Mainshaft Spline 1.09 2007 11 Seal Leaks-Rear O/P ID 2.64 2007 11 Porosity-Rear Retainer 2.56 2007 11 Assembly Issues-Shafts Do Not Rotate 1.92 2007 11 Leak-No Leak Found 1.52 2007 11 Assembly Issues-Tight Mainshaft Spline 1.28 2007 11 Assembly Issues-Misbuild 1.28 2007 12 Damaged-Motor 3.78 2007 12 Assembly Issues-Motor - Damaged 2.92 2007 12 Seal Leaks-Rear O/P ID 2.27 2007 12 Leak-No Leak Found 1.62 2007 12 Noise-Shift Motor Noisy During Shift 1.51 2008 1 Seal Leaks-Rear O/P ID 2.70 2008 1 Damaged-Motor 1.96 2008 1 Assembly Issues-Shafts Do Not Rotate 1.59 2008 1 Hick-All Gears 1.35 2008 1 Leak-No Leak Found 1.10 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Chart
I understand the issue; I used to do this stuff. Stacked columns, paretos,
or whatever show only a snapshot in time. but it seems to me that a better visual is a time line showing all the data. In December, if you had a bunch of things at 1.50, they wouldn't show up, because the fifth worst was 1.51. However, in October, they all would be higher than the actual fifth worst of 1.09. Sure it's important to track the worst ones, but don't ignore the ones whose rank drops slightly because of fluctuations in all of the data. I did a little exercise to illustrate my point. I started with your data, then expanded it so I had each defect type listed for each month, even if I had no data. I added a column for "Realistic", by which I meant it showed some kind of incidence for that item. I essentially took a random number between zero and the lowest data I had (the fifth worst). I made a pivot table to prep the data for plotting as time series, with incidence of each defect vs. month. Then I made a regular chart from this data. It's a little cluttered, so I highlighted the five worst from each month with a circle around the points, and I think this made it fairly effective. It not only shows which five were worst in a given month, but it shows the trend of each defect, and where each crosses between "good" and "bad". It's only made up data for those below the cutoff, so I won't draw any conclusions from the data. But from the chart, I'll draw the conclusion that it's more useful than gust a bad/good decision for each defect each month. The zipped workbook is located he http://peltiertech.com/Excel/Zips/DefectTracking.zip - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Paul Ilacqua" wrote in message ... I may have confused the issue. What I want to see is of the distinct issues, what was thier place in 10-07, 11-07, 12-07 etc. As in a stacked bar with the bar containing different colors for each month. As you look at the chart you can see that it was a problem in NOV-07, went away in 1-08 and was reduced in 2-08. Does that explain it better? YY MM DEFECT c_1000 ==================================== 2007 10 Porosity-Rear Case 2.18 2007 10 Seal Leaks-Rear O/P ID 1.71 2007 10 Porosity-Rear Retainer 1.40 2007 10 Assembly Issues-High Bolt - Case 1.32 2007 10 Assembly Issues-Tight Mainshaft Spline 1.09 2007 11 Seal Leaks-Rear O/P ID 2.64 2007 11 Porosity-Rear Retainer 2.56 2007 11 Assembly Issues-Shafts Do Not Rotate 1.92 2007 11 Leak-No Leak Found 1.52 2007 11 Assembly Issues-Tight Mainshaft Spline 1.28 2007 11 Assembly Issues-Misbuild 1.28 2007 12 Damaged-Motor 3.78 2007 12 Assembly Issues-Motor - Damaged 2.92 2007 12 Seal Leaks-Rear O/P ID 2.27 2007 12 Leak-No Leak Found 1.62 2007 12 Noise-Shift Motor Noisy During Shift 1.51 2008 1 Seal Leaks-Rear O/P ID 2.70 2008 1 Damaged-Motor 1.96 2008 1 Assembly Issues-Shafts Do Not Rotate 1.59 2008 1 Hick-All Gears 1.35 2008 1 Leak-No Leak Found 1.10 "Jon Peltier" wrote in message ... How many possible issues are there? If it's less than a dozen or so, I'd make a line chart and show them all (not just the five worst, but perhaps the ones which are ever fifth worst), with incidence as the Y values and month & year as X. This way you can see the trends over time, and eyeball the five worst each month. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Paul Ilacqua" wrote in message ... I have a data set in the form as below and need to show the top 5 issues per month in a chart. I'm baffled as to how and what type of chart to best show this information. I appreciate any direction someone could send me. YY is year , MM is month etc... Thanks Paul YY MM DEFECT c_1000 2007 10 Porosity-Rear Case 2.18 2007 10 Seal Leaks-Rear O/P ID 1.71 2007 10 Porosity-Rear Retainer 1.40 2007 10 Assembly Issues-High Bolt - Case 1.32 2007 10 Assembly Issues-Tight Mainshaft Spline 1.09 2007 11 Seal Leaks-Rear O/P ID 2.64 2007 11 Porosity-Rear Retainer 2.56 2007 11 Assembly Issues-Shafts Do Not Rotate 1.92 2007 11 Leak-No Leak Found 1.52 2007 11 Assembly Issues-Tight Mainshaft Spline 1.28 2007 11 Assembly Issues-Misbuild 1.28 2007 12 Damaged-Motor 3.78 2007 12 Assembly Issues-Motor - Damaged 2.92 2007 12 Seal Leaks-Rear O/P ID 2.27 2007 12 Leak-No Leak Found 1.62 2007 12 Noise-Shift Motor Noisy During Shift 1.51 2008 1 Seal Leaks-Rear O/P ID 2.70 2008 1 Damaged-Motor 1.96 2008 1 Assembly Issues-Shafts Do Not Rotate 1.59 2008 1 Hick-All Gears 1.35 2008 1 Leak-No Leak Found 1.10 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel Chart
Jon,
Thanks for the advice.. this will add nicely to the stacked bar... thanks so much for the time you invested to produce the example. Paul "Jon Peltier" wrote in message ... I understand the issue; I used to do this stuff. Stacked columns, paretos, or whatever show only a snapshot in time. but it seems to me that a better visual is a time line showing all the data. In December, if you had a bunch of things at 1.50, they wouldn't show up, because the fifth worst was 1.51. However, in October, they all would be higher than the actual fifth worst of 1.09. Sure it's important to track the worst ones, but don't ignore the ones whose rank drops slightly because of fluctuations in all of the data. I did a little exercise to illustrate my point. I started with your data, then expanded it so I had each defect type listed for each month, even if I had no data. I added a column for "Realistic", by which I meant it showed some kind of incidence for that item. I essentially took a random number between zero and the lowest data I had (the fifth worst). I made a pivot table to prep the data for plotting as time series, with incidence of each defect vs. month. Then I made a regular chart from this data. It's a little cluttered, so I highlighted the five worst from each month with a circle around the points, and I think this made it fairly effective. It not only shows which five were worst in a given month, but it shows the trend of each defect, and where each crosses between "good" and "bad". It's only made up data for those below the cutoff, so I won't draw any conclusions from the data. But from the chart, I'll draw the conclusion that it's more useful than gust a bad/good decision for each defect each month. The zipped workbook is located he http://peltiertech.com/Excel/Zips/DefectTracking.zip - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Paul Ilacqua" wrote in message ... I may have confused the issue. What I want to see is of the distinct issues, what was thier place in 10-07, 11-07, 12-07 etc. As in a stacked bar with the bar containing different colors for each month. As you look at the chart you can see that it was a problem in NOV-07, went away in 1-08 and was reduced in 2-08. Does that explain it better? YY MM DEFECT c_1000 ==================================== 2007 10 Porosity-Rear Case 2.18 2007 10 Seal Leaks-Rear O/P ID 1.71 2007 10 Porosity-Rear Retainer 1.40 2007 10 Assembly Issues-High Bolt - Case 1.32 2007 10 Assembly Issues-Tight Mainshaft Spline 1.09 2007 11 Seal Leaks-Rear O/P ID 2.64 2007 11 Porosity-Rear Retainer 2.56 2007 11 Assembly Issues-Shafts Do Not Rotate 1.92 2007 11 Leak-No Leak Found 1.52 2007 11 Assembly Issues-Tight Mainshaft Spline 1.28 2007 11 Assembly Issues-Misbuild 1.28 2007 12 Damaged-Motor 3.78 2007 12 Assembly Issues-Motor - Damaged 2.92 2007 12 Seal Leaks-Rear O/P ID 2.27 2007 12 Leak-No Leak Found 1.62 2007 12 Noise-Shift Motor Noisy During Shift 1.51 2008 1 Seal Leaks-Rear O/P ID 2.70 2008 1 Damaged-Motor 1.96 2008 1 Assembly Issues-Shafts Do Not Rotate 1.59 2008 1 Hick-All Gears 1.35 2008 1 Leak-No Leak Found 1.10 "Jon Peltier" wrote in message ... How many possible issues are there? If it's less than a dozen or so, I'd make a line chart and show them all (not just the five worst, but perhaps the ones which are ever fifth worst), with incidence as the Y values and month & year as X. This way you can see the trends over time, and eyeball the five worst each month. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Paul Ilacqua" wrote in message ... I have a data set in the form as below and need to show the top 5 issues per month in a chart. I'm baffled as to how and what type of chart to best show this information. I appreciate any direction someone could send me. YY is year , MM is month etc... Thanks Paul YY MM DEFECT c_1000 2007 10 Porosity-Rear Case 2.18 2007 10 Seal Leaks-Rear O/P ID 1.71 2007 10 Porosity-Rear Retainer 1.40 2007 10 Assembly Issues-High Bolt - Case 1.32 2007 10 Assembly Issues-Tight Mainshaft Spline 1.09 2007 11 Seal Leaks-Rear O/P ID 2.64 2007 11 Porosity-Rear Retainer 2.56 2007 11 Assembly Issues-Shafts Do Not Rotate 1.92 2007 11 Leak-No Leak Found 1.52 2007 11 Assembly Issues-Tight Mainshaft Spline 1.28 2007 11 Assembly Issues-Misbuild 1.28 2007 12 Damaged-Motor 3.78 2007 12 Assembly Issues-Motor - Damaged 2.92 2007 12 Seal Leaks-Rear O/P ID 2.27 2007 12 Leak-No Leak Found 1.62 2007 12 Noise-Shift Motor Noisy During Shift 1.51 2008 1 Seal Leaks-Rear O/P ID 2.70 2008 1 Damaged-Motor 1.96 2008 1 Assembly Issues-Shafts Do Not Rotate 1.59 2008 1 Hick-All Gears 1.35 2008 1 Leak-No Leak Found 1.10 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy chart formatting and chart templates in Excel 2007 | Charts and Charting in Excel | |||
| Excel 2007 Chart - determining the color of a chart style | Charts and Charting in Excel | |||
| Excel 2003 is missing Built-In Custom Chart Types in Chart Wizard | Charts and Charting in Excel | |||
| Excel 2003 is missing Built-In Custom Chart Types in Chart Wizard | Setting up and Configuration of Excel | |||
| Excel 2003: How to nudge a chart element or shape on a chart? | Charts and Charting in Excel | |||
Linear Mode
