trouble calculating Cp Cpk using C# programming

[Akshay Shirahatti]

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

[Mayank Gupta]

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.

 

 

[Akshay Shirahatti]

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 April 12, 2019 by Akshay Shirahatti

[Mayank Gupta]

Hi Akshay

 

As I had mentioned in my previous comment, the calculation of sum and sumsq and using it to calculate the standard deviation is not right.