public List <double[]> fillTable1(List <Variable> variablesD, List <Variable> variablesI, DataSet dataSet, int col, _Worksheet sheet) { List <double[]> final = new List <double[]>(); List <int> posN = new List <int>(); List <String> no = new List <String>(); List <int> posY = new List <int>(); string ran2 = variablesD[0].Range.ToString(); Array distCat = dataSet.getWorksheet().Range[ran2].Value; no = distCat.OfType <String>().ToList(); int i = 0; foreach (String str in no) { if (str.Equals("No")) { posN.Add(i); } else { posY.Add(i); } i++; } sheet.Cells[3, 2] = posN.Count; sheet.Cells[4, 2] = posY.Count; i = 0; foreach (Variable var in variablesI) { double[] meanN = new double[posN.Count]; double[] meanY = new double[posY.Count]; ran2 = variablesI[i].Range.ToString(); distCat = dataSet.getWorksheet().Range[ran2].Value; List <double> all = distCat.OfType <double>().ToList(); int j = 0; foreach (int val in posN) { meanN[j] = all[val]; j++; } j = 0; foreach (int val in posY) { meanY[j] = all[val]; j++; } final.Add(meanN); final.Add(meanY); sheet.Cells[3, 3 + i] = meanN.Average(); sheet.Cells[4, 3 + i] = meanY.Average(); i++; } return(final); }
public void generateChart(Array arr, List <Variable> variables, _Worksheet sheet, DataSet dataSet) { sheet.Cells[1, 1] = "Autocorrelation Table"; sheet.Cells[1, 2] = variables[0].name; sheet.Cells[2, 1] = "Number of Values"; sheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")"; int length = Convert.ToInt32((sheet.Cells[2, 2] as Range).Value); sheet.Cells[3, 1] = "Standard Error"; sheet.Cells[3, 2] = 1.0 / Math.Sqrt(length); int rows = 4; int i = 1; double[] vals = new double[length]; int count = 0; foreach (var item in arr) { double temp = Convert.ToDouble(item); vals[count] = temp; count = count + 1; } double var = sheet.Application.WorksheetFunction.Var(vals); double average = vals.Average(); while (i <= 12) { double solution = 0; double[] temp1 = new double[length - i]; double[] temp2 = new double[length - i]; List <double> valsL = new List <double>(vals); valsL.RemoveRange(0, i); valsL.CopyTo(temp1); List <double> valsL2 = new List <double>(vals); //valsL2.Reverse(); valsL2.RemoveRange(length - i, i); //valsL2.Reverse(); valsL2.CopyTo(temp2); int j = 0; while (j < valsL2.Count) { solution = solution + ((temp2[j] - average) * (temp1[j] - average)); j++; } solution = solution / var; //double covar = sheet.Application.WorksheetFunction.Covar(temp1, temp2); sheet.Cells[rows, 1] = "Lag #" + i; sheet.Cells[rows++, 2] = solution / (length - 1); i = i + 1; } }
public double[,] calcXdata(int length, DataSet dataSet, List <Variable> variablesI) { System.Diagnostics.Debug.WriteLine("lengte xdata is = {0}", length); int count = 0; double[,] xData = new double[length, variablesI.Count + 1]; //bereken (Xi-Xgem) while (count < length) { xData[count, 0] = 1; count++; } count = 0; foreach (Variable var in variablesI) { int i = 0; string ran = variablesI[count].Range.ToString(); Array arr = dataSet.getWorksheet().Range[ran].Value; double[] vals = new double[length]; foreach (var item in arr) { if (item.GetType() == typeof(string)) { MessageBox.Show("Please use numeric data", "Invalid data type error"); break; } double temp = Convert.ToDouble(item); xData[i, count + 1] = temp; //xData[count,i] = (temp - meansI[count]); i++; } count++; } return(xData); }
public static DataSet modify(DataSet dataSet, string newRange) { string tmp = dataSet.Range; int temp = tmp.IndexOf(':'); int start = tmp.IndexOf('$', temp + 1) + 1; char[] ch = tmp.ToCharArray(); ch[start] = newRange.ToCharArray()[0]; string newtmp = new string(ch); System.Diagnostics.Debug.WriteLine(tmp); System.Diagnostics.Debug.WriteLine(newtmp); Range range = dataSet.getWorksheet().Range[newtmp]; range.Select(); List <Variable> variables = createVariables(dataSet.getWorksheet(), range, dataSet.getRangeLayout(), dataSet.getVariableNamesInFirstRowOrColumn()); DataSet test = new DataSet(dataSet.getWorksheet(), range, dataSet.getName(), dataSet.getRangeLayout(), dataSet.getVariableNamesInFirstRowOrColumn(), variables); return(test); }
public void setVariableNamesInFirstRowOrColumn(DataSet dataSet, bool variableNamesInFirstRowOrColumn) { if (dataSet == null) { return; } DataSet newDataSet = DataSetFactory.create(dataSet.getWorksheet(), dataSet.getRange(), dataSet.getName(), dataSet.getRangeLayout(), variableNamesInFirstRowOrColumn); model.swapDataSets(dataSet, newDataSet); }
public void createConfidence(List <Variable> variables, DataSet dataSet) { _Worksheet worksheet = WorksheetHelper.NewWorksheet("Confidence"); if (variables.Count == 1) { foreach (Variable variable in variables) { worksheet.Cells[1, 1] = "Conf. intervals (Proportion)"; worksheet.Cells[1, 2] = variable.name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")"; worksheet.Cells[3, 1] = "Sample proportion"; worksheet.Cells[3, 2] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variable.Range + "," + 0 + ") / " + (worksheet.Cells[2, 2] as Range).Value; PrintCategories(worksheet, 4, dataSet); } } else if (variables.Count == 2) { worksheet.Cells[1, 1] = "Conf. intervals (Proportion)"; worksheet.Cells[1, 2] = variables[0].name; worksheet.Cells[1, 3] = variables[1].name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")"; worksheet.Cells[2, 3] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")"; worksheet.Cells[3, 1] = "Sample proportion"; worksheet.Cells[3, 2] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + "," + 0 + ") / " + (worksheet.Cells[2, 2] as Range).Value; worksheet.Cells[3, 3] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + "," + 0 + ") / " + (worksheet.Cells[2, 3] as Range).Value; PrintCategories2(worksheet, 4, dataSet); } }
public bool checkInput(List <Variable> variablesX, DataSet dataSet) { string ran = variablesX[0].Range.ToString(); Array arr = dataSet.getWorksheet().Range[ran].Value; if (dataSet != null) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Autocorrelation"); generateChart(arr, variablesX, sheet, dataSet); return(true); } else { MessageBox.Show("Please correct all fields to generate Autocorrelation", "Autocorrelation error"); } return(false); }
public double[] calcYdata(List <Variable> variablesD, DataSet dataSet, int length) { //bereken (Yi-Ygem) int count = 0; double[] yData = new double[length]; foreach (Variable var in variablesD) { string ran = variablesD[0].Range.ToString(); Array arr = dataSet.getWorksheet().Range[ran].Value; double[] vals = new double[length]; foreach (var item in arr) { double temp = Convert.ToDouble(item); yData[count] = temp; //temp2[count] = (temp - meansD); count++; } } return(yData); }
public void createHypothese(List <Variable> variables, DataSet dataSet) { _Worksheet worksheet = WorksheetHelper.NewWorksheet("Hypothesis test"); if (variables.Count == 1) { foreach (Variable variable in variables) { worksheet.Cells[1, 1] = "Hypothesis test"; worksheet.Cells[1, 2] = variable.name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")"; worksheet.Cells[3, 1] = "Sample mean"; worksheet.Cells[3, 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")"; worksheet.Cells[4, 1] = "Sample Std. Dev"; worksheet.Cells[4, 2] = "=STDEV(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")"; if (model.equal) { PrintCategories(worksheet, 5, "equal", dataSet); } else if (model.greater) { PrintCategories(worksheet, 5, "greater", dataSet); } } } else if (variables.Count == 2) { worksheet.Cells[1, 1] = "Hypothesis test"; worksheet.Cells[1, 2] = variables[0].name + " - " + variables[1].name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")"; worksheet.Cells[3, 1] = "Sample mean"; worksheet.Cells[3, 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ") - AVERAGE(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")"; worksheet.Cells[4, 1] = "Sample Std. Dev"; int length = Convert.ToInt32((worksheet.Cells[2, 2] as Range).Value); double[] vals1 = new double[length]; string ran1 = variables[0].Range.ToString(); Array arr1 = dataSet.getWorksheet().Range[ran1].Value; double[] vals2 = new double[length]; string ran2 = variables[1].Range.ToString(); Array arr2 = dataSet.getWorksheet().Range[ran2].Value; int count = 0; foreach (var item in arr1) { double temp = Convert.ToDouble(item); vals1[count] = temp; count = count + 1; } count = 0; foreach (var item in arr2) { double temp = Convert.ToDouble(item); vals2[count] = temp; count = count + 1; } count = 0; while (count < vals2.Length) { vals2[count] = vals2[count] - vals1[count]; count++; } double std = worksheet.Application.WorksheetFunction.StDev(vals2); worksheet.Cells[4, 2] = std; //worksheet.Cells[4, 2] = "=STDEV(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ") + STDEV(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")"; if (model.equal) { PrintCategories(worksheet, 5, "equal", dataSet); } else if (model.greater) { PrintCategories(worksheet, 5, "greater", dataSet); } } }
public void createCorrelationCovariance(List <Variable> variablesX, DataSet dataSet) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Correlation Covariance"); int col = 1; int row = 1; int countX = variablesX.Count; int i = 0; sheet.Cells[row, col] = "Correlation"; sheet.Cells[row + countX + 2, col] = "Covariance"; while (i < variablesX.Count) { col = col + 1; sheet.Cells[row, col] = variablesX[i].name.ToString(); i++; } i = 0; col = 1; row = 1; while (i < variablesX.Count) { row = row + 1; sheet.Cells[row, col] = variablesX[i].name.ToString(); i++; } row = countX + 3; col = 1; i = 0; while (i < variablesX.Count) { col = col + 1; sheet.Cells[row, col] = variablesX[i].name.ToString(); i++; } i = 0; col = 1; row = countX + 3; while (i < variablesX.Count) { row = row + 1; sheet.Cells[row, col] = variablesX[i].name.ToString(); i++; } // CORRELATION i = 0; row = 2; col = 2; while (i < variablesX.Count) { String range1 = variablesX[i].Range; int j = 0; while (j < variablesX.Count) { String range2 = variablesX[j].Range; sheet.Cells[row + i, col + j] = "=CORREL(" + dataSet.getWorksheet().Name + "!" + range1 + "," + dataSet.getWorksheet().Name + "!" + range2 + ")"; j++; } i++; } // COVARIANCE i = 0; row = countX + 4; col = 2; while (i < variablesX.Count) { String range1 = variablesX[i].Range; int j = 0; while (j < variablesX.Count) { String range2 = variablesX[j].Range; sheet.Cells[row + i, col + j] = "=COVARIANCE.S(" + dataSet.getWorksheet().Name + "!" + range1 + "," + dataSet.getWorksheet().Name + "!" + range2 + ")"; j++; } i++; } }
private void generatePChart(int startindex, int stopindex, DataSet dataSet, _Worksheet sheet, int plotstartindex, int plotstopindex) { int index = 0; int row = 1; int column = 1; double[] pValues = new double[dataSet.amountOfVariables() - 1]; double[] averageOfPValues = new double[dataSet.amountOfVariables() - 1]; double[] pValuesInRange = new double[stopindex - startindex + 1]; double[] pChartUpperControlLimit = new double[dataSet.amountOfVariables()]; double[] pChartLowerControlLimit = new double[dataSet.amountOfVariables()]; sheet.Cells[row, column] = "Index"; sheet.Cells[row, column + 1] = "Observation"; sheet.Cells[row, column + 2] = "Average"; for (index = 1; index < dataSet.amountOfVariables(); index++) { row++; sheet.Cells[row, column] = index - 1; sheet.Cells[row, column + 1] = dataSet.getVariables()[index].name; sheet.Cells[row, column + 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")"; var cellValue = (double)(sheet.Cells[row, column + 2] as Range).Value; if (cellValue < -214682680) { cellValue = 0; // if cellValue is the result of a division by 0, set value to 0 } pValues[index - 1] = cellValue; if (cellValue > 1) { MessageBox.Show("Cannot generate P-Chart; Data in " + dataSet.Name + " contains samples greater than 1"); return; } } for (index = startindex; index <= stopindex; index++) { pValuesInRange[index - startindex] = pValues[index]; } double pChartCorrection = Math.Pow(((pValuesInRange.Average() * (1 - pValuesInRange.Average())) / dataSet.rangeSize()), (0.3333333)); for (index = 0; index < dataSet.amountOfVariables() - 1; index++) { averageOfPValues[index] = pValuesInRange.Average(); pChartUpperControlLimit[index] = pValues.Average() + pChartCorrection; pChartLowerControlLimit[index] = pValues.Average() - pChartCorrection; } // new arrays for subsets of existing arrays but within limits double[] plotpValues = new double[plotstopindex - plotstartindex + 1]; double[] plotaverageOfPValues = new double[plotstopindex - plotstartindex + 1]; double[] plotpChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1]; double[] plotpChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1]; int[] ArrayIndex = new int[plotstopindex - plotstartindex + 1]; for (int i = plotstartindex; i <= plotstopindex; i++) { plotpValues[i - plotstartindex] = pValues[i]; plotaverageOfPValues[i - plotstartindex] = averageOfPValues[i]; plotpChartLowerControlLimit[i - plotstartindex] = pChartLowerControlLimit[i]; plotpChartUpperControlLimit[i - plotstartindex] = pChartUpperControlLimit[i]; ArrayIndex[i - plotstartindex] = i; } var Xcharts = (ChartObjects)sheet.ChartObjects(); var XchartObject = Xcharts.Add(340, 20, 550, 300); var Xchart = XchartObject.Chart; Xchart.ChartType = XlChartType.xlXYScatterLines; Xchart.ChartWizard(Title: "P-Chart " + dataSet.Name, HasLegend: true); var XseriesCollection = (SeriesCollection)Xchart.SeriesCollection(); var pseries = XseriesCollection.NewSeries(); var CLseries = XseriesCollection.NewSeries(); var UCLseries = XseriesCollection.NewSeries(); var LCLseries = XseriesCollection.NewSeries(); pseries.Name = ("Proportion"); CLseries.Name = ("Center line"); UCLseries.Name = ("UCL"); LCLseries.Name = ("LCL"); pseries.Values = plotpValues; CLseries.Values = plotaverageOfPValues; UCLseries.Values = plotpChartUpperControlLimit; LCLseries.Values = plotpChartLowerControlLimit; pseries.XValues = ArrayIndex; CLseries.XValues = ArrayIndex; UCLseries.XValues = ArrayIndex; LCLseries.XValues = ArrayIndex; }
public void Interaction(Variable variable, Variable variable2, DataSet dataSet) //cat = gender var = salary { String colLetter = dataSet.getVariables()[dataSet.getVariables().Count - 1].Range[1].ToString(); int columnIndex = ColumnLetterToColumnIndex(colLetter) + 1; string ran2 = variable.Range.ToString(); String colLetterCat = variable.Range[1].ToString(); int columnIndexCat = ColumnLetterToColumnIndex(colLetterCat) - 1; Array distCat = dataSet.getWorksheet().Range[ran2].Value; Array cat = dataSet.getWorksheet().Range[ran2].Value; string ran = variable2.Range.ToString(); String colLetterVar = variable2.Range[1].ToString(); int columnIndexVar = ColumnLetterToColumnIndex(colLetterVar) - 1; Array dist = dataSet.getWorksheet().Range[ran].Value; int count = 0; foreach (var item in distCat) { if (item == null) { count = 2; } else if (item.GetType().ToString() == "System.String") { count = 1; break; } else { count = 2; break; } } foreach (var item in dist) { if (item == null) { count = 2; } else if (item.GetType().ToString() == "System.String") { count = 1; break; } else { count = 2; break; } } if (count == 1) { MessageBox.Show("Interaction can only be calculated for numbers."); return; } double[] var1 = distCat.OfType <double>().ToArray(); double[] var2 = dist.OfType <double>().ToArray(); System.Diagnostics.Debug.WriteLine(var1.Length); System.Diagnostics.Debug.WriteLine(var2.Length); int row = 1; int column = columnIndex; worksheet.Cells[row, column] = dataSet.getVariables()[columnIndexCat].name + " - " + dataSet.getVariables()[columnIndexVar].name; row = 0; int len = var1.Length; if (var2.Length < var1.Length) { len = var2.Length; } while (row < len) { if (var1.Length < var2.Length) { column = columnIndex; int temp = var2.Length - var1.Length; worksheet.Cells[row + 2 + temp, column] = var1[row] * var2[row + temp]; row = row + 1; } else { column = columnIndex; int temp = var1.Length - var2.Length; worksheet.Cells[row + 2 + temp, column] = var1[row + temp] * var2[row]; row = row + 1; } } DataSet nwRange = DataSetFactory.modify(dataSet, ColumnIndexToColumnLetter(column)); dataSet.setRange(nwRange.getRange()); dataSet.setVariables(nwRange.getVariables()); }
public void addUnstacked(Variable category, Variable variable, DataSet dataSet) //cat = gender var = salary { string ran2 = category.Range.ToString(); String colLetterCat = category.Range[1].ToString(); int columnIndexCat = ColumnLetterToColumnIndex(colLetterCat) - 1; Array distCat = dataSet.getWorksheet().Range[ran2].Value; Array cat = dataSet.getWorksheet().Range[ran2].Value; string ran = variable.Range.ToString(); String colLetterVar = variable.Range[1].ToString(); int columnIndexVar = ColumnLetterToColumnIndex(colLetterVar) - 1; Array dist = dataSet.getWorksheet().Range[ran].Value; int count = 0; Worksheet newworksheet; try { newworksheet = dataSet.getWorksheet().Application.Worksheets.Add(); newworksheet.Name = "Unstack" + dataSet.getVariables()[columnIndexVar].name + "-" + dataSet.getVariables()[columnIndexCat].name; } catch (SystemException e) { MessageBox.Show("Unstacked data already exists, change name of existing worksheet and try again."); return; } foreach (var item in distCat) { if (item == null) { count = 2; } else if (item.GetType().ToString() == "System.String") { count = 1; } else { count = 2; } break; } if (count == 1) { List <String> valuesCat = distCat.OfType <String>().ToList(); List <Double> valuesVar = dist.OfType <Double>().ToList(); distCat = valuesCat.Distinct <String>().ToArray(); int row = 1; int column = 1; foreach (var item in distCat) { newworksheet.Cells[row, column] = dataSet.getVariables()[columnIndexVar].name + "(" + item.ToString() + ")"; column = column + 1; } column = 1; foreach (var item in distCat) { row = 0; int counter = row; while (row < valuesVar.Count) { String temp = valuesCat[row]; if (temp.Equals(item.ToString())) { newworksheet.Cells[counter + 2, column] = valuesVar[row]; counter = counter + 1; } row = row + 1; } column = column + 1; } } else { List <Double> valueSortCat = distCat.OfType <Double>().ToList(); // één lijst gaat gesorteerd worden en de andere niet List <Double> valueCat = distCat.OfType <Double>().ToList(); List <Double> valuesVar = dist.OfType <Double>().ToList(); valueSortCat.Sort(); distCat = valueSortCat.Distinct <Double>().ToArray(); int row = 1; int column = 1; foreach (var item in distCat) { newworksheet.Cells[row, column] = dataSet.getVariables()[columnIndexVar].name + "(" + item.ToString() + ")"; column = column + 1; } column = 1; foreach (var item in distCat) { row = 0; int counter = row; while (row < valuesVar.Count) { double temp = valueCat[row]; if (temp == Convert.ToDouble(item.ToString())) { newworksheet.Cells[counter + 2, column] = valuesVar[row]; counter = counter + 1; } row = row + 1; } column = column + 1; } } }
public void addDummy(Variable variable, DataSet dataSet) { string ran = variable.Range.ToString(); String colLetter2 = variable.Range[1].ToString(); int columnIndex2 = ColumnLetterToColumnIndex(colLetter2) - 1; String colLetter = dataSet.getVariables()[dataSet.getVariables().Count - 1].Range[1].ToString(); int columnIndex = ColumnLetterToColumnIndex(colLetter) + 1; Array dist = dataSet.getWorksheet().Range[ran].Value; int count = 0; foreach (var item in dist) { if (item.GetType().ToString() == "System.String") { count = 1; } else { count = 2; } break; } if (count == 1) { List <String> values = dist.OfType <String>().ToList(); dist = values.Distinct <String>().ToArray(); int row = 1; int column = columnIndex; foreach (var item in dist) { worksheet.Cells[row, column] = dataSet.getVariables()[columnIndex2].name + "=" + item.ToString(); column = column + 1; } row = 0; while (row < values.Count) { String temp = values[row]; column = columnIndex; foreach (var item in dist) { if (temp.Equals(item.ToString())) { worksheet.Cells[row + 2, column] = "1"; } else { worksheet.Cells[row + 2, column] = "0"; } column = column + 1; } row = row + 1; } DataSet nwRange = DataSetFactory.modify(dataSet, ColumnIndexToColumnLetter(column - 1)); dataSet.setRange(nwRange.getRange()); dataSet.setVariables(nwRange.getVariables()); } else { List <Double> values = dist.OfType <Double>().ToList(); List <Double> value = dist.OfType <Double>().ToList(); values.Sort(); dist = values.Distinct <Double>().ToArray(); int row = 1; int column = columnIndex; foreach (var item in dist) { worksheet.Cells[row, column] = dataSet.getVariables()[columnIndex2].name + "=" + item.ToString(); column = column + 1; } row = 0; while (row < values.Count) { double temp = value[row]; column = columnIndex; foreach (var item in dist) { if (temp == Convert.ToInt16(item)) { worksheet.Cells[row + 2, column] = "1"; } else { worksheet.Cells[row + 2, column] = "0"; } column = column + 1; } row = row + 1; } DataSet nwRange = DataSetFactory.modify(dataSet, ColumnIndexToColumnLetter(column - 1)); dataSet.setRange(nwRange.getRange()); dataSet.setVariables(nwRange.getVariables()); } }
public void generateProcessCapability(int LSL, int USL, DataSet dataSet, _Worksheet sheet) { int index = 0; int row = 1; int column = 1; double correctionFactor1, correctionFactor2; double[] averages = new double[dataSet.amountOfVariables()]; double[] Rvalues = new double[dataSet.amountOfVariables()]; double[] averageOfAverages = new double[dataSet.amountOfVariables()]; double[] xChartUpperControlLimit = new double[dataSet.amountOfVariables()]; double[] xChartLowerControlLimit = new double[dataSet.amountOfVariables()]; double[] rChartUpperControlLimit = new double[dataSet.amountOfVariables()]; double[] rChartLowerControlLimit = new double[dataSet.amountOfVariables()]; double[] averageOfRvalues = new double[dataSet.amountOfVariables()]; int[] ArrayIndex = new int[dataSet.amountOfVariables()]; double[] constantC4 = new double[25] { 0.0, 0.7979, 0.8862, 0.9213, 0.9400, 0.9515, 0.9594, 0.9650, 0.9693, 0.9727, 0.9754, 0.9776, 0.9794, 0.9810, 0.9823, 0.9835, 0.9845, 0.9854, 0.9862, 0.9869, 0.9876, 0.9882, 0.9887, 0.9892, 0.9896 }; double[] constantD2 = new double[25] { 0.0, 1.128, 1.693, 2.059, 2.326, 2.534, 2.704, 2.847, 2.970, 3.078, 3.173, 3.258, 3.336, 3.407, 3.472, 3.532, 3.588, 3.640, 3.689, 3.735, 3.778, 3.819, 3.858, 3.895, 3.931 }; sheet.Cells[row, column] = "Index"; sheet.Cells[row, column + 1] = "Observation"; sheet.Cells[row, column + 2] = "Average"; sheet.Cells[row, column + 3] = "Max"; sheet.Cells[row, column + 4] = "Min"; sheet.Cells[row, column + 5] = "R"; for (index = 1; index < dataSet.amountOfVariables(); index++) { row++; sheet.Cells[row, column] = index - 1; sheet.Cells[row, column + 1] = dataSet.getVariables()[index].name; sheet.Cells[row, column + 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")"; sheet.Cells[row, column + 3] = "=MAX(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")"; sheet.Cells[row, column + 4] = "=MIN(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")"; sheet.Cells[row, column + 5] = (double)(sheet.Cells[row, column + 3] as Range).Value - (double)(sheet.Cells[row, column + 4] as Range).Value; ArrayIndex[index - 1] = index; var cellValue = (double)(sheet.Cells[row, column + 2] as Range).Value; if (cellValue < -214682680) { cellValue = 0; // if cellValue is the result of a division by 0, set value to 0 } averages[index - 1] = cellValue; cellValue = (double)(sheet.Cells[row, column + 5] as Range).Value; Rvalues[index - 1] = cellValue; } // create variable to limit amount of subsamples in 1 measurement to 25 int safe; if (dataSet.rangeSize() > 24) { safe = 24; } else { safe = dataSet.rangeSize(); } if (dataSet.getVariableNamesInFirstRowOrColumn()) { correctionFactor1 = constantC4[safe - 1]; correctionFactor2 = constantD2[safe - 1]; } else { correctionFactor1 = constantC4[safe]; } correctionFactor2 = constantD2[safe]; double sigma = Rvalues.Average() / correctionFactor2; double Cp = (USL - LSL) / (6 * sigma); double Cpk = Math.Min((USL - averages.Average()) / (3 * sigma), (averages.Average() - LSL) / (3 * sigma)); row = 1; column = 1; sheet.Cells[row, column + 7] = "LSL"; sheet.Cells[row, column + 8] = "USL"; sheet.Cells[row, column + 9] = "Cp"; sheet.Cells[row, column + 10] = "Cpk"; row++; sheet.Cells[row, column + 7] = LSL; sheet.Cells[row, column + 8] = USL; sheet.Cells[row, column + 9] = Cp; sheet.Cells[row, column + 10] = Cpk; }
public void createChiKwadraatPlot(DataSet dataSet) { string range = model.range.Replace("$", null); //string range = model.range; //string[] splitted = range.Split(':'); string first = range.Split(':')[0]; string second = range.Split(':')[1]; /*if (splitted[0].Length > 4) * { * int i = splitted[0].Length; * first = splitted[0][1] + Convert.ToString(splitted[0][3]); * int j = 4; * while (j < i) * { * first = first + Convert.ToString(splitted[0][j]); * j++; * } * } * else * { * first = splitted[0][1] + Convert.ToString(splitted[0][3]); * } * if (splitted[1].Length > 4) * { * int i = splitted[1].Length; * second = splitted[1][1] + Convert.ToString(splitted[1][3]); * int j = 4; * while (j < i) * { * second = second + Convert.ToString(splitted[1][j]); * j++; * } * } * else * { * second = splitted[1][1] + Convert.ToString(splitted[1][3]); * }*/ _Worksheet sheet = WorksheetHelper.NewWorksheet("Chi Square"); Range from = dataSet.getWorksheet().Range[first, second]; int rangeWidth = from.Columns.Count; int rangeHeight = from.Rows.Count; //original Counts sheet.Cells[2, 1] = "Original Counts"; //sheet.get_Range("A2").Cells.HorizontalAlignment = XlHAlign.xlHAlignFill; sheet.get_Range("A2", AddressConverter.CellAddress(2, rangeWidth + 2, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; Range to = sheet.Range[sheet.Cells[3, 2], sheet.Cells[3, 2].offset(rangeHeight, rangeWidth)]; from.Copy(to); int col = 2; while (col < rangeWidth + 2) { int row = 3; double sum = 0; while (row < rangeHeight + 3) { sum += Convert.ToDouble((sheet.Cells[row, col] as Range).Value); row++; } sheet.Cells[3 + rangeHeight, col] = sum; col++; } sheet.Cells[2, col] = "Total"; int roww = 3; while (roww < rangeHeight + 4) { int coll = 2; double summ = 0; while (coll < rangeWidth + 2) { summ += Convert.ToDouble((sheet.Cells[roww, coll] as Range).Value); coll++; } sheet.Cells[roww, 2 + rangeWidth] = summ; roww++; } sheet.Cells[roww - 1, 1] = "Total"; // percentage of Rows sheet.Cells[rangeHeight + 6, 1] = "Percentage of Rows"; sheet.get_Range("A2", AddressConverter.CellAddress(rangeHeight + 6, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; int rowb = rangeHeight + 7; while (rowb < rangeHeight + rangeHeight + 7) { int cola = 2; while (cola < rangeWidth + 2) { double proda = 0; proda = Convert.ToDouble((sheet.Cells[rowb - rangeHeight - 4, cola] as Range).Value) / Convert.ToDouble((sheet.Cells[rowb - rangeHeight - 4, rangeWidth + 2] as Range).Value); sheet.Cells[rowb, cola] = Math.Round(proda * 100, 2); //sheet.Range[rowb, cola].NumberFormat = "0.00%"; cola++; } rowb++; } //Percentage of Colums sheet.Cells[rangeHeight + rangeHeight + 9, 1] = "Percentage of Colums"; sheet.get_Range("A2", AddressConverter.CellAddress(2 * rangeHeight + 9, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; int rowd = rangeHeight + rangeHeight + 10; while (rowd < rangeHeight + rangeHeight + rangeHeight + 10) { int colc = 2; while (colc < rangeWidth + 2) { double prodc = 0; prodc = Convert.ToDouble((sheet.Cells[rowd - rangeHeight - rangeHeight - 7, colc] as Range).Value) / Convert.ToDouble((sheet.Cells[3 + rangeHeight, colc] as Range).Value); sheet.Cells[rowd, colc] = Math.Round(prodc * 100, 2); //sheet.Range[rowd, colc].NumberFormat = "0.00%"; colc++; } rowd++; } // Expected Counts sheet.Cells[3 * (rangeHeight) + 12, 1] = "Expected Counts"; sheet.get_Range("A2", AddressConverter.CellAddress(3 * rangeHeight + 12, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; int rowe = 3 * rangeHeight + 13; while (rowe < 4 * (rangeHeight) + 13) { int cole = 2; while (cole < rangeWidth + 2) { double prode = 0; prode = Convert.ToDouble((sheet.Cells[rangeHeight + 3, cole] as Range).Value) * Convert.ToDouble((sheet.Cells[rowe - 3 * (rangeHeight) - 10, rangeWidth + 2] as Range).Value) / Convert.ToDouble((sheet.Cells[rangeHeight + 3, rangeWidth + 2] as Range).Value); sheet.Cells[rowe, cole] = prode; cole++; } rowe++; } //distance ftom expected sheet.Cells[4 * rangeHeight + 15, 1] = "Distance from Expected"; sheet.get_Range("A2", AddressConverter.CellAddress(4 * rangeHeight + 15, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; int rowf = 4 * rangeHeight + 16; while (rowf < 5 * rangeHeight + 16) { int colf = 2; while (colf < rangeWidth + 2) { double prodf = 0; double tussenprod = Convert.ToDouble((sheet.Cells[rowf - 4 * rangeHeight - 13, colf] as Range).Value) - Convert.ToDouble((sheet.Cells[rowf - rangeHeight - 3, colf] as Range).Value); prodf = Math.Pow(tussenprod, 2) / Convert.ToDouble((sheet.Cells[rowf - rangeHeight - 3, colf] as Range).Value); sheet.Cells[rowf, colf] = Math.Round(prodf, 4); colf++; } rowf++; } //chi square dings sheet.Cells[5 * rangeHeight + 18, 1] = "Chi-Square Statistic"; sheet.Cells[5 * rangeHeight + 19, 1] = "CHi-Square"; sheet.Cells[5 * rangeHeight + 20, 1] = "p-value"; //=1-CHIKW.VERD(B39;3*3;1) sheet.get_Range("A2", AddressConverter.CellAddress(5 * rangeHeight + 18, 2, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; int rowg = 4 * rangeHeight + 16; double som = 0; while (rowg < 5 * rangeHeight + 16) { int colg = 2; while (colg < rangeWidth + 2) { som += Convert.ToDouble((sheet.Cells[rowg, colg] as Range).Value); colg++; } rowg++; } sheet.Cells[5 * rangeHeight + 19, 2] = som; sheet.Cells[5 * rangeHeight + 20, 2] = sheet.Application.WorksheetFunction.ChiSq_Dist(som, (rangeHeight - 1) * (rangeWidth - 1), false); //autofit ((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 7]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 8]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 9]).EntireColumn.AutoFit(); //((Range)sheet.Cells[1, 10]).EntireColumn.AutoFit(); // Merge Headers sheet.Range[sheet.Cells[1, 2], sheet.Cells[1, rangeWidth + 2]].Merge(); sheet.Range[sheet.Cells[rangeHeight + 5, 2], sheet.Cells[rangeHeight + 5, rangeWidth + 1]].Merge(); sheet.Range[sheet.Cells[2 * rangeHeight + 8, 2], sheet.Cells[2 * rangeHeight + 8, rangeWidth + 1]].Merge(); sheet.Range[sheet.Cells[3 * rangeHeight + 11, 2], sheet.Cells[3 * rangeHeight + 11, rangeWidth + 1]].Merge(); sheet.Range[sheet.Cells[4 * rangeHeight + 14, 2], sheet.Cells[4 * rangeHeight + 14, rangeWidth + 1]].Merge(); // Build Headers var header = ""; if (model.hasRowTitle) { header += "Rows: {0}"; } if (model.hasRowTitle && model.hasColTitle) { header += " / "; } if (model.hasColTitle) { header += "Columns: {1}"; } header = String.Format(header, model.rowTitle, model.colTitle); // Fill in Headers sheet.Cells[1, 2] = header; sheet.Cells[rangeHeight + 5, 2] = header; sheet.Cells[2 * rangeHeight + 8, 2] = header; sheet.Cells[3 * rangeHeight + 11, 2] = header; sheet.Cells[4 * rangeHeight + 14, 2] = header; // Center Headers sheet.Cells[1, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Cells[rangeHeight + 5, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Cells[2 * rangeHeight + 8, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Cells[3 * rangeHeight + 11, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.Cells[4 * rangeHeight + 14, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter; // Row and column titles if (model.hasRowColHeaders) { String[] rowTitles = new String[rangeHeight]; String[] colTitles = new String[rangeWidth]; for (int i = 0; i < rangeHeight; i++) { rowTitles[i] = dataSet.getWorksheet().Range[first].Offset[i, -1].Value; } for (int i = 0; i < rangeWidth; i++) { colTitles[i] = dataSet.getWorksheet().Range[first].Offset[-1, i].Value; } for (int i = 0; i < rangeHeight; i++) { sheet.Cells[3 + i, 1] = rowTitles[i]; sheet.Cells[7 + rangeHeight + i, 1] = rowTitles[i]; sheet.Cells[10 + 2 * rangeHeight + i, 1] = rowTitles[i]; sheet.Cells[13 + 3 * rangeHeight + i, 1] = rowTitles[i]; sheet.Cells[16 + 4 * rangeHeight + i, 1] = rowTitles[i]; } for (int i = 0; i < rangeWidth; i++) { sheet.Cells[2, 2 + i] = colTitles[i]; sheet.Cells[6 + rangeHeight, 2 + i] = colTitles[i]; sheet.Cells[9 + 2 * rangeHeight, 2 + i] = colTitles[i]; sheet.Cells[12 + 3 * rangeHeight, 2 + i] = colTitles[i]; sheet.Cells[15 + 4 * rangeHeight, 2 + i] = colTitles[i]; } } }
public void generateXRChart(List <Variable> variables, int startindex, int stopindex, int plotstartindex, int plotstopindex, DataSet dataSet, int offset, _Worksheet sheet) { int counter = 0; int index = 0; int row = 1; int column = 1; double xControlLimitFactor, rControlLimitFactor1, rControlLimitFactor2; double[] averages = new double[dataSet.rangeSize()]; double[] Rvalues = new double[dataSet.rangeSize()]; double[] averageOfAverages = new double[dataSet.rangeSize()]; double[] xChartUpperControlLimit = new double[dataSet.rangeSize()]; //amountOfVariables() double[] xChartLowerControlLimit = new double[dataSet.rangeSize()]; double[] rChartUpperControlLimit = new double[dataSet.rangeSize()]; double[] rChartLowerControlLimit = new double[dataSet.rangeSize()]; double[] averageOfRvalues = new double[dataSet.rangeSize()]; double[] RvaluesInRange = new double[stopindex - startindex]; // +1 double[] averageOfAveragesInRange = new double[stopindex - startindex]; // +1 int[] ArrayIndex = new int[plotstopindex - plotstartindex + 1]; // +1 double[] xChartConstants = new double[25] { 0.0, 1.128, 1.693, 2.059, 2.326, 2.534, 2.704, 2.847, 2.970, 3.078, 3.173, 3.258, 3.336, 3.407, 3.472, 3.532, 3.588, 3.640, 3.689, 3.735, 3.778, 3.819, 3.858, 3.895, 3.931 }; double[] rChartConstants1 = new double[25] { 0, 0, 0, 0, 0, 0, 0.076, 0.136, 0.184, 0.223, 0.256, 0.283, 0.307, 0.328, 0.347, 0.363, 0.378, 0.391, 0.403, 0.415, 0.425, 0.434, 0.443, 0.451, 0.459 }; double[] rChartConstants2 = new double[25] { 0, 3.267, 2.574, 2.282, 2.114, 2.004, 1.924, 1.864, 1.816, 1.777, 1.744, 1.717, 1.693, 1.672, 1.653, 1.637, 1.662, 1.607, 1.597, 1.585, 1.575, 1.566, 1.557, 1.548, 1.541 }; sheet.Cells[row, column] = "Index"; sheet.Cells[row, column + 1] = "Observation"; sheet.Cells[row, column + 2] = "Average"; sheet.Cells[row, column + 3] = "Max"; sheet.Cells[row, column + 4] = "Min"; sheet.Cells[row, column + 5] = "R"; string colLetter = variables[0].Range[1].ToString(); int columnIndex = ColumnLetterToColumnIndex(colLetter); int columnIndex2 = columnIndex + variables.Count - 1; string colLetter2 = ColumnIndexToColumnLetter(columnIndex2); char chstart = variables[0].Range[3]; int start = (int)Char.GetNumericValue(chstart); int final = 0; for (counter = 1; counter <= dataSet.rangeSize(); counter++) //amountOfVariables() { row++; for (index = 0; index < variables.Count; index++) { sheet.Cells[row, column] = counter; if (variables.Count > 1) { sheet.Cells[row, column + 1] = variables[0].name + "-" + variables[variables.Count - 1].name; } else { sheet.Cells[row, column + 1] = variables[index].name; } } int temp = counter; if (dataSet.getVariableNamesInFirstRowOrColumn()) { temp = counter + 1; } sheet.Cells[row, column + 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!$" + colLetter + "$" + start + ":$" + colLetter2 + "$" + temp + ")"; sheet.Cells[row, column + 3] = "=MAX(" + dataSet.getWorksheet().Name + "!$" + colLetter + "$" + temp + ":$" + colLetter2 + "$" + temp + ")"; sheet.Cells[row, column + 4] = "=MIN(" + dataSet.getWorksheet().Name + "!$" + colLetter + "$" + temp + ":$" + colLetter2 + "$" + temp + ")"; sheet.Cells[row, column + 5] = (double)(sheet.Cells[row, column + 3] as Range).Value - (double)(sheet.Cells[row, column + 4] as Range).Value; var cellValue = (double)sheet.Cells[row, column + 2].Value; if (cellValue < -214682680) { cellValue = 0; // if cellValue is the result of a division by 0, set value to 0 } averages[counter - 1] = cellValue; cellValue = (double)(sheet.Cells[row, column + 5] as Range).Value; Rvalues[counter - 1] = cellValue; start = start + 1; final = temp; } for (counter = startindex; counter < stopindex; counter++) { RvaluesInRange[counter - startindex] = Rvalues[counter]; averageOfAveragesInRange[counter - startindex] = averages[counter]; } if (dataSet.getVariableNamesInFirstRowOrColumn()) { xControlLimitFactor = xChartConstants[variables.Count - 1]; rControlLimitFactor1 = rChartConstants1[variables.Count - 1]; rControlLimitFactor2 = rChartConstants2[variables.Count - 1]; } else { xControlLimitFactor = xChartConstants[variables.Count]; rControlLimitFactor1 = rChartConstants1[variables.Count]; rControlLimitFactor2 = rChartConstants2[variables.Count]; } for (counter = 0; counter <= final - 2; counter++) { averageOfAverages[counter] = averageOfAveragesInRange.Average(); xChartUpperControlLimit[counter] = averageOfAveragesInRange.Average() + 3.0 * (Rvalues.Average() / (xControlLimitFactor * Math.Sqrt(variables.Count))); xChartLowerControlLimit[counter] = averageOfAveragesInRange.Average() - 3.0 * (Rvalues.Average() / (xControlLimitFactor * Math.Sqrt(variables.Count))); averageOfRvalues[counter] = RvaluesInRange.Average(); rChartUpperControlLimit[counter] = RvaluesInRange.Average() * rControlLimitFactor2; rChartLowerControlLimit[counter] = RvaluesInRange.Average() * rControlLimitFactor1; } // new subsets of arrays for plotting data double[] plotaverages = new double[plotstopindex - plotstartindex + 1]; double[] plotRvalues = new double[plotstopindex - plotstartindex + 1]; double[] plotaverageOfAverages = new double[plotstopindex - plotstartindex + 1]; double[] plotxChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1]; double[] plotxChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1]; double[] plotrChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1]; double[] plotrChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1]; double[] plotaverageOfRvalues = new double[plotstopindex - plotstartindex + 1]; for (int i = plotstartindex; i <= plotstopindex; i++) { ArrayIndex[i - plotstartindex] = i; plotaverages[i - plotstartindex] = averages[i]; plotRvalues[i - plotstartindex] = Rvalues[i]; plotaverageOfAverages[i - plotstartindex] = averageOfAverages[i]; plotxChartUpperControlLimit[i - plotstartindex] = xChartUpperControlLimit[i]; plotxChartLowerControlLimit[i - plotstartindex] = xChartLowerControlLimit[i]; plotrChartUpperControlLimit[i - plotstartindex] = rChartUpperControlLimit[i]; plotrChartLowerControlLimit[i - plotstartindex] = rChartLowerControlLimit[i]; plotaverageOfRvalues[i - plotstartindex] = averageOfRvalues[i]; } var Xcharts = (ChartObjects)sheet.ChartObjects(); var XchartObject = Xcharts.Add(340, 20, 550, 300); var Xchart = XchartObject.Chart; Xchart.ChartType = XlChartType.xlXYScatterLines; Xchart.ChartWizard(Title: "X-Chart " + dataSet.Name, HasLegend: true); var XseriesCollection = (SeriesCollection)Xchart.SeriesCollection(); var avgseries = XseriesCollection.NewSeries(); var avgAvgseries = XseriesCollection.NewSeries(); var UCLseries = XseriesCollection.NewSeries(); var LCLseries = XseriesCollection.NewSeries(); avgseries.Name = ("Observation Averages"); avgAvgseries.Name = ("Center Line"); UCLseries.Name = ("UCL"); LCLseries.Name = ("LCL"); avgseries.Values = plotaverages; avgAvgseries.Values = plotaverageOfAverages; UCLseries.Values = plotxChartUpperControlLimit; LCLseries.Values = plotxChartLowerControlLimit; avgseries.XValues = ArrayIndex; UCLseries.XValues = ArrayIndex; LCLseries.XValues = ArrayIndex; avgAvgseries.XValues = ArrayIndex; var Rcharts = (ChartObjects)sheet.ChartObjects(); var RchartObject = Rcharts.Add(340, 20 + offset, 550, 300); var Rchart = RchartObject.Chart; Rchart.ChartType = XlChartType.xlXYScatterLines; Rchart.ChartWizard(Title: "R-Chart " + dataSet.Name, HasLegend: true); var RseriesCollection = (SeriesCollection)Rchart.SeriesCollection(); var rSeries = RseriesCollection.NewSeries(); var averageRSeries = RseriesCollection.NewSeries(); var UCLSeries = RseriesCollection.NewSeries(); var LCLSeries = RseriesCollection.NewSeries(); rSeries.Name = ("Observation R"); averageRSeries.Name = ("Center Line"); UCLSeries.Name = ("UCL"); LCLSeries.Name = ("LCL"); rSeries.Values = plotRvalues; averageRSeries.Values = plotaverageOfRvalues; UCLSeries.Values = plotrChartUpperControlLimit; LCLSeries.Values = plotrChartLowerControlLimit; rSeries.XValues = ArrayIndex; averageRSeries.XValues = ArrayIndex; UCLSeries.XValues = ArrayIndex; LCLSeries.XValues = ArrayIndex; }
public void generateXRChart(int startindex, int stopindex, int plotstartindex, int plotstopindex, DataSet dataSet, int offset, _Worksheet sheet) { // declaration of variables int index = 0; int row = 1; int column = 1; double xControlLimitFactor, rControlLimitFactor1, rControlLimitFactor2; double[] averages = new double[dataSet.amountOfVariables()]; double[] Rvalues = new double[dataSet.amountOfVariables()]; double[] averageOfAverages = new double[dataSet.amountOfVariables()]; double[] xChartUpperControlLimit = new double[dataSet.amountOfVariables()]; double[] xChartLowerControlLimit = new double[dataSet.amountOfVariables()]; double[] rChartUpperControlLimit = new double[dataSet.amountOfVariables()]; double[] rChartLowerControlLimit = new double[dataSet.amountOfVariables()]; double[] averageOfRvalues = new double[dataSet.amountOfVariables()]; double[] RvaluesInRange = new double[stopindex - startindex + 1]; double[] averageOfAveragesInRange = new double[stopindex - startindex + 1]; int[] ArrayIndex = new int[plotstopindex - plotstartindex + 1]; double[] xChartConstants = new double[25] { 0.0, 1.880, 1.023, 0.729, 0.577, 0.483, 0.419, 0.373, 0.337, 0.308, 0.285, 0.266, 0.249, 0.235, 0.223, 0.212, 0.203, 0.194, 0.187, 0.180, 0.173, 0.167, 0.162, 0.157, 0.153 }; double[] rChartConstants1 = new double[25] { 0, 0, 0, 0, 0, 0, 0.076, 0.136, 0.184, 0.223, 0.256, 0.283, 0.307, 0.328, 0.347, 0.363, 0.378, 0.391, 0.403, 0.415, 0.425, 0.434, 0.443, 0.451, 0.459 }; double[] rChartConstants2 = new double[25] { 0, 3.267, 2.574, 2.282, 2.114, 2.004, 1.924, 1.864, 1.816, 1.777, 1.744, 1.717, 1.693, 1.672, 1.653, 1.637, 1.662, 1.607, 1.597, 1.585, 1.575, 1.566, 1.557, 1.548, 1.541 }; // write labels to sheet sheet.Cells[row, column] = "Index"; sheet.Cells[row, column + 1] = "Observation"; sheet.Cells[row, column + 2] = "Average"; sheet.Cells[row, column + 3] = "Max"; sheet.Cells[row, column + 4] = "Min"; sheet.Cells[row, column + 5] = "R"; // write values to sheet for (index = 1; index < dataSet.amountOfVariables(); index++) { row++; sheet.Cells[row, column] = index - 1; sheet.Cells[row, column + 1] = dataSet.getVariables()[index].name; sheet.Cells[row, column + 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")"; sheet.Cells[row, column + 3] = "=MAX(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")"; sheet.Cells[row, column + 4] = "=MIN(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")"; sheet.Cells[row, column + 5] = (double)(sheet.Cells[row, column + 3] as Range).Value - (double)(sheet.Cells[row, column + 4] as Range).Value; var cellValue = (double)sheet.Cells[row, column + 2].Value; if (cellValue < -214682680) { cellValue = 0; // if cellValue is the result of a division by 0, set value to 0 } averages[index - 1] = cellValue; cellValue = (double)(sheet.Cells[row, column + 5] as Range).Value; Rvalues[index - 1] = cellValue; } // make arrays for calculation within limits for (index = startindex; index <= stopindex; index++) { RvaluesInRange[index - startindex] = Rvalues[index]; averageOfAveragesInRange[index - startindex] = averages[index]; } // create variable to limit amount of subsamples in 1 measurement to 25 int safe; if (dataSet.rangeSize() > 24) { safe = 24; } else { safe = dataSet.rangeSize(); } if (dataSet.getVariableNamesInFirstRowOrColumn()) { xControlLimitFactor = xChartConstants[safe - 1]; rControlLimitFactor1 = rChartConstants1[safe - 1]; rControlLimitFactor2 = rChartConstants2[safe - 1]; } else { xControlLimitFactor = xChartConstants[safe]; } rControlLimitFactor1 = rChartConstants1[safe]; rControlLimitFactor2 = rChartConstants2[safe]; for (index = 0; index < dataSet.amountOfVariables(); index++) { averageOfAverages[index] = averageOfAveragesInRange.Average(); xChartUpperControlLimit[index] = averageOfAveragesInRange.Average() + (xControlLimitFactor * Rvalues.Average()); xChartLowerControlLimit[index] = averageOfAveragesInRange.Average() - (xControlLimitFactor * Rvalues.Average()); averageOfRvalues[index] = RvaluesInRange.Average(); rChartUpperControlLimit[index] = RvaluesInRange.Average() * rControlLimitFactor2; rChartLowerControlLimit[index] = RvaluesInRange.Average() * rControlLimitFactor1; } // new subsets of arrays for plotting data double[] plotaverages = new double[plotstopindex - plotstartindex + 1]; double[] plotRvalues = new double[plotstopindex - plotstartindex + 1]; double[] plotaverageOfAverages = new double[plotstopindex - plotstartindex + 1]; double[] plotxChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1]; double[] plotxChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1]; double[] plotrChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1]; double[] plotrChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1]; double[] plotaverageOfRvalues = new double[plotstopindex - plotstartindex + 1]; for (int i = plotstartindex; i <= plotstopindex; i++) { ArrayIndex[i - plotstartindex] = i; plotaverages[i - plotstartindex] = averages[i]; plotRvalues[i - plotstartindex] = Rvalues[i]; plotaverageOfAverages[i - plotstartindex] = averageOfAverages[i]; plotxChartUpperControlLimit[i - plotstartindex] = xChartUpperControlLimit[i]; plotxChartLowerControlLimit[i - plotstartindex] = xChartLowerControlLimit[i]; plotrChartUpperControlLimit[i - plotstartindex] = rChartUpperControlLimit[i]; plotrChartLowerControlLimit[i - plotstartindex] = rChartLowerControlLimit[i]; plotaverageOfRvalues[i - plotstartindex] = averageOfRvalues[i]; } var Xcharts = (ChartObjects)sheet.ChartObjects(); var XchartObject = Xcharts.Add(340, 20, 550, 300); var Xchart = XchartObject.Chart; Xchart.ChartType = XlChartType.xlXYScatterLines; Xchart.ChartWizard(Title: "X-Chart " + dataSet.Name, HasLegend: true); var XseriesCollection = (SeriesCollection)Xchart.SeriesCollection(); var avgseries = XseriesCollection.NewSeries(); var avgAvgseries = XseriesCollection.NewSeries(); var UCLseries = XseriesCollection.NewSeries(); var LCLseries = XseriesCollection.NewSeries(); avgseries.Name = ("Observation Averages"); avgAvgseries.Name = ("Center Line"); UCLseries.Name = ("UCL"); LCLseries.Name = ("LCL"); avgseries.Values = plotaverages; avgAvgseries.Values = plotaverageOfAverages; UCLseries.Values = plotxChartUpperControlLimit; LCLseries.Values = plotxChartLowerControlLimit; avgseries.XValues = ArrayIndex; UCLseries.XValues = ArrayIndex; LCLseries.XValues = ArrayIndex; avgAvgseries.XValues = ArrayIndex; var Rcharts = (ChartObjects)sheet.ChartObjects(); var RchartObject = Rcharts.Add(340, 20 + offset, 550, 300); var Rchart = RchartObject.Chart; Rchart.ChartType = XlChartType.xlXYScatterLines; Rchart.ChartWizard(Title: "R-Chart " + dataSet.Name, HasLegend: true); var RseriesCollection = (SeriesCollection)Rchart.SeriesCollection(); var rSeries = RseriesCollection.NewSeries(); var averageRSeries = RseriesCollection.NewSeries(); var UCLSeries = RseriesCollection.NewSeries(); var LCLSeries = RseriesCollection.NewSeries(); rSeries.Name = ("Observation R"); averageRSeries.Name = ("Center Line"); UCLSeries.Name = ("UCL"); LCLSeries.Name = ("LCL"); rSeries.Values = plotRvalues; averageRSeries.Values = plotaverageOfRvalues; UCLSeries.Values = plotrChartUpperControlLimit; LCLSeries.Values = plotrChartLowerControlLimit; rSeries.XValues = ArrayIndex; averageRSeries.XValues = ArrayIndex; UCLSeries.XValues = ArrayIndex; LCLSeries.XValues = ArrayIndex; }
private void generatePChart(List <Variable> variableX, List <Variable> variableY, bool radioButton2, int startindex, int stopindex, DataSet dataSet, _Worksheet sheet, int plotstartindex, int plotstopindex) { int index = 0; int row = 1; int column = 1; double[] pValues = new double[dataSet.rangeSize()]; double[] sample = new double[dataSet.rangeSize()]; double[] averageOfPValues = new double[dataSet.rangeSize()]; double[] pValuesInRange = new double[stopindex - startindex + 1]; double[] pChartUpperControlLimit = new double[dataSet.rangeSize()]; double[] pChartLowerControlLimit = new double[dataSet.rangeSize()]; double[] pChartCorrection = new double[dataSet.rangeSize()]; sheet.Cells[row, column] = "Index"; sheet.Cells[row, column + 1] = "Sample Size"; sheet.Cells[row, column + 2] = "Proportion"; for (index = 1; index <= dataSet.rangeSize(); index++) { int temp = index + 1; String N = "(" + dataSet.getWorksheet().Name + "!$" + variableY[0].Range[1] + "$" + temp + ")"; String K = "(" + dataSet.getWorksheet().Name + "!$" + variableX[0].Range[1] + "$" + temp + ")"; row++; sheet.Cells[row, column] = index; sheet.Cells[row, column + 1] = "=(" + dataSet.getWorksheet().Name + "!$" + variableX[0].Range[1] + "$" + temp + ")"; if (radioButton2) { sheet.Cells[row, column + 2] = "=" + N + "/" + K; } else { sheet.Cells[row, column + 2] = "=(" + dataSet.getWorksheet().Name + "!$" + variableY[0].Range[1] + "$" + temp + ")"; } var cellValue = (double)(sheet.Cells[row, column + 2] as Range).Value; if (cellValue < -214682680) { cellValue = 0; // if cellValue is the result of a division by 0, set value to 0 } pValues[index - 1] = cellValue; if (cellValue > 1) { MessageBox.Show("Cannot generate P-Chart; Data in " + dataSet.Name + " contains samples greater than 1"); return; } cellValue = (double)(sheet.Cells[row, column + 1] as Range).Value; if (cellValue < -214682680) { cellValue = 0; // if cellValue is the result of a division by 0, set value to 0 } sample[index - 1] = cellValue; } for (index = startindex; index <= stopindex; index++) { pValuesInRange[index - startindex] = pValues[index]; } for (index = 0; index <= dataSet.rangeSize() - 1; index++) { pChartCorrection[index] = Math.Sqrt((pValuesInRange.Average() * (1 - pValuesInRange.Average())) / sample[index]); averageOfPValues[index] = pValuesInRange.Average(); pChartUpperControlLimit[index] = pValues.Average() + 3.0 * pChartCorrection[index]; pChartLowerControlLimit[index] = pValues.Average() - 3.0 * pChartCorrection[index]; } // new arrays for subsets of existing arrays but within limits double[] plotpValues = new double[plotstopindex - plotstartindex + 1]; double[] plotaverageOfPValues = new double[plotstopindex - plotstartindex + 1]; double[] plotpChartUpperControlLimit = new double[plotstopindex - plotstartindex + 1]; double[] plotpChartLowerControlLimit = new double[plotstopindex - plotstartindex + 1]; int[] ArrayIndex = new int[plotstopindex - plotstartindex + 1]; for (int i = plotstartindex; i <= plotstopindex; i++) { plotpValues[i - plotstartindex] = pValues[i]; plotaverageOfPValues[i - plotstartindex] = averageOfPValues[i]; plotpChartLowerControlLimit[i - plotstartindex] = pChartLowerControlLimit[i]; plotpChartUpperControlLimit[i - plotstartindex] = pChartUpperControlLimit[i]; ArrayIndex[i - plotstartindex] = i; } var Xcharts = (ChartObjects)sheet.ChartObjects(); var XchartObject = Xcharts.Add(340, 20, 550, 300); var Xchart = XchartObject.Chart; Xchart.ChartType = XlChartType.xlXYScatterLines; Xchart.ChartWizard(Title: "P-Chart " + dataSet.Name, HasLegend: true); var XseriesCollection = (SeriesCollection)Xchart.SeriesCollection(); var pseries = XseriesCollection.NewSeries(); var CLseries = XseriesCollection.NewSeries(); var UCLseries = XseriesCollection.NewSeries(); var LCLseries = XseriesCollection.NewSeries(); pseries.Name = ("Proportion"); CLseries.Name = ("Center line"); UCLseries.Name = ("UCL"); LCLseries.Name = ("LCL"); pseries.Values = plotpValues; CLseries.Values = plotaverageOfPValues; UCLseries.Values = plotpChartUpperControlLimit; LCLseries.Values = plotpChartLowerControlLimit; pseries.XValues = ArrayIndex; CLseries.XValues = ArrayIndex; UCLseries.XValues = ArrayIndex; LCLseries.XValues = ArrayIndex; }
public void createHypothese(List <Variable> variables, DataSet dataSet) { _Worksheet worksheet = WorksheetHelper.NewWorksheet("Hypothesis test"); if (variables.Count == 1) { foreach (Variable variable in variables) { worksheet.Cells[1, 1] = "Hypothesis test"; worksheet.Cells[1, 2] = variable.name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")"; worksheet.Cells[3, 1] = "Sample proportion"; string ran = variable.Range.ToString(); Array dist = dataSet.getWorksheet().Range[ran].Value; double count = 0; foreach (var item in dist) { if (Convert.ToInt32(item) == 0) { System.Diagnostics.Debug.WriteLine(item); count = count + 1; } } double n = (worksheet.Cells[2, 2] as Range).Value; double prop = count / n; worksheet.Cells[3, 2] = prop; if (model.equal) { PrintCategories(worksheet, 5, "equal", dataSet); } else if (model.greater) { PrintCategories(worksheet, 5, "greater", dataSet); } } } else if (variables.Count == 2) { worksheet.Cells[1, 1] = "Hypothesis test"; worksheet.Cells[1, 2] = variables[0].name; worksheet.Cells[1, 3] = variables[1].name; worksheet.Cells[2, 1] = "Sample size"; worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")"; worksheet.Cells[2, 3] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")"; worksheet.Cells[3, 1] = "Sample proportion"; string ran = variables[0].Range.ToString(); string ran1 = variables[1].Range.ToString(); Array dist = dataSet.getWorksheet().Range[ran].Value; Array dist1 = dataSet.getWorksheet().Range[ran1].Value; double count = 0; double count1 = 0; foreach (var item in dist) { if (Convert.ToInt32(item) == 0) { System.Diagnostics.Debug.WriteLine(item); count = count + 1; } } foreach (var item in dist1) { if (Convert.ToInt32(item) == 0) { System.Diagnostics.Debug.WriteLine(item); count1 = count1 + 1; } } double n = (worksheet.Cells[2, 2] as Range).Value; double n1 = (worksheet.Cells[2, 3] as Range).Value; double prop = count / n; double prop1 = count1 / n1; worksheet.Cells[3, 2] = prop; worksheet.Cells[3, 3] = prop1; if (model.equal) { PrintCategories2(worksheet, 5, "equal", dataSet); } else if (model.greater) { PrintCategories2(worksheet, 5, "greater", dataSet); } } }
public void generateProcessCapability(List <Variable> variables, double LSL, double USL, DataSet dataSet, _Worksheet sheet) { int index = 0; int row = 1; int column = 1; double correctionFactor1, correctionFactor2; double[] averages = new double[dataSet.amountOfVariables()]; double[] Rvalues = new double[dataSet.amountOfVariables()]; double[] averageOfAverages = new double[dataSet.amountOfVariables()]; double[] xChartUpperControlLimit = new double[dataSet.amountOfVariables()]; double[] xChartLowerControlLimit = new double[dataSet.amountOfVariables()]; double[] rChartUpperControlLimit = new double[dataSet.amountOfVariables()]; double[] rChartLowerControlLimit = new double[dataSet.amountOfVariables()]; double[] averageOfRvalues = new double[dataSet.amountOfVariables()]; int[] ArrayIndex = new int[dataSet.amountOfVariables()]; double[] constantC4 = new double[25] { 0.0, 0.7979, 0.8862, 0.9213, 0.9400, 0.9515, 0.9594, 0.9650, 0.9693, 0.9727, 0.9754, 0.9776, 0.9794, 0.9810, 0.9823, 0.9835, 0.9845, 0.9854, 0.9862, 0.9869, 0.9876, 0.9882, 0.9887, 0.9892, 0.9896 }; double[] constantD2 = new double[25] { 0.0, 1.128, 1.693, 2.059, 2.326, 2.534, 2.704, 2.847, 2.970, 3.078, 3.173, 3.258, 3.336, 3.407, 3.472, 3.532, 3.588, 3.640, 3.689, 3.735, 3.778, 3.819, 3.858, 3.895, 3.931 }; sheet.Cells[row, column] = "Index"; sheet.Cells[row, column + 1] = "Observation"; sheet.Cells[row, column + 2] = "Average"; sheet.Cells[row, column + 3] = "STD DEV"; sheet.Cells[row, column + 4] = "Max"; sheet.Cells[row, column + 5] = "Min"; sheet.Cells[row, column + 6] = "R"; sheet.Cells[100, 100] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")"; int length = Convert.ToInt32((sheet.Cells[100, 100] as Range).Value); sheet.Cells[100, 100] = ""; double temp = 0; List <double> allVals = new List <double>(); double[] all = new double[length * variables.Count]; foreach (Variable var in variables) { string ran = var.Range.ToString(); Array arr = dataSet.getWorksheet().Range[ran].Value; double[] vals = new double[length]; int count = 0; foreach (var item in arr) { temp = Convert.ToDouble(item); vals[count] = temp; count++; } allVals.AddRange(vals); } all = allVals.ToArray(); double totMean = 0; double totStd = 0; for (index = 0; index < variables.Count; index++) { row++; sheet.Cells[row, column] = index; sheet.Cells[row, column + 1] = variables[index].name; sheet.Cells[row, column + 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")"; sheet.Cells[row, column + 3] = "=STDEV(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")"; sheet.Cells[row, column + 4] = "=MAX(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")"; sheet.Cells[row, column + 5] = "=MIN(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")"; sheet.Cells[row, column + 6] = (double)(sheet.Cells[row, column + 4] as Range).Value - (double)(sheet.Cells[row, column + 5] as Range).Value; totMean = totMean + Convert.ToDouble((sheet.Cells[row, column + 2] as Range).Value); totStd = totStd + (Convert.ToDouble((sheet.Cells[row, column + 3] as Range).Value)); ArrayIndex[index] = index; var cellValue = (double)(sheet.Cells[row, column + 2] as Range).Value; if (cellValue < -214682680) { cellValue = 0; // if cellValue is the result of a division by 0, set value to 0 } averages[index] = cellValue; cellValue = (double)(sheet.Cells[row, column + 6] as Range).Value; Rvalues[index] = cellValue; } totMean = all.Average(); totStd = sheet.Application.WorksheetFunction.StDev(all); //totMean = totMean / variables.Count; //totStd = totStd / variables.Count; if (dataSet.getVariableNamesInFirstRowOrColumn()) { correctionFactor1 = constantC4[variables.Count - 1]; correctionFactor2 = constantD2[variables.Count - 1]; } else { correctionFactor1 = constantC4[variables.Count]; } correctionFactor2 = constantD2[variables.Count]; System.Diagnostics.Debug.WriteLine(totStd); double sigma = totStd; double Cp = (USL - LSL) / (6 * sigma); double Cpk = Math.Min((USL - totMean) / (3 * sigma), (totMean - LSL) / (3 * sigma)); double Pbelow = sheet.Application.WorksheetFunction.NormDist(LSL, totMean, totStd, true); double Pabove = 1.0 - sheet.Application.WorksheetFunction.NormDist(USL, totMean, totStd, true); row = 1; column = 1; sheet.Cells[row, column + 8] = "LSL"; sheet.Cells[row, column + 9] = "USL"; sheet.Cells[row, column + 10] = "Cp"; sheet.Cells[row, column + 11] = "Cpk"; sheet.Cells[row, column + 12] = "P(below LSL)"; sheet.Cells[row, column + 13] = " Per million"; sheet.Cells[row, column + 14] = "P(above USL)"; sheet.Cells[row, column + 15] = " Per million"; row++; sheet.Cells[row, column + 8] = LSL; sheet.Cells[row, column + 9] = USL; sheet.Cells[row, column + 10] = Cp; sheet.Cells[row, column + 11] = Cpk; sheet.Cells[row, column + 12] = Pbelow; sheet.Cells[row, column + 13] = Pbelow * 1000000; sheet.Cells[row, column + 14] = Pabove; sheet.Cells[row, column + 15] = Pabove * 1000000; }
public void createRegression(List <Variable> variablesD, List <Variable> variablesI, DataSet dataSet, DataSet dataSet2, bool[] graphs, List <Variable> variablesPrediction) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Regression"); double confLevel = model.confidenceLevel; sheet.Cells[100, 100] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variablesI[0].Range + ")"; int length = Convert.ToInt32((sheet.Cells[100, 100] as Range).Value); sheet.Cells[100, 100] = ""; int row = 1; sheet.Cells[1, 1] = "Regression Summary"; sheet.Cells[2, 1] = "R"; sheet.Cells[3, 1] = "R-square"; sheet.Cells[4, 1] = "adjusted R-square"; sheet.Cells[5, 1] = "stErr of Estimate"; sheet.Cells[9, 1] = "ANOVA Table"; sheet.Cells[10, 1] = "Explained"; sheet.Cells[11, 1] = "Unexplained"; sheet.Cells[8, 3] = "Sum of"; sheet.Cells[9, 3] = "Squares"; sheet.Cells[8, 2] = "Degrees of"; sheet.Cells[9, 2] = "Freedom"; sheet.Cells[8, 4] = "Mean"; sheet.Cells[9, 4] = "Squares"; sheet.Cells[9, 5] = "F-Ratio"; sheet.Cells[9, 6] = "p-Value"; sheet.Cells[15, 1] = "Regression Table"; sheet.Cells[16, 1] = "Constant"; row = 17; foreach (Variable var in variablesI) { sheet.Cells[row, 1] = var.name; row++; } lowestDataRow = row; sheet.Cells[15, 2] = "Coefficient"; sheet.Cells[14, 3] = "Standard"; sheet.Cells[15, 3] = "Error"; sheet.Cells[15, 4] = "t-value"; sheet.Cells[15, 5] = "p-value"; sheet.Cells[14, 6] = "Confidence Interval " + confLevel.ToString() + "%"; sheet.Range[sheet.Cells[14, 6], sheet.Cells[14, 7]].Merge(); sheet.Cells[15, 6] = "Lower"; sheet.Cells[15, 7] = "Upper"; sheet.get_Range("B1", "J200").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.get_Range("A1", "B1").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.get_Range("A9", "F9").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.get_Range("A15", "G15").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.get_Range("B3", "B5").NumberFormat = "0.0000"; sheet.get_Range("B18", "B20").NumberFormat = "0.0000"; sheet.get_Range("D18", "E19").NumberFormat = "0.0000"; Globals.ExcelAddIn.Application.ActiveWindow.DisplayGridlines = false; // //calculate values of regression // double[] b = new double[variablesI.Count]; double[] yData = calcYdata(variablesD, dataSet, length); double[,] xData = calcXdata(length, dataSet, variablesI); b = calculateCoefB(yData, xData, b); double a = b[0]; var X = DenseMatrix.OfArray(xData); var B = new DenseVector(b); var Y = new DenseVector(yData); var yh = X.Multiply(B); var er = Y.Subtract(yh); double[] error = er.ToArray(); double[] yhat = yh.ToArray(); double R2 = sheet.Application.WorksheetFunction.Correl(yData, yhat); double R = Math.Pow(R2, 2); double Radj = 1.0 - ((1 - R) * (length - 1) / (length - variablesI.Count - 1)); double err = 0; int i = 0; while (i < error.Length) { err = err + Math.Pow(error[i], 2); i++; } err = Math.Sqrt(err / (length - 4)); double[] anovaResults = calculateAnova(yData, yhat, variablesI.Count(), sheet); multicollinearity(sheet, xData, variablesI.Count); if (model.doDurbinWatson == true) { DurbinWatson(sheet, error); } // //Calculate regressionTable // double MSE = anovaResults[6]; var Xt = X.Transpose(); var tempM = (Xt.Multiply(X)).Inverse(); var variancesM = tempM.Multiply(MSE); double[] variances = variancesM.Diagonal().ToArray(); double[] std = new double[variances.Length]; int index = 0; foreach (double elem in variances) { std[index] = Math.Sqrt(elem); //System.Diagnostics.Debug.WriteLine(std[index]); index++; } double[] tValue = new double[b.Length]; double[] pValue = new double[b.Length]; double[] lowerInt = new double[b.Length]; double[] higherInt = new double[b.Length]; for (index = 0; index < b.Length; index++) { tValue[index] = b[index] / std[index]; pValue[index] = sheet.Application.WorksheetFunction.TDist(Math.Abs(tValue[index]), length - variablesI.Count - 1, 2); lowerInt[index] = b[index] - sheet.Application.WorksheetFunction.TInv(1 - confLevel / 100, length - variablesI.Count - 1) * std[index]; higherInt[index] = b[index] + sheet.Application.WorksheetFunction.TInv(1 - confLevel / 100, length - variablesI.Count - 1) * std[index]; //System.Diagnostics.Debug.WriteLine("{0}, p={1}, lower = {2}, n={3}, inv={4}, std={5}, inv2={6}",real_p, pValue[index], lowerInt[index], length - 1, sheet.Application.WorksheetFunction.TInv(1-0.975, length - 1), std[index], sheet.Application.WorksheetFunction.TInv(1 - 0.95, length - 1)); } // //Prediction // if (model.doPrediction) { double[,] xDataPred = calcXdata(dataSet2.getNrDataRows(), dataSet2, variablesPrediction); int[] rightCell = { dataSet2.getRange().Row, dataSet2.getRange().Column + dataSet2.amountOfVariables() }; //{row, column} prediction(dataSet2.getWorksheet(), xDataPred, b, length, variablesI.Count, X, MSE, rightCell, variablesD[0].name); setPrediction(false); } // //Draw graphs // if (graphs[0]) { drawGraphs(sheet, yData, yhat, "Scatter plot of fitted values vs. actual values"); } if (graphs[1]) { drawGraphs(sheet, yData, error, "Scatter plot of residuals vs. fitted values"); } if (graphs[2]) { int varNumber = 1; foreach (Variable var in variablesI) { double[] xDataTemp = new double[xData.GetLength(0)]; for (int x = 0; x < xData.GetLength(0); x++) { xDataTemp[x] = xData[x, varNumber]; } drawGraphs(sheet, xDataTemp, error, "Scatter plot of residuals vs. " + var.name); varNumber++; } } if (graphs[3]) { int varNumber = 1; foreach (Variable var in variablesI) { double[] xDataTemp = new double[xData.GetLength(0)]; for (int x = 0; x < xData.GetLength(0); x++) { xDataTemp[x] = xData[x, varNumber]; } drawGraphs(sheet, xDataTemp, yData, "Scatter plot of actual Y values vs. " + var.name); varNumber++; } } if (graphs[4]) { int varNumber = 1; foreach (Variable var in variablesI) { double[] xDataTemp = new double[xData.GetLength(0)]; for (int x = 0; x < xData.GetLength(0); x++) { xDataTemp[x] = xData[x, varNumber]; } drawGraphs(sheet, xDataTemp, yhat, "Scatter plot of fitted Y values vs. " + var.name); varNumber++; } } // //print results to excel sheet // sheet.Cells[16, 2] = a; row = 17; foreach (Variable var in variablesI) { sheet.Cells[row, 2] = b[row - 16]; row++; } FillR(sheet, R2, R, Radj, err); FillAnova(sheet, anovaResults); FillRegressionTable(sheet, std, tValue, pValue, lowerInt, higherInt); ((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 6]).EntireColumn.ColumnWidth = 13; ((Range)sheet.Cells[1, 7]).EntireColumn.ColumnWidth = 13; ((Range)sheet.Cells[1, 8]).EntireColumn.ColumnWidth = 13; ((Range)sheet.Cells[1, 9]).EntireColumn.ColumnWidth = 13; ((Range)sheet.Cells[1, 10]).EntireColumn.AutoFit(); }
//public void createRegression(List<Variable> variables) public void createDiscriminant(List <Variable> variablesD, List <Variable> variablesI, DataSet dataSet) { _Worksheet sheet = WorksheetHelper.NewWorksheet("Discriminant"); sheet.Cells[100, 100] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variablesI[0].Range + ")"; int length = Convert.ToInt32((sheet.Cells[100, 100] as Range).Value); sheet.Cells[100, 100] = ""; int row = 1; int column = 1; sheet.Cells[2, 1] = "Sample Summary"; sheet.Cells[3, 1] = "No"; sheet.Cells[4, 1] = "Yes"; sheet.Cells[1, 2] = "Sample"; sheet.Cells[2, 2] = "Size"; int i = 0; while (i < variablesI.Count) { sheet.Cells[1, 3 + i] = "Mean"; sheet.Cells[2, 3 + i] = variablesI[i].name; sheet.Cells[8 + i, 1] = variablesI[i].name; i++; } row = 8 + i - 1 + 3; column = 3 + i - 1; string let = ColumnIndexToColumnLetter(3 + i - 1); List <double[]> final = fillTable1(variablesD, variablesI, dataSet, 3, sheet); double[] coef = calcCoef(final, variablesI.Count, sheet); sheet.get_Range("A2", "J200").Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; sheet.get_Range("A2", let + "2").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Cells[7, 1] = "Discriminant Function"; sheet.Cells[7, 2] = "Coefficient"; sheet.get_Range("A7", "B7").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Cells[row, 1] = "Classification Matrix"; sheet.Cells[row, 2] = "No"; sheet.Cells[row, 3] = "Yes"; sheet.Cells[row, 4] = "Correct"; double cut = cutoff(coef, final, variablesI.Count, sheet); string num = Convert.ToString(row); row++; sheet.Cells[row++, 1] = "No"; sheet.Cells[row, 1] = "Yes"; classification(cut, coef, final, variablesI.Count, sheet, row); sheet.get_Range("A" + num, "D" + num).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; row = row + 3; num = Convert.ToString(row); sheet.Cells[row++, 1] = "Summary Classification"; sheet.Cells[row++, 1] = "Correct"; sheet.Cells[row++, 1] = "Base"; sheet.Cells[row, 1] = "Improvement"; sheet.get_Range("A" + num, "B" + num).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; //sheet.get_Range("B3", "B5").NumberFormat = "0.0000"; //sheet.get_Range("B11", AddressConverter.CellAddress(14, variables.Count + 1, false, false)).NumberFormat = "0.000"; sheet.get_Range("B18", "B20").NumberFormat = "0.0000"; sheet.get_Range("D18", "E19").NumberFormat = "0.0000"; row = row + 3; num = Convert.ToString(row); sheet.Cells[row++, 1] = "Cutoff Score"; sheet.get_Range("A" + num, "A" + num).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble; sheet.Cells[row, 1] = cut; //sheet.get_Range("B24", AddressConverter.CellAddress(23 + c, 2, false, false)).NumberFormat = "0.0000"; //sheet.get_Range("C24", AddressConverter.CellAddress(23 + c, 8, false, false)).NumberFormat = "0.000000"; //sheet.get_Range("C24", AddressConverter.CellAddress(23 + c, 8, false, false)).Cells.HorizontalAlignment = XlHAlign.xlHAlignRight; Globals.ExcelAddIn.Application.ActiveWindow.DisplayGridlines = false; ((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit(); ((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit(); }
private void PrintVariables(_Worksheet _sheet, int _row, int _counter, Variable variable, DataSet dataSet) { var function = Globals.ExcelAddIn.Application.WorksheetFunction; int numberOfBins = model.useBins && model.bins > 0 ? model.bins : (int)function.RoundUp(Math.Sqrt(function.Count(variable.getRange())), 0); _sheet.Cells[100, 100] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")"; int length = Convert.ToInt32((_sheet.Cells[100, 100] as Range).Value); _sheet.Cells[100, 100] = ""; int count = 0; int i = 0; int j = 0; double temp; double[] yData = new double[length]; string ran = variable.Range.ToString(); Array arr = dataSet.getWorksheet().Range[ran].Value; double[] vals = new double[length]; foreach (var item in arr) { temp = Convert.ToDouble(item); yData[count] = temp; count++; } List <double> Y = new List <double>(yData); Y.Sort(); yData = Y.ToArray(); double start = yData[0]; double end = yData[length - 1]; double step = (end - start) / numberOfBins; List <List <double> > finalList = new List <List <double> >(); i = 0; double startVal = start; double endVal = startVal + step; while (i < numberOfBins) { List <double> tempList = calcVal(finalList, yData, startVal, endVal); finalList.Add(tempList); startVal = endVal; endVal = endVal + step; i++; } startVal = start; endVal = startVal + step; for (int bin = 0; bin < numberOfBins; bin++) { var column = 1; var range = variable.getRange().Address(true, true, true); _sheet.Cells[_row, column++] = "Bin #" + bin; _sheet.Cells[_row, column++] = startVal; _sheet.Cells[_row, column++] = endVal; _sheet.Cells[_row, column++] = startVal + (endVal - startVal) / 2.0; //_sheet.WriteFunction(_row, column, bin == 0 ? "MIN(" + range + ")" : AddressConverter.CellAddress(_row - 1, column + 1)); column++; //_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "+" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)"); column++; //_sheet.Cells[_row, column] = "=(" + AddressConverter.CellAddress(_row, column - 2) + "+" + AddressConverter.CellAddress(_row, column - 1) + ")/2"; column++; _sheet.WriteFunction(_row, column, "COUNTIF(" + range + ",\"<=\"&" + AddressConverter.CellAddress(_row, column - 2) + ")-COUNTIF(" + range + ",\"<\"&" + AddressConverter.CellAddress(_row, column - 3) + ")"); column++; _sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "COUNT(" + range + ")"); double mean = (_sheet.Cells[_row, column] as Range).Value; column++; _sheet.Cells[_row, column] = mean / (endVal - startVal); startVal = endVal; endVal = endVal + step; //_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)"); _row++; } // Create the chart. var charts = (ChartObjects)_sheet.ChartObjects(); var chartObject = charts.Add(400, 225 * _counter, 100 * numberOfBins, 200); var chart = chartObject.Chart; chart.ChartType = XlChartType.xlColumnClustered; chart.ChartWizard(Title: "Histogram - " + variable.name, HasLegend: false); var seriesCollection = (SeriesCollection)chart.SeriesCollection(); var series = seriesCollection.Add(_sheet.Range[_sheet.Cells[_row - numberOfBins, 5], _sheet.Cells[_row - 1, 5]]); series.ChartType = XlChartType.xlColumnClustered; series.XValues = _sheet.Range[_sheet.Cells[_row - numberOfBins, 4], _sheet.Cells[_row - 1, 4]]; }