private void addDataSetOfCurrentSelection()
{
_Worksheet workSheet = Globals.ExcelAddIn.getActiveWorksheet();
Range range = Globals.ExcelAddIn.getCurrentSelectionRange();
if (model.hasIntersectionWith(range))
{
if (DataSetManagerForm.ignoreIntersection(model.getFirstIntersectingDataSetWith(range)))
{
return;
}
}
if (range.Rows.Count == 1 && range.Columns.Count == 1)
{
range = Globals.ExcelAddIn.getExpandedCurrentRange();
range.Select();
}
bool addNewDataSet = (range.Rows.Count != 1 || range.Columns.Count != 1) && DataSetManagerForm.addNewDataSet(range);
if (!addNewDataSet)
{
return;
}
DataSet newDataSet = DataSetFactory.create(workSheet, range, "Data Set " + (model.numberOfDataSets() + 1), COLUMNS, true);
model.addDataSet(newDataSet);
view.selectDataSet(newDataSet);
}
public void addNewDataSet()
{
DataSet newDataSet = DataSetFactory.create(Globals.ExcelAddIn.getActiveWorksheet(), null, "Data Set " + (model.numberOfDataSets() + 1), COLUMNS, true);
model.addDataSet(newDataSet);
view.selectDataSet(newDataSet);
}
private void PrintCategories2(_Worksheet _sheet, int rowIn, DataSet dataSet)
{
double alpha = (100.0 - model.confidence) / 200.0;
double norm = _sheet.Application.WorksheetFunction.NormSInv(alpha);
var pValue = (_sheet.Cells[3, 2] as Range).Value;
var pValue2 = (_sheet.Cells[3, 3] as Range).Value;
double p = 1.0 - Convert.ToDouble(pValue);
double p2 = 1.0 - Convert.ToDouble(pValue2);
double n = Convert.ToDouble((_sheet.Cells[2, 2] as Range).Value);
double n2 = Convert.ToDouble((_sheet.Cells[2, 3] as Range).Value);
double sqrt = Math.Sqrt((p * (1.0 - p) / n) + (p2 * (1.0 - p2) / n2));
var row = rowIn;
_sheet.Cells[row, 1] = "Confidence level";
_sheet.Cells[row++, 2] = model.confidence.ToString();
_sheet.Cells[row, 1] = "Lower limit";
double tmp = p - p2 - Math.Abs((norm * sqrt));
_sheet.Cells[row++, 2] = tmp;
_sheet.Cells[row, 1] = "Upper limit";
tmp = p - p2 + Math.Abs((norm * sqrt));
_sheet.Cells[row++, 2] = tmp;
}
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 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);
}
}
private void PrintCategories(_Worksheet _sheet, int rowIn, String input, DataSet dataSet)
{
double alpha = (double)model.alpha;
int tail = 1;
if (input.Equals("equal"))
{
alpha = alpha / 200.0;
tail = 2;
}
else
{
alpha = alpha / 100.0;
tail = 1;
}
double mean = (_sheet.Cells[3, 2] as Range).Value;
double s = (_sheet.Cells[4, 2] as Range).Value;
double n = (_sheet.Cells[2, 2] as Range).Value;
double den = (s / Math.Sqrt(n));
double mu = Convert.ToDouble(model.Null);
double nom = mean - mu;
double t = nom / den;
var row = rowIn;
_sheet.Cells[row, 1] = "Hypothesized mean";
_sheet.Cells[row++, 2] = model.Null;
_sheet.Cells[row, 1] = "Alternative Hypothesis";
if (input.Equals("equal"))
{
_sheet.Cells[row++, 2] = "'=/= " + model.Null;
}
else
{
_sheet.Cells[row++, 2] = "> " + model.Null;
}
_sheet.Cells[row, 1] = "Alpha";
_sheet.Cells[row++, 2] = (double)model.alpha / 100.0;
_sheet.Cells[row, 1] = "t-Test Statistic";
_sheet.Cells[row++, 2] = t;
double p = _sheet.Application.WorksheetFunction.TDist(Math.Abs(t), n - 1, tail);
_sheet.Cells[row, 1] = "p-Value";
_sheet.Cells[row++, 2] = p;
_sheet.Cells[row, 1] = "Null Hypothesis";
if (p <= alpha)
{
_sheet.Cells[row, 2] = "Reject";
}
else
{
_sheet.Cells[row, 2] = "Accept";
}
}
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 bool checkInput(DataSet dataSet)
{
if (dataSet != null)
{
createUnstacked(dataSet);
return(true);
}
else
{
MessageBox.Show("Please correct all fields to generate interaction", "Interaction error");
}
return(false);
}
public bool checkInput(DataSet dataSet, List <Variable> variables, bool rdbMovingAverage, bool rdbSimpleExponentialSmoothing, bool rdbHoltsExponentialSmoothing, bool rdbWintersExponentialSmoothing, bool doOptimizeParameters, int numberOfForecasts, int numberOfHoldouts, int seasonalPeriod, int span, string level, string trend, string seasonality)
{
if (variables.Count != 0 && double.TryParse(seasonality, out _seasonality) && double.TryParse(trend, out _trend) && double.TryParse(level, out _level))
{
Print(dataSet, variables, rdbMovingAverage, rdbSimpleExponentialSmoothing, rdbHoltsExponentialSmoothing, rdbWintersExponentialSmoothing, doOptimizeParameters, numberOfForecasts, numberOfHoldouts, seasonalPeriod, span, level, trend, seasonality);
return(true);
}
else
{
MessageBox.Show("Please correct all fields to generate Forecast");
return(false);
}
}
public bool checkInput(DataSet dataSet)
{
if (dataSet != null)
{
createUnstacked(dataSet);
return(true);
}
else
{
MessageBox.Show("Please correct all fields to calculate unstacked", "Unstacked error");
}
return(false);
}
public bool checkInput(List <Variable> variablesX, DataSet dataSet)
{
if (dataSet != null)
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("Time Series Graph");
generateChart(variablesX, sheet, dataSet);
return(true);
}
else
{
MessageBox.Show("Please correct all fields to generate Time Series Graph", "Time Series Graph error");
}
return(false);
}
private double[] CalculateSumOfSquares(DataSet dataSet, List <Variable> valueArrayX, Variable valueY, Matrix <double> beta)
{
var sumOfSquare = new double[2];
for (var i = 0; i < dataSet.rangeSize(); i++)
{
var fittedValue = beta[0, 0];
for (var j = 0; j < valueArrayX.Count; j++)
{
fittedValue = fittedValue + beta[j + 1, 0] * dataSet.getValuesArray(valueArrayX[j])[i];
}
sumOfSquare[0] = sumOfSquare[0] + (fittedValue - _functions.Average(valueY.getRange())) * (fittedValue - _functions.Average(valueY.getRange()));
sumOfSquare[1] = sumOfSquare[1] + (dataSet.getValuesArray(valueY)[i] - fittedValue) * (dataSet.getValuesArray(valueY)[i] - fittedValue);
}
return(sumOfSquare);
}
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 bool checkInput(bool rdbAllObservations, bool rdbObservationsInRange, DataSet dataSet, string uiTextBox_StopIndex, string uiTextBox_StartIndex, bool rdbPlotAllObservations, bool rdbPlotObservationsInRange, string uiTextBox_PlotStopIndex, string uiTextBox_PlotStartIndex)
{
int startindex = Convert.ToInt16(uiTextBox_StartIndex);
int stopindex = Convert.ToInt16(uiTextBox_StopIndex);
int plotstartindex = Convert.ToInt16(uiTextBox_PlotStartIndex);
int plotstopindex = Convert.ToInt16(uiTextBox_PlotStopIndex);
if ((rdbAllObservations || rdbObservationsInRange) && (rdbPlotAllObservations || rdbPlotObservationsInRange) && (dataSet != null) && (startindex <= stopindex) && (startindex >= 0 && stopindex >= 0) && (plotstartindex <= plotstopindex) && (plotstartindex >= 0 && plotstopindex >= 0))
{
offset = 320;
if (rdbAllObservations)
{
startindex = 0;
stopindex = dataSet.amountOfVariables() - 2;
}
if (rdbObservationsInRange && stopindex >= dataSet.amountOfVariables())
{
stopindex = dataSet.amountOfVariables() - 2;
}
if (rdbPlotAllObservations)
{
plotstartindex = 0;
plotstopindex = dataSet.amountOfVariables() - 2;
}
if (rdbPlotObservationsInRange && plotstopindex >= dataSet.amountOfVariables())
{
plotstopindex = dataSet.amountOfVariables() - 2;
}
_Worksheet sheet = WorksheetHelper.NewWorksheet("XR Chart");
generateXRChart(startindex, stopindex, plotstartindex, plotstopindex, dataSet, offset, sheet);
return(true);
}
else
{
MessageBox.Show("Please correct all fields to generate X/R-Chart", "XR-Chart error");
}
return(false);
}
public void createHistograms(List <Variable> variables, DataSet dataSet)
{
_Worksheet worksheet = WorksheetHelper.NewWorksheet("Histogram");
int row = 1;
int counter = 0;
// Create the Histogram.
foreach (Variable variable in variables)
{
PrintCategories(worksheet, row++, 1);
PrintVariables(worksheet, row++, counter++, variable, dataSet);
if (row < 15 * counter)
{
row = 15 * counter;
}
}
}
private double CalculateStdErrorEstimation(DataSet dataSet, List <Variable> valueArrayX, Variable valueY, Matrix <double> beta)
{
double n = dataSet.rangeSize();
double ssRes = 0;
for (var i = 0; i < n; i++)
{
var fittedValue = beta[0, 0];
for (var j = 0; j < valueArrayX.Count; j++)
{
fittedValue = fittedValue + beta[j + 1, 0] * dataSet.getValuesArray(valueArrayX[j])[i];
}
ssRes = ssRes + (dataSet.getValuesArray(valueY)[i] - fittedValue) * (dataSet.getValuesArray(valueY)[i] - fittedValue);
}
var stdErrorEstimation = Math.Sqrt(ssRes / (n - 1 - valueArrayX.Count));
return(stdErrorEstimation);
}
private double CalculateRsquare(DataSet dataSet, List <Variable> valueArrayX, Variable valueY, Matrix <double> beta)
{
double ssTot = 0;
double ssRes = 0;
for (var i = 0; i < dataSet.rangeSize(); i++)
{
ssTot = ssTot + (dataSet.getValuesArray(valueY)[i] - _functions.Average(valueY.getRange())) * (dataSet.getValuesArray(valueY)[i] - _functions.Average(valueY.getRange()));
var fittedValue = beta[0, 0];
for (var j = 0; j < valueArrayX.Count; j++)
{
fittedValue = fittedValue + beta[j + 1, 0] * dataSet.getValuesArray(valueArrayX[j])[i];
}
ssRes = ssRes + (dataSet.getValuesArray(valueY)[i] - fittedValue) * (dataSet.getValuesArray(valueY)[i] - fittedValue);
}
var rSquare = 1 - ssRes / ssTot;
return(rSquare);
}
private void PrintCategories(_Worksheet _sheet, int rowIn, String input, DataSet dataSet)
{
double alpha = (100.0 - model.mean) / 200.0;
double norm = _sheet.Application.WorksheetFunction.NormSInv(alpha);
var stdValue = (_sheet.Cells[4, 2] as Range).Value;
double std = Convert.ToDouble(stdValue);
double sqrt = Math.Sqrt(Convert.ToDouble((_sheet.Cells[2, 2] as Range).Value));
double avr = Convert.ToDouble((_sheet.Cells[3, 2] as Range).Value);
var row = rowIn;
_sheet.Cells[row, 1] = "Confidence level (" + input + ")";
if (input.Equals("mean"))
{
_sheet.Cells[row++, 2] = model.mean.ToString();
}
else
{
_sheet.Cells[row++, 2] = model.std.ToString();
}
_sheet.Cells[row, 1] = "Lower limit";
if (input.Equals("mean"))
{
double tmp = avr - Math.Abs((norm * std / sqrt));
_sheet.Cells[row++, 2] = tmp;
}
else
{
double tmp = std - Math.Abs((norm * std / sqrt));
_sheet.Cells[row++, 2] = tmp;
}
_sheet.Cells[row, 1] = "Upper limit";
if (input.Equals("mean"))
{
double tmp = avr + Math.Abs((norm * std / sqrt));
_sheet.Cells[row++, 2] = tmp;
}
else
{
double tmp = std + Math.Abs((norm * std / sqrt));
_sheet.Cells[row++, 2] = tmp;
}
}
public bool checkInput(List <Variable> variables, DataSet dataSet, string uiTextBox_LowerLimit, string uiTextBox_UpperLimit)
{
double LSL = Convert.ToDouble(uiTextBox_LowerLimit);
double USL = Convert.ToDouble(uiTextBox_UpperLimit);
if ((dataSet != null) && (LSL < USL))
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("Process Capability");
generateProcessCapability(variables, LSL, USL, dataSet, sheet);
return(true);
}
else
{
MessageBox.Show("Please correct all fields to generate X/R-Chart, LSL should be smaller than USL", "XR-Chart error");
}
return(false);
}
public bool checkInput(DataSet dataSet, string uiTextBox_LowerLimit, string uiTextBox_UpperLimit)
{
int LSL = Convert.ToInt16(uiTextBox_LowerLimit);
int USL = Convert.ToInt16(uiTextBox_UpperLimit);
if ((dataSet != null) && (LSL < USL))
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("Process Capability");
generateProcessCapability(LSL, USL, dataSet, sheet);
return(true);
}
else
{
MessageBox.Show("Please correct all fields to generate X/R-Chart", "XR-Chart error");
}
return(false);
}
private int calculateRuns(_Worksheet sheet, DataSet dataSet, Range range, double cutoff)
{
int runs = 1;
double[,] array = RangeHelper.To2DDoubleArray(range);
double[] array2 = new double[array.Length];
for (int i = 0; i < array.Length; i++)
{
array2[i] = array[i, 0];
}
for (int i = 0; i < array.Length - 1; i++)
{
if (Math.Sign(array2[i] - cutoff) != Math.Sign(array2[i + 1] - cutoff))
{
runs++;
}
}
return(runs);
}
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 generateChart(List <Variable> variablesX, _Worksheet sheet, DataSet dataSet)
{
int offsetY = 0;
foreach (Variable variableX in variablesX)
{
int offsetX = 0;
var charts = (ChartObjects)sheet.ChartObjects();
var chartObject = charts.Add(offsetX * 450, offsetY * 250, 450, 250);
var chart = chartObject.Chart;
chart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
chart.ChartWizard(Title: "Time Series " + dataSet.getName() + " - " + variableX.name, HasLegend: false);
var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.Add();
series.Values = variableX.getRange();
series.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle;
offsetX++;
offsetY++;
}
}
public void deleteDataSet(DataSet dataSet)
{
model.removeDataSet(dataSet);
}
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 bool checkInput(DataSet dataSet, List <Variable> independentVariables, Variable dependentVariable)
{
if (dataSet == null || independentVariables.Count() == 0)
{
MessageBox.Show(" Please correct all fields to perform logistic regression. Make sure that only one independent variable is selected.");
return(false);
}
// declare function to use function of excel
var functions = Globals.ExcelAddIn.Application.WorksheetFunction;
var sheet = WorksheetHelper.NewWorksheet("Logistic Regression");
var matrixX = Matrix <double> .Build.Dense(dataSet.rangeSize(), independentVariables.Count + 1);
for (var i = 0; i < dataSet.rangeSize(); i++)
{
matrixX[i, 0] = 1;
for (var j = 0; j < independentVariables.Count; j++)
{
matrixX[i, j + 1] = dataSet.getValuesArray(independentVariables[j])[i];
}
}
var matrixY = Matrix <double> .Build.Dense(dataSet.rangeSize(), 1);
for (var i = 0; i < dataSet.rangeSize(); i++)
{
matrixY[i, 0] = dataSet.getValuesArray(dependentVariable)[i];
}
// calculate theta iteratively using newton's method, starting from zero matrix
var startTheta = Matrix <double> .Build.Dense(independentVariables.Count + 1, 1);
var theta = NewtonsMethod(matrixX, matrixY, startTheta);
// calculate classification matrix
var classificationMatrix = CalculateClassificationMatrix(matrixX, matrixY, theta);
// calculate summary classification
var summaryClassificationPercentages = SummaryClassification(classificationMatrix);
var expThetaX = theta.Transpose() * matrixX.Transpose();
var matrixV = Matrix <double> .Build.Dense(dataSet.rangeSize(), dataSet.rangeSize());
for (var i = 0; i < dataSet.rangeSize(); i++)
{
var prob = 1 / (1 + Math.Exp(-expThetaX[0, i]));
matrixV[i, i] = prob * (1 - prob);
}
var matrixCovar = (matrixX.Transpose() * matrixV * matrixX).Inverse();
// locations of tables
const int title = 1;
const int summaryName = 3;
const int name = summaryName + 6;
const int betaCoeffName = 2;
const int stdErrorName = betaCoeffName + 1;
const int waldValueName = stdErrorName + 1;
const int pValueName = waldValueName + 1;
const int lowerLimitName = pValueName + 1;
const int upperLimitName = lowerLimitName + 1;
var classificationMatrixName = name + 3 + independentVariables.Count;
var summaryClassificationName = classificationMatrixName + 4;
var dataName = summaryClassificationName + 5;
var probName = independentVariables.Count + 2;
// names of variables on sheet
sheet.Cells[title, 1] = "Logistic Regression";
sheet.Cells[summaryName, 1] = "Summary";
sheet.Cells[summaryName + 1, 1] = "Null Deviance";
sheet.Cells[summaryName + 2, 1] = "Model Deviance";
sheet.Cells[summaryName + 3, 1] = "Improvement";
sheet.Cells[summaryName + 4, 1] = "P Value";
sheet.Cells[name + 1, 1] = "Constant";
sheet.Cells[name, betaCoeffName] = "Beta Coefficient";
sheet.Cells[name, stdErrorName] = "Standard Error";
sheet.Cells[name, waldValueName] = "Wald Value";
sheet.Cells[name, pValueName] = "p Value";
sheet.Cells[name, lowerLimitName] = "Lower Limit (95%)";
sheet.Cells[name, upperLimitName] = "Upper Limit (95%)";
((Range)sheet.Cells[name + 1, 1]).EntireColumn.AutoFit();
((Range)sheet.Cells[name, betaCoeffName]).EntireColumn.AutoFit();
((Range)sheet.Cells[name, stdErrorName]).EntireColumn.AutoFit();
((Range)sheet.Cells[name, waldValueName]).EntireColumn.AutoFit();
((Range)sheet.Cells[name, pValueName]).EntireColumn.AutoFit();
((Range)sheet.Cells[name, lowerLimitName]).EntireColumn.AutoFit();
((Range)sheet.Cells[name, upperLimitName]).EntireColumn.AutoFit();
for (var i = 0; i < independentVariables.Count; i++)
{
sheet.Cells[name + i + 2, 1] = independentVariables[i].name;
}
sheet.Cells[classificationMatrixName, 1] = "Classification Matrix";
sheet.Cells[classificationMatrixName, 2] = "1";
sheet.Cells[classificationMatrixName, 3] = "0";
sheet.Cells[classificationMatrixName, 4] = "Percentage Correct";
sheet.Cells[classificationMatrixName + 1, 1] = "1";
sheet.Cells[classificationMatrixName + 2, 1] = "0";
sheet.Cells[summaryClassificationName, 1] = "Classification Summary";
sheet.Cells[summaryClassificationName, 2] = "Percentage";
sheet.Cells[summaryClassificationName + 1, 1] = "Correct";
sheet.Cells[summaryClassificationName + 2, 1] = "Base";
sheet.Cells[summaryClassificationName + 3, 1] = "Improvement";
sheet.Cells[summaryClassificationName + 1, 2] = summaryClassificationPercentages[0];
sheet.Cells[summaryClassificationName + 2, 2] = summaryClassificationPercentages[1];
sheet.Cells[summaryClassificationName + 3, 2] = summaryClassificationPercentages[2];
((Range)sheet.Cells[summaryClassificationName, 1]).EntireColumn.AutoFit();
((Range)sheet.Cells[9, 1]).EntireRow.AutoFit();
sheet.Cells[dataName, 1] = "";
for (var i = 0; i < independentVariables.Count; i++)
{
sheet.Cells[dataName, 2 + i] = independentVariables[i].name;
}
sheet.Cells[dataName, probName] = "Probability";
sheet.Cells[dataName, probName + 1] = "Forecast";
sheet.Cells[dataName, probName + 2] = "Class";
for (var i = 0; i < dataSet.rangeSize(); i++)
{
sheet.Cells[dataName + 1 + i, 1] = i + 1;
for (var j = 0; j < independentVariables.Count; j++)
{
sheet.Cells[dataName + 1 + i, 2 + j] = matrixX[i, j + 1];
}
var prob = 1 / (1 + Math.Exp(-expThetaX[0, i]));
sheet.Cells[dataName + 1 + i, probName] = prob;
if (prob > 0.5)
{
sheet.Cells[dataName + 1 + i, probName + 1] = 1;
}
else
{
sheet.Cells[dataName + 1 + i, probName + 1] = 0;
}
sheet.Cells[dataName + 1 + i, probName + 2] = matrixY[i, 0];
}
double dF = dataSet.rangeSize() - independentVariables.Count() - 1;
// print out beta coefficients and std error of coefficient
for (var i = 0; i < theta.RowCount; i++)
{
sheet.Cells[name + i + 1, betaCoeffName] = theta[i, 0];
var stdError = Math.Sqrt(matrixCovar[i, i]);
sheet.Cells[name + i + 1, stdErrorName] = stdError;
sheet.Cells[name + i + 1, waldValueName] = theta[i, 0] / stdError;
var pValue = functions.TDist(Math.Abs(theta[i, 0] / stdError), dF, 2);
sheet.Cells[name + i + 1, pValueName] = pValue;
var confidenceConstant = 1.96;
var lower = theta[i, 0] - stdError * confidenceConstant;
var upper = theta[i, 0] + stdError * confidenceConstant;
sheet.Cells[name + i + 1, lowerLimitName] = lower;
sheet.Cells[name + i + 1, upperLimitName] = upper;
}
//print out classification matrix
sheet.Cells[classificationMatrixName + 1, 2] = classificationMatrix[0];
sheet.Cells[classificationMatrixName + 1, 3] = classificationMatrix[1];
var n0 = Convert.ToDouble(classificationMatrix[0]) + Convert.ToDouble(classificationMatrix[1]);
sheet.Cells[classificationMatrixName + 1, 4] = Convert.ToDouble(classificationMatrix[0]) / (Convert.ToDouble(classificationMatrix[0]) + Convert.ToDouble(classificationMatrix[1]));
sheet.Cells[classificationMatrixName + 2, 2] = classificationMatrix[2];
sheet.Cells[classificationMatrixName + 2, 3] = classificationMatrix[3];
var n1 = Convert.ToDouble(classificationMatrix[2]) + Convert.ToDouble(classificationMatrix[3]);
sheet.Cells[classificationMatrixName + 2, 4] = Convert.ToDouble(classificationMatrix[3]) / (Convert.ToDouble(classificationMatrix[2]) + Convert.ToDouble(classificationMatrix[3]));
// summary measures
var nullDeviance = 2 * (-n0 * Math.Log(n0 / (n0 + n1)) - n1 * Math.Log(n1 / (n0 + n1)));
double modelDeviance = 0;
for (var i = 0; i < dataSet.rangeSize(); i++)
{
var prob = 1 / (1 + Math.Exp(-expThetaX[0, i]));
double yValue = matrixY[i, 0];
modelDeviance = modelDeviance + yValue * Math.Log(1 / prob) + (1 - yValue) * Math.Log(1 / (1 - prob));
}
modelDeviance = modelDeviance * 2;
sheet.Cells[summaryName + 1, 2] = nullDeviance;
sheet.Cells[summaryName + 2, 2] = modelDeviance;
sheet.Cells[summaryName + 3, 2] = nullDeviance - modelDeviance;
sheet.Cells[summaryName + 4, 2] = "P Value";
return(true);
}
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;
}
// vanaf hier copy van oude code
public void Print(DataSet dataSet, List <Variable> variables, bool rdbMovingAverage, bool rdbSimpleExponentialSmoothing, bool rdbHoltsExponentialSmoothing, bool rdbWintersExponentialSmoothing, bool doOptimizeParameters, int numberOfForecasts, int numberOfHoldouts, int seasonalPeriod, int span, string level, string trend, string seasonality)
{
// Create new sheet
var sheet = WorksheetHelper.NewWorksheet("Forecast");
// use variables
_doOptimizeParameters = doOptimizeParameters;
_numberOfForecasts = numberOfForecasts;
_numberOfHoldouts = numberOfHoldouts;
_seasonalPeriod = seasonalPeriod;
_span = span;
// variables for writing on sheet and writing of title
int title = 1;
int forcastingConstants = 3;
int summaryName = forcastingConstants + 5;
int row = summaryName + 5;
sheet.Cells[title, 1] = "Forecast ";
sheet.Cells[forcastingConstants, 1] = "Forecasting Constants";
sheet.Cells[summaryName, 1] = "Summary";
sheet.Cells[summaryName + 1, 1] = "Mean Absolute Error";
sheet.Cells[summaryName + 2, 1] = "Root Mean Squared Error";
sheet.Cells[summaryName + 3, 1] = "Mean Absolute Percentage Error";
((Range)sheet.Cells[row, 1]).EntireColumn.AutoFit();
// variables to count number of forecast, holdouts and data
var nForecasts = Convert.ToDouble(numberOfForecasts);
var nHoldouts = Convert.ToDouble(numberOfHoldouts);
var nData = Convert.ToDouble(dataSet.rangeSize());
int column = 2;
foreach (Variable variable in variables)
{
row = 1;
double[,] array = RangeHelper.To2DDoubleArray(variable.getRange());
double[] array2 = new double[array.Length];
for (int i = 0; i < array.Length; i++)
{
array2[i] = array[i, 0]; // hier moet nog iets komen waardoor het mogelijk wordt om variabelen in rijen te verwerken
}
//Print data
column++;
sheet.Cells[row++, column] = variable.name;
for (int i = 0; i < array2.Length; i++)
{
sheet.Cells[row++, column] = array2[i];
}
row = 1;
column++;
// Plot next figure below the data and the forecast
int rowFigure = row + variables.Count + Convert.ToInt16(nForecasts) + 2;
// Calculate moving average forecast if box is checked
if (rdbMovingAverage)
{
EvaluateMovingAverage(array2, row, column, sheet);
string name = "Forecast Moving Average: " + variable.name;
//var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]];
var rangeData = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]];
var rangeForecast = sheet.Range[sheet.Cells[row + 2, column], sheet.Cells[row + nData + nForecasts, column]];
new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name);
}
// Calculate exponential smoothing (simple) forecast if box is checked
if (rdbSimpleExponentialSmoothing)
{
CalculateSimple(array2, row, sheet, column);
string name = "Forecast Exponential smoothing (Simple): " + variable.name;
//var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]];
var rangeData = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]];
var rangeForecast = sheet.Range[sheet.Cells[row + 2, column + 1], sheet.Cells[row + nData + nForecasts, column + 1]];
new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name);
}
// Calculate exponential smoothing (holt) forecast if box is checked
if (rdbHoltsExponentialSmoothing)
{
CalculateHolt(array2, row, sheet, column);
string name = "Forecast Exponential smoothing (Holt): " + variable.name;
//var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]];
var rangeData = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]];
var rangeForecast = sheet.Range[sheet.Cells[row + 2, column + 2], sheet.Cells[row + nData + nForecasts, column + 2]];
new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name);
}
// Calculate exponential smoothing (winters) forecast if box is checked
if (rdbWintersExponentialSmoothing)
{
CalculateWinter(array2, row, sheet, column);
string name = "Forecast Exponential smoothing (Winter): " + variable.name;
//var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]];
var rangeData = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]];
var rangeForecast = sheet.Range[sheet.Cells[row + 2, column + 3], sheet.Cells[row + nData + nForecasts, column + 3]];
new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name);
}
column = column + 6;
}
}
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 bool checkInput(List <Variable> variables, DataSet selectedDataSet, bool rdbMean, bool rdbMedian)
{
if (variables.Count == 0)
{
return(false);
}
_Worksheet worksheet = WorksheetHelper.NewWorksheet("Runs test");
int column = 1;
int row = 2;
worksheet.Cells[row, column] = "Runs test for randomness";
worksheet.Cells[row++, column] = "Observations";
if (rdbMean)
{
worksheet.Cells[row++, column] = "Mean";
}
if (rdbMedian)
{
worksheet.Cells[row++, column] = "Median";
}
worksheet.Cells[row++, column] = "Below cutoff";
worksheet.Cells[row++, column] = "Above cutoff";
worksheet.Cells[row++, column] = "Number of runs";
worksheet.Cells[row++, column] = "E(R)";
worksheet.Cells[row++, column] = "Stddev(R)";
worksheet.Cells[row++, column] = "Z-Value";
worksheet.Cells[row++, column] = "P-Value (two-tailed)";
((Range)worksheet.Cells[row, column]).EntireColumn.AutoFit();
row = 1;
column = 2;
foreach (Variable variable in variables)
{
worksheet.Cells[row++, column] = variable.name;
var range = variable.getRange().Address(true, true, true);
worksheet.Cells[row++, column] = selectedDataSet.rangeSize();
var ntotal = selectedDataSet.rangeSize();
if (rdbMean)
{
worksheet.Cells[row++, column] = "=AVERAGE(" + range + ")";
}
if (rdbMedian)
{
worksheet.Cells[row++, column] = "=MEDIAN(" + range + ")";
}
var cutoffValue = (double)worksheet.Cells[row - 1, column].Value;
int amountOfRuns = calculateRuns(worksheet, selectedDataSet, variable.getRange(), cutoffValue);
worksheet.WriteFunction(row++, column, "COUNTIF(" + range + ",\"<\"&" + AddressConverter.CellAddress(row - 2, column) + ")");
worksheet.WriteFunction(row++, column, "COUNTIF(" + range + ",\">\"&" + AddressConverter.CellAddress(row - 3, column) + ")");
worksheet.Cells[row++, column] = amountOfRuns;
worksheet.WriteFunction(row++, column, "1 + (2*" + AddressConverter.CellAddress(row - 4, column) + "*" + AddressConverter.CellAddress(row - 3, column) + ")/(" + AddressConverter.CellAddress(row - 4, column) + "+" + AddressConverter.CellAddress(row - 3, column) + ")");
worksheet.WriteFunction(row++, column, "SQRT(2*" + AddressConverter.CellAddress(row - 5, column) + "*" + AddressConverter.CellAddress(row - 4, column) + "*(2*" + AddressConverter.CellAddress(row - 5, column) + "*" + AddressConverter.CellAddress(row - 4, column) + "-" + AddressConverter.CellAddress(row - 5, column) + "-" + AddressConverter.CellAddress(row - 4, column) + ")/((" + AddressConverter.CellAddress(row - 5, column) + "+" + AddressConverter.CellAddress(row - 4, column) + ")*(" + AddressConverter.CellAddress(row - 5, column) + "+" + AddressConverter.CellAddress(row - 4, column) + ")*(" + AddressConverter.CellAddress(row - 5, column) + "+" + AddressConverter.CellAddress(row - 4, column) + "-1)))");
worksheet.WriteFunction(row++, column, "(" + AddressConverter.CellAddress(row - 4, column) + "-" + AddressConverter.CellAddress(row - 3, column) + ")/" + AddressConverter.CellAddress(row - 2, column));
worksheet.WriteFunction(row++, column, "2*(1-NORMSDIST(ABS(" + AddressConverter.CellAddress(row - 2, column) + ")))");
((Range)worksheet.Cells[row, column]).EntireColumn.AutoFit();
row = 1;
column++;
}
return(true);
}
