MS Excel for LSL, USL in Histogram and Standard Deviation

G

gopinathgopi

I am doing one Project related to Six Sigma Tools. I draw histogram by excel. In that Histogram I want to Insert LSL,USL,Normal Distribution curve. When I Give the Input in Excel (Sheet1) it will show that results in Excel(Sheet2).Based on that i completed the Histogram. The problem is LSL,USL. When i used Line Chart for Drawing this LSL,USL it is not accurate.Based on the Data LSL,USL vary. For Example if i give LSL=20,USL=40, the Line chart i had draw in the histogram it will show LSL=24,USL=56. It not show the accurate. We know it is easy to draw MINITAB,ADD-INS.But Based on the Financial Situation we cannot put for all the Systems.So Its Is Possible Insert LSL,USL line in histogram with excel.And also I know know how we put NORMAL DISTRIBUTION CURVE.Otherwise it is possible by VBA programming in EXCEL. And final figure I want exactly (MINITAB histogram with Normal Distribution = Excel Histogram with ND curve).If it is possible can anyone give Ideas with Examples.Otherwise give a link what i will do(Guidness).I hope it will be understandable. Any questions you want?? i will give...
 

Steve Prevette

Deming Disciple
Leader
Super Moderator
By USL and LSL - do you really want Upper Control Limit and Lower Control Limit? USL and LSL refer to Specification Limits, which come from customer requirements, not a data analysis.

UCL and LCL are usually calculated from the data, using the average for a fixed time interval, then the average plus or minus three standard deviations. See http://www.efcog.org/wg/esh_es/Statistical_Process_Control/ for a discussion of control charting. One primarily uses a line chart, not a histogram, as we must determine if the data are stable in order for proper determination of the UCL and LCL.
 
G

gopinathgopi

We need USL and LSL only. As of now we are having a excel workbook with two sheets.In th efirst sheet data is entried. Based on that data graph changes in next sheet. WE have created histogram that way. What we need now is to insert vertical line for USL and LSL which should change when we chage data in sheet 1. Kindly let us know how to insert vertical line in histogram/bar chart which should change on changing LSL and USL value in sheet 1.
:thanx: in advance
 

Steve Prevette

Deming Disciple
Leader
Super Moderator
I can't speak to minitab, but in Excel there probably is a way to overlay a x-y plot with the USL/LSL vertical lines over your histogram. Probably would have to have the x-y plot on a "second x-axis" since the histogram is likely done using a text axis, but you need the x-y plot to have a numeric axis. Another difficulty may be if the USL and/or LSL falls in the middle of a category.

Without seeing the Excel file, it is hard to determine what would be a feasible approach.
 
G

gopinathgopi

We have attached the graph we have created for further help and guidance.Please help.

:thanx: in advance..
 

Attachments

  • process capability1.xlsx
    47.7 KB · Views: 1,113

Steve Prevette

Deming Disciple
Leader
Super Moderator
The process by which the chart is made appears to be hidden, and the chart is just a picture of the result . . .
 
G

gopinathgopi

Here I attached my EXCEL file. And also i enclose what i want in that graph. Is any other details you want plz tell? :thanx: and waiting for your reply.........
 

Attachments

  • process capability1.xlsx
    98.4 KB · Views: 709

Steve Prevette

Deming Disciple
Leader
Super Moderator
I'm afraid someone else will need to help (someone who knows Minitab). The graphics are simply pictures exported from Minitab. Excel is not making the charts.
 
G

gopinathgopi

Kindly send us a format with overlay of vertical lines in histogram. I hope that would help us to some extent.
 

Steve Prevette

Deming Disciple
Leader
Super Moderator
Here is an example in Excel. The only thing that is manual is you must match the range of the secondary horizontal axis to the range of the data (0 to 24 in this case).
 

Attachments

  • Histogram with USL LSL example.xlsx
    12.8 KB · Views: 1,149
Top Bottom