Statistical results varying from excel vsto to minitab

Ramadas Ravikumar 46 Reputation points
2023-01-02T03:59:13.72+00:00

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

  1. 9
  2. 2
  3. 8
  4. 2
  5. 8
    2
    2
  6. 4
    2
  7. 3
  8. 1
  9. 9
    2
  10. 1
  11. 2
  12. 9
    2
  13. 3
  14. 3
    2
  15. 2
    2
    2
  16. 9
  17. 1
  18. 3
  19. 1
  20. 8
  21. 2
  22. 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();  
        }  
  
    }  
  
}  
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
11,039 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
4,009 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,730 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.