Statistical results varying from excel vsto to minitab
Ramadas Ravikumar
46
Reputation points
Dear All,
I am using below code to calculate and display statistical results of the selected column in a new sheet:
however ,
the results are varying from minitab to the results from this code:
Results from Minitab:
Cp = 1.45 , Cpk = 1.07 , Pp = 1.72 , Ppk = 1.28 ,P- Value = 0.110
But results from the below code:
Cp = 1.72431175510886 , Cpk = 1.27801930084539 , Pp = 0.608580619450184 , Ppk = 1.27801930084539 ,P- Value = -2146826259:
Below is the data:
Elong
- 9
- 2
- 8
- 2
- 8
2
2 - 4
2 - 3
- 1
- 9
2 - 1
- 2
- 9
2 - 3
- 3
2 - 2
2
2 - 9
- 1
- 3
- 1
- 8
- 2
- 1
below is the code:
using Microsoft.Office.Tools.Ribbon;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
namespace Auto_Calculate
{
public partial class Entrydata : Form
{
public Entrydata()
{
InitializeComponent();
}
private void Entrydata_Load(object sender, EventArgs e)
{
this.TopMost = true;
}
private void textBox1_Enter(object sender, EventArgs e)
{
Globals.ThisAddIn.Application.ActiveWindow.Activate();
}
private void textBox2_Enter(object sender, EventArgs e)
{
// Set focus to Excel and display selected range of cells in textbox
Globals.ThisAddIn.Application.ActiveWindow.Activate();
}
private void btncloumn_Click(object sender, EventArgs e)
{
var cellValue = Globals.ThisAddIn.Application.ActiveCell?.Value;
if (cellValue != null)
{
textBox1.Text = cellValue.ToString();
}
}
private void btnrange_Click(object sender, EventArgs e)
{
textBox2.Text = Globals.ThisAddIn.Application.ActiveWindow?.RangeSelection.Address;
}
private void button1_Click(object sender, EventArgs e)
{
// Get column name, range, LSL, and USL from textboxes
string columnName = textBox1.Text;
string range = textBox2.Text;
double lsl = double.Parse(textBox3.Text);
double usl = double.Parse(textBox4.Text);
// Get data from range
var data = Globals.ThisAddIn.Application.ActiveSheet.Range[range].Value;
// Calculate CP, CPK, PP, PPK, CPM, PPM, P-value, min, max, and avg
double cp = Cp(data, lsl, usl);
double cpk = Cpk(data, lsl, usl);
double pp = Pp(data, lsl, usl);
double ppk = Ppk(data, lsl, usl);
double cpm = Cpm(data, lsl, usl);
double ppm = Ppm(data, lsl, usl);
double pValue = PValue(data);
double min = Min(data);
double max = Max(data);
double average = Average(data);
// Create new sheet with column name
var newSheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.Worksheets.Add();
newSheet.Name = columnName;
// Add results to sheet
newSheet.Cells[1, 1] = "CP";
newSheet.Cells[1, 2] = cp;
newSheet.Cells[2, 1] = "CPK";
newSheet.Cells[2, 2] = cpk;
newSheet.Cells[3, 1] = "PP";
newSheet.Cells[3, 2] = pp;
newSheet.Cells[4, 1] = "PPK";
newSheet.Cells[4, 2] = ppk;
newSheet.Cells[5, 1] = "CPM";
newSheet.Cells[5, 2] = cpm;
newSheet.Cells[6, 1] = "PPM";
newSheet.Cells[6, 2] = ppm;
newSheet.Cells[7, 1] = "P-Value";
newSheet.Cells[7, 2] = pValue;
newSheet.Cells[8, 1] = "Min";
newSheet.Cells[8, 2] = min;
newSheet.Cells[9, 1] = "Max";
newSheet.Cells[9, 2] = max;
newSheet.Cells[10, 1] = "Avg";
newSheet.Cells[10, 2] = average;
}
private double Cp(object[,] data, double lsl, double usl)
{
return (usl - lsl) / (6 * StandardDeviation(data));
}
private double Cpk(object[,] data, double lsl, double usl)
{
return Math.Min((usl - Mean(data)) / (3 * StandardDeviation(data)), (Mean(data) - lsl) / (3 * StandardDeviation(data)));
}
private double Pp(object[,] data, double lsl, double usl)
{
return (Max(data) - Min(data)) / (6 * StandardDeviation(data));
}
private double Ppk(object[,] data, double lsl, double usl)
{
return Math.Min((usl - Mean(data)) / (3 * StandardDeviation(data)), (Mean(data) - lsl) / (3 * StandardDeviation(data)));
}
private double Cpm(object[,] data, double lsl, double usl)
{
return (usl - lsl) / (6 * StandardDeviation(data));
}
private double Ppm(object[,] data, double lsl, double usl)
{
return (Max(data) - Min(data)) / (6 * StandardDeviation(data));
}
private double PValue(object[,] data)
{
string range = textBox2.Text;
return (double)Globals.ThisAddIn.Application.ActiveSheet.Evaluate("NORM.S.TEST(" + range + ", TRUE)");
}
private double Mean(object[,] data)
{
return data.Cast<double>().Average();
}
private double StandardDeviation(object[,] data)
{
string range = textBox2.Text;
return (double)Globals.ThisAddIn.Application.ActiveSheet.Evaluate("STDEV.S(" + range + ")");
}
private double Min(object[,] data)
{
return data.Cast<double>().Min();
}
private double Max(object[,] data)
{
return data.Cast<double>().Max();
}
private double Average(object[,] data)
{
return data.Cast<double>().Average();
}
}
}
Sign in to answer