Jump to content
Click here to know about ONLINE Lean Six Sigma Certiifcation ×

trouble calculating Cp Cpk using C# programming


Akshay Shirahatti

Recommended Posts

Hi,

I'm not getting the correct Cp,Cpk values. I'm using C# to calculate cp cpk for the input test file which is in CSV format (attached). 

Here is my full code

 

using Microsoft.VisualBasic.FileIO;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace _1.CSV
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        DataTable dt = new DataTable();
        private void button1_Click(object sender, EventArgs e)
        {

            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            openFileDialog1.Filter = ("comma seperated value | *.CSV");
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string strfilename = openFileDialog1.FileName;
                if (File.Exists(strfilename))
                {
                    textBox1.Text = strfilename;
                }
                else
                {

                    strfilename = "";
                }
                if (!string.IsNullOrWhiteSpace(strfilename))
                {
                    textBox1.Text = strfilename;
                }
                else
                {
                    MessageBox.Show("Please select a file");
                }
            }
            else
            {
                MessageBox.Show("Please select a file");
            }
           
        }

        private void button2_Click(object sender, EventArgs e)
        {
            dt = GetDataTabletFromCSVFile(textBox1.Text);

            foreach (DataRow row in dt.Rows)
            {
                if (Convert.ToDateTime(row["Date"]) <= Convert.ToDateTime(dateTimePicker2.Text) && Convert.ToDateTime(row["Date"]) >= Convert.ToDateTime(dateTimePicker1.Text))
                {

                    double sum = 0;
                    double sumsq = 0;
                    foreach (DataRow Row in dt.Rows)
                    {
                        Double delta = double.Parse(row["USL"].ToString()) - Double.Parse(row["LSL"].ToString());
                        sum += delta;
                        sumsq += delta * delta;
                    }

                    double mean = sum / dt.Rows.Count;
                    double stdev = Math.Sqrt(sumsq / dt.Rows.Count - mean * mean);


                    Double usl = Convert.ToDouble(row["USL"]);
                    Double lsl = Convert.ToDouble(row["LSL"]);
                    double Cp = (usl - lsl) / (6 * stdev);
                    double Cpk = Math.Min(
                        (usl - mean) / (3 * stdev),
                        (mean - lsl) / (3 * stdev)
                        );
                    Console.WriteLine(
                        "{0}: {1},{2}, {3}, {4}",
                        row["Date"], usl, lsl, Cp, Cpk);
                    button2.Enabled = false;


                }
            }
        }
        private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
        {
            DataTable csvData = new DataTable();
            try
            {
                using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                {
                    csvReader.SetDelimiters(new string[] { "," });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    string[] colFields = csvReader.ReadFields();
                    foreach (string column in colFields)
                    {
                        if (column == "Date" || column == "Test1" || column == "USL" || column == "LSL")
                        {
                            DataColumn datecolumn = new DataColumn(column);
                            datecolumn.AllowDBNull = true;
                            csvData.Columns.Add(datecolumn);
                        }
                    }
                    while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        for (int i = 0; i < fieldData.Length; i++)
                        {
                            if (fieldData == "")
                            {
                                fieldData = null;
                            }
                        }
                        csvData.Rows.Add(fieldData);
                    }
                }

            }
            catch (Exception)
            {
                throw;
            }
            return csvData;

        }
    }
}
 

csvdemo1.csv

Link to comment
Share on other sites

Hello Akshay

 

I am not a coding expert in C#. However, looking at the code, I guess the issue is with the calculation of Mean and Standard Deviation. With reference to your code....could you please help me understand what is happening in the highlighted steps

 

******************

foreach (DataRow Row in dt.Rows)
                    {
                        Double delta = double.Parse(row["USL"].ToString()) - Double.Parse(row["LSL"].ToString());
                        sum += delta;
                        sumsq += delta * delta;
                    }

                    double mean = sum / dt.Rows.Count;
                    double stdev = Math.Sqrt(sumsq / dt.Rows.Count - mean * mean);

******************

 

Per my understanding, for each row in the table, you are first calculating Delta = USL - LSL and then updating 'sum' and 'sumsq'. Is it correct? If that's the case, then your mean and stdev calculations are incorrect and therefore you are getting incorrect Cp and Cpk values.

And your formula for stdev also seems to be incorrect.

 

Also, I looked at the CSV file. USL says 7.25 while LSL says 8. This seems to be incorrect. USL has to be higher than LSL. So you need to fix your inputs as well.

 

Plus, you don't need to create the code for this calculation. You can refer to our Cp/Cpk calculator at the following link. Enter the values for mean, standard deviation, LSL and USL and you will get Cp and Cpk

https://www.benchmarksixsigma.com/calculators/process-capability-calculator/

 

Though you will not be able to upload a CSV file, but you can simply calculate the mean and stdev for Test column and enter the values in the ready to use calculator.

 

 

Link to comment
Share on other sites

On 4/11/2019 at 10:31 AM, Mayank Gupta said:

Hello Akshay

 

I am not a coding expert in C#. However, looking at the code, I guess the issue is with the calculation of Mean and Standard Deviation. With reference to your code....could you please help me understand what is happening in the highlighted steps

 

******************

foreach (DataRow Row in dt.Rows)
                    {
                        Double delta = double.Parse(row["USL"].ToString()) - Double.Parse(row["LSL"].ToString());
                        sum += delta;
                        sumsq += delta * delta;
                    }

                    double mean = sum / dt.Rows.Count;
                    double stdev = Math.Sqrt(sumsq / dt.Rows.Count - mean * mean);

******************

 

Per my understanding, for each row in the table, you are first calculating Delta = USL - LSL and then updating 'sum' and 'sumsq'. Is it correct? If that's the case, then your mean and stdev calculations are incorrect and therefore you are getting incorrect Cp and Cpk values.

And your formula for stdev also seems to be incorrect.

 

Also, I looked at the CSV file. USL says 7.25 while LSL says 8. This seems to be incorrect. USL has to be higher than LSL. So you need to fix your inputs as well.

 

Plus, you don't need to create the code for this calculation. You can refer to our Cp/Cpk calculator at the following link. Enter the values for mean, standard deviation, LSL and USL and you will get Cp and Cpk

https://www.benchmarksixsigma.com/calculators/process-capability-calculator/

 

Though you will not be able to upload a CSV file, but you can simply calculate the mean and stdev for Test column and enter the values in the ready to use calculator.

 

 

Hi,

I'm sorry about that CSV file . The USL should be 8 and LSL should be 7,.25.

I think I'm stuck with the standard deviation part. I'm getting NaN for Std deviation.

 

I checked with the calculator. I need to get 2.57 as Cp

 

Edited by Akshay Shirahatti
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Who's Online (See full list)

    • There are no registered users currently online
  • Forum Statistics

    • Total Topics
      3.3k
    • Total Posts
      16.9k
  • Member Statistics

    • Total Members
      55,176
    • Most Online
      990

    Newest Member
    Mihir Shekh
    Joined
×
×
  • Create New...