trouble calculating Cp Cpk using C# programming

Followers

April 10, 20197 yr

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

April 11, 20197 yr

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.

April 12, 20197 yr

Author

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.