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);
}
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);
}
public bool checkInput(List <Variable> variables, 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.rangeSize() - 1;
}
if (rdbObservationsInRange && stopindex >= dataSet.rangeSize())
{
stopindex = dataSet.rangeSize() - 1;
}
if (rdbPlotAllObservations)
{
plotstartindex = 0;
plotstopindex = dataSet.rangeSize() - 1;
}
if (rdbPlotObservationsInRange && plotstopindex >= dataSet.rangeSize())
{
plotstopindex = dataSet.rangeSize() - 1;
}
_Worksheet sheet = WorksheetHelper.NewWorksheet("XR Chart");
generateXRChart(variables, 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 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);
}
// 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 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);
}
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 bool checkInput(DataSet dataSet, List <Variable> independentVariables, Variable dependentVariable, string probability, string misclassification0, string misclassification1)
{
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("Discriminant Analysis");
var matrixX = Matrix <double> .Build.Dense(dataSet.rangeSize(), independentVariables.Count);
for (var i = 0; i < dataSet.rangeSize(); i++)
{
for (var j = 0; j < independentVariables.Count; j++)
{
matrixX[i, j] = 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];
}
// Tables location
const int setupName = 3;
const int summaryName = setupName + 4;
const int functionName = summaryName + 4;
var matrixName = functionName + independentVariables.Count + 2;
var classificationName = matrixName + 4;
var covarsName = classificationName + 5;
var cutOffName = covarsName + independentVariables.Count + 2;
var dataName = cutOffName + 3;
var discrimName = independentVariables.Count + 2;
// write tables
sheet.Cells[1, 1] = "Discriminant Analysis";
sheet.Cells[setupName, 1] = "Setup";
sheet.Cells[setupName, 2] = "Prior Probability of 0";
sheet.Cells[setupName, 3] = "Misclassication cost: 0 as 1";
sheet.Cells[setupName, 4] = "Misclassication cost: 1 as 0";
((Range)sheet.Cells[setupName, 1]).EntireColumn.AutoFit();
((Range)sheet.Cells[setupName, 2]).EntireColumn.AutoFit();
((Range)sheet.Cells[setupName, 3]).EntireColumn.AutoFit();
((Range)sheet.Cells[setupName, 4]).EntireColumn.AutoFit();
sheet.Cells[setupName + 1, 2] = probability;
sheet.Cells[setupName + 1, 3] = misclassification0;
sheet.Cells[setupName + 1, 4] = misclassification1;
sheet.Cells[summaryName, 1] = "Sample Summary";
sheet.Cells[summaryName + 1, 1] = "0";
sheet.Cells[summaryName + 2, 1] = "1";
sheet.Cells[summaryName, 2] = "Sample Size";
for (var i = 0; i < independentVariables.Count; i++)
{
sheet.Cells[summaryName, 3 + i] = "Mean " + independentVariables[i].name;
}
sheet.Cells[functionName, 1] = "Discriminant Function";
sheet.Cells[functionName, 2] = "Coefficient";
for (var i = 0; i < independentVariables.Count; i++)
{
sheet.Cells[functionName + 1 + i, 1] = independentVariables[i].name;
}
sheet.Cells[matrixName, 1] = "Classification Matrix";
sheet.Cells[matrixName + 1, 1] = "0";
sheet.Cells[matrixName + 2, 1] = "1";
sheet.Cells[matrixName, 2] = "0";
sheet.Cells[matrixName, 3] = "1";
sheet.Cells[matrixName, 4] = "Correct";
sheet.Cells[classificationName, 1] = "Classification Summary";
sheet.Cells[classificationName + 1, 1] = "Correct";
sheet.Cells[classificationName + 2, 1] = "Base";
sheet.Cells[classificationName + 3, 1] = "Improvement";
sheet.Cells[covarsName, 1] = "Covariance Matrix";
for (var i = 0; i < independentVariables.Count; i++)
{
sheet.Cells[covarsName + 1 + i, 1] = independentVariables[i].name;
sheet.Cells[covarsName, 2 + i] = independentVariables[i].name;
}
sheet.Cells[cutOffName, 1] = "Cut off ";
sheet.Cells[dataName, 1] = "Data";
for (var i = 0; i < independentVariables.Count; i++)
{
sheet.Cells[dataName, 2 + i] = independentVariables[i].name;
}
sheet.Cells[dataName, discrimName] = "Discriminant";
sheet.Cells[dataName, discrimName + 1] = "Class";
sheet.Cells[dataName, discrimName + 2] = "Analysis";
var mean = new double[independentVariables.Count];
var mean0 = Matrix <double> .Build.Dense(independentVariables.Count, 1);
var mean1 = Matrix <double> .Build.Dense(independentVariables.Count, 1);
// mean for all X
for (var i = 0; i < independentVariables.Count; i++)
{
mean[i] = matrixX.Column(i).Average();
}
var n = dataSet.rangeSize();
var n0 = 0;
var n1 = 0;
var total0 = new double[independentVariables.Count];
var total1 = new double[independentVariables.Count];
//mean for X_0 and X_1, total elements n_0 and n_1
for (var i = 0; i < dataSet.rangeSize(); i++)
{
if (matrixY[i, 0] == 0)
{
n0++;
for (var j = 0; j < independentVariables.Count; j++)
{
total0[j] = total0[j] + matrixX[i, j];
}
}
else
{
n1++;
for (var j = 0; j < independentVariables.Count; j++)
{
total1[j] = total1[j] + matrixX[i, j];
}
}
}
sheet.Cells[summaryName + 1, 2] = n0;
sheet.Cells[summaryName + 2, 2] = n1;
for (var i = 0; i < independentVariables.Count; i++)
{
mean0[i, 0] = total0[i] / Convert.ToDouble(n0);
mean1[i, 0] = total1[i] / Convert.ToDouble(n1);
sheet.Cells[summaryName + 1, 3 + i] = mean0[i, 0];
sheet.Cells[summaryName + 2, 3 + i] = mean1[i, 0];
}
// create X_average matrix
var matrixXAver = Matrix <double> .Build.Dense(dataSet.rangeSize(), independentVariables.Count);
for (var i = 0; i < dataSet.rangeSize(); i++)
{
if (matrixY[i, 0] == 0)
{
for (var j = 0; j < independentVariables.Count; j++)
{
matrixXAver[i, j] = mean0[j, 0];
}
}
else
{
for (var j = 0; j < independentVariables.Count; j++)
{
matrixXAver[i, j] = mean1[j, 0];
}
}
}
var matrixXTransposed = matrixX.Transpose() * matrixX;
var matrixXAverTransposed = matrixXAver.Transpose() * matrixXAver;
var matrixS = Matrix <double> .Build.Dense(matrixXTransposed.RowCount, matrixXTransposed.ColumnCount);
for (var i = 0; i < matrixS.RowCount; i++)
{
for (var j = 0; j < matrixS.ColumnCount; j++)
{
matrixS[i, j] = (matrixXTransposed[i, j] - matrixXAverTransposed[i, j]) / (n - independentVariables.Count);
sheet.Cells[covarsName + 1 + i, 2 + j] = matrixS[i, j];
}
}
var matrixSInv = matrixS.Inverse();
var matrixResult = matrixSInv * (mean0 - mean1);
for (var i = 0; i < matrixResult.RowCount; i++)
{
sheet.Cells[functionName + 1 + i, 2] = matrixResult[i, 0];
}
var z0 = mean0.Transpose() * matrixResult;
var z1 = mean1.Transpose() * matrixResult;
var cutOff = (z0[0, 0] + z1[0, 0]) / 2 + Math.Log(Convert.ToDouble(misclassification1) * (1 - Convert.ToDouble(probability)) / Convert.ToDouble(misclassification0) / Convert.ToDouble(probability));
sheet.Cells[cutOffName + 1, 1] = cutOff;
var correctClass0 = 0;
var correctClass1 = 0;
for (var i = 0; i < dataSet.rangeSize(); i++)
{
sheet.Cells[dataName + 1 + i, 1] = i + 1;
double discrim = 0;
for (var j = 0; j < independentVariables.Count; j++)
{
sheet.Cells[dataName + 1 + i, j + 2] = matrixX[i, j];
discrim = discrim + matrixX[i, j] * matrixResult[j, 0];
}
sheet.Cells[dataName + 1 + i, discrimName] = discrim;
sheet.Cells[dataName + 1 + i, discrimName + 1] = matrixY[i, 0];
if (discrim > cutOff)
{
sheet.Cells[dataName + 1 + i, discrimName + 2] = 0;
if (matrixY[i, 0] == 0)
{
correctClass0++;
}
}
else
{
sheet.Cells[dataName + 1 + i, discrimName + 2] = 1;
if (matrixY[i, 0] == 1)
{
correctClass1++;
}
}
}
sheet.Cells[matrixName + 1, 2] = correctClass0;
sheet.Cells[matrixName + 1, 3] = n0 - correctClass0;
sheet.Cells[matrixName + 2, 3] = correctClass1;
sheet.Cells[matrixName + 2, 2] = n1 - correctClass1;
sheet.Cells[matrixName + 1, 4] = Convert.ToDouble(correctClass0) / Convert.ToDouble(n0);
sheet.Cells[matrixName + 2, 4] = Convert.ToDouble(correctClass1) / Convert.ToDouble(n1);
var correct = Convert.ToDouble(correctClass0 + correctClass1) / Convert.ToDouble(n);
double basis;
if (Convert.ToDouble(n0) * 2 > Convert.ToDouble(n))
{
basis = Convert.ToDouble(n0) / Convert.ToDouble(n);
}
else
{
basis = Convert.ToDouble(n1) / Convert.ToDouble(n);
}
var improvement = (correct - basis) / (1 - basis);
sheet.Cells[classificationName + 1, 2] = correct;
sheet.Cells[classificationName + 2, 2] = basis;
sheet.Cells[classificationName + 3, 2] = improvement;
return(true);
}
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 createRegression(DataSet dataSet, List <Variable> independentVariables, Variable dependentVariable)
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("Simple 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];
}
var matrixXt = matrixX.Transpose();
var matrixXtX = matrixXt * matrixX;
var matrixInv = matrixXtX.Inverse();
var matrixInvXt = matrixInv * matrixXt;
var matrixResult = matrixInvXt * matrixY;
const int title = 1;
const int name = 3;
const int betaCoeff = 2;
const int stdErrorName = betaCoeff + 1;
const int pValuesName = stdErrorName + 1;
const int lowerLimitName = pValuesName + 1;
const int upperLimitName = lowerLimitName + 1;
var summaryName = name + independentVariables.Count + 3;
var anovaTableName = summaryName + 3;
const int degreesOfFreedomName = 2;
const int sumOfSquaresName = 3;
const int meanOfSquaresName = 4;
const int anovaFValueName = 5;
const int anovaPValueName = 6;
var dataName = anovaTableName + 4;
sheet.Cells[title, 1] = "Regression";
sheet.Cells[name + 1, 1] = "Constant";
sheet.Cells[name, betaCoeff] = "Beta";
sheet.Cells[name, stdErrorName] = "Standard Error";
sheet.Cells[name, pValuesName] = "P-Value";
sheet.Cells[name, lowerLimitName] = "Lower Limit";
sheet.Cells[name, upperLimitName] = "Upper Limit";
sheet.Cells[summaryName + 1, 1] = "Regression Summary";
sheet.Cells[summaryName, 2] = "R-Square";
sheet.Cells[summaryName, 3] = "Adjusted R-Square";
sheet.Cells[summaryName, 4] = "Standard Error of estimation";
if (model.regressionEquation)
{
sheet.Cells[summaryName, 5] = "Regression Equation";
var regressionEquation = "" + matrixResult[0, 0];
for (var i = 0; i < independentVariables.Count; i++)
{
regressionEquation = regressionEquation + " + (" + matrixResult[i + 1, 0] + "*" + independentVariables[i].name + ")";
}
sheet.Cells[summaryName + 1, 5] = dependentVariable.name + " = " + regressionEquation;
}
sheet.Cells[anovaTableName, 1] = "Anova Table";
sheet.Cells[anovaTableName + 1, 1] = "Explained";
sheet.Cells[anovaTableName + 2, 1] = "Unexplained";
sheet.Cells[anovaTableName, degreesOfFreedomName] = "Degrees of Freedom";
sheet.Cells[anovaTableName + 1, degreesOfFreedomName] = independentVariables.Count;
var dF = dataSet.rangeSize() - 1 - independentVariables.Count;
sheet.Cells[anovaTableName + 2, degreesOfFreedomName] = dF;
sheet.Cells[anovaTableName, sumOfSquaresName] = "Sum of Squares";
var sumOfSquare = CalculateSumOfSquares(dataSet, independentVariables, dependentVariable, matrixResult);
sheet.Cells[anovaTableName + 1, sumOfSquaresName] = sumOfSquare[0];
sheet.Cells[anovaTableName + 2, sumOfSquaresName] = sumOfSquare[1];
sheet.Cells[anovaTableName, meanOfSquaresName] = "Mean of Squares";
sheet.Cells[anovaTableName + 1, meanOfSquaresName] = sumOfSquare[0] / independentVariables.Count;
sheet.Cells[anovaTableName + 2, meanOfSquaresName] = sumOfSquare[1] / (dataSet.rangeSize() - 1 - independentVariables.Count);
sheet.Cells[anovaTableName, anovaFValueName] = "F";
sheet.Cells[anovaTableName, anovaPValueName] = "P-Value";
var fTest = (sumOfSquare[0] / independentVariables.Count) / (sumOfSquare[1] / (dataSet.rangeSize() - 1 - independentVariables.Count));
sheet.Cells[anovaTableName + 1, anovaFValueName] = fTest;
sheet.Cells[anovaTableName + 1, anovaPValueName] = _functions.FDist(fTest, independentVariables.Count, dataSet.rangeSize() - 1 - independentVariables.Count);
sheet.Cells[dataName, 1] = "Data";
sheet.Cells[dataName, 2] = "Y: " + dependentVariable.name;
sheet.Cells[dataName, 3] = "Fit";
sheet.Cells[dataName, 4] = "Residuals";
// calculate r-square, adj r-square and std error of estimation
var rSquare = CalculateRsquare(dataSet, independentVariables, dependentVariable, matrixResult);
var adjRSquare = CalculateAdjRSquare(rSquare, matrixX);
var stdErrorEstimation = CalculateStdErrorEstimation(dataSet, independentVariables, dependentVariable, matrixResult);
sheet.Cells[summaryName + 1, 2] = rSquare;
sheet.Cells[summaryName + 1, 3] = adjRSquare;
sheet.Cells[summaryName + 1, 4] = stdErrorEstimation;
for (int i = 1; i < matrixResult.RowCount; i++)
{
sheet.Cells[name + i + 1, 1] = independentVariables[i - 1].name;
}
var meanOfSquaresError = sumOfSquare[1] / (dataSet.rangeSize() - 1 - independentVariables.Count);
for (var i = 0; i < matrixResult.RowCount; i++)
{
var coeff = matrixResult[i, 0];
var stdError = Math.Sqrt(matrixInv[i, i] * meanOfSquaresError);
var pValue = _functions.TDist(Math.Abs(coeff / stdError), dF, 2);
var confidenceConstant = _functions.T_Inv_2T(1 - model.confidenceLevel, dF);
var lower = coeff - stdError * confidenceConstant;
var upper = coeff + stdError * confidenceConstant;
sheet.Cells[name + i + 1, betaCoeff] = coeff;
sheet.Cells[name + i + 1, stdErrorName] = stdError;
sheet.Cells[name + i + 1, pValuesName] = pValue;
sheet.Cells[name + i + 1, lowerLimitName] = lower;
sheet.Cells[name + i + 1, upperLimitName] = upper;
}
for (var i = 0; i < independentVariables.Count; i++)
{
sheet.Cells[dataName, 5 + i] = independentVariables[i].name;
}
var nextFigure = CreateDataRegression(matrixX, matrixY, matrixResult, dataName, sheet);
if (model.fittedVSActual)
{
var rangeX = sheet.Range[sheet.Cells[dataName + 1, 2], sheet.Cells[dataName + dataSet.rangeSize(), 2]];
var rangeY = sheet.Range[sheet.Cells[dataName + 1, 3], sheet.Cells[dataName + dataSet.rangeSize(), 3]];
nextFigure = CreateNewFigure(rangeX, rangeY, nextFigure, "Fitted Values vs Actual Y-Values: " + dependentVariable.name, sheet);
}
if (model.residualsVSFitted)
{
var rangeX = sheet.Range[sheet.Cells[dataName + 1, 3], sheet.Cells[dataName + dataSet.rangeSize(), 3]];
var rangeY = sheet.Range[sheet.Cells[dataName + 1, 4], sheet.Cells[dataName + dataSet.rangeSize(), 4]];
nextFigure = CreateNewFigure(rangeX, rangeY, nextFigure, "Residuals vs Fitted Values", sheet);
}
if (model.residualsVSX)
{
var rangeY = sheet.Range[sheet.Cells[dataName + 1, 4], sheet.Cells[dataName + dataSet.rangeSize(), 4]];
for (var i = 0; i < independentVariables.Count; i++)
{
var rangeX = sheet.Range[sheet.Cells[dataName + 1, 5 + i], sheet.Cells[dataName + dataSet.rangeSize(), 5 + i]];
var nameX = independentVariables[i].name;
nextFigure = CreateNewFigure(rangeX, rangeY, nextFigure, "Residuals vs " + nameX, sheet);
}
}
}
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 bool checkInput(List <Variable> variables, DataSet selectedDataSet, bool rdbMean, bool rdbMedian, bool rdbCustomValue, string CustomCutoffValue)
{
if (variables.Count == 0)
{
MessageBox.Show("Please correct all fields to generate Runs Test.");
return(false); // wanneer de gebruiker geen variabele geselecteerd heeft, stop functie
}
_Worksheet worksheet = WorksheetHelper.NewWorksheet("Runs test");
int column = 1;
int row = 2;
worksheet.Cells[row, column] = "Runs test for randomness"; // schrijf strings naar worksheet
worksheet.Cells[row++, column] = "Observations";
if (rdbMean)
{
worksheet.Cells[row++, column] = "Mean";
}
if (rdbMedian)
{
worksheet.Cells[row++, column] = "Median";
}
if (rdbCustomValue)
{
worksheet.Cells[row++, column] = "Custom cutoff Value";
}
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) // deze loop wordt herhaald voor elke geselecteerde variabele van datagridview
{
worksheet.Cells[row++, column] = variable.name; // schrijf naam variabele naar worksheet
var range = variable.getRange().Address(true, true, true); // sla range variabele op in "range"
worksheet.Cells[row++, column] = selectedDataSet.rangeSize(); // schrijf de hoeveelheid gegevens in de variabele naar worksheet
var ntotal = selectedDataSet.rangeSize();
if (rdbMean)
{
worksheet.Cells[row++, column] = "=AVERAGE(" + range + ")"; // schrijf afhankelijk van de gebruikersinput de cutoffvalue naar worksheet
}
if (rdbMedian)
{
worksheet.Cells[row++, column] = "=MEDIAN(" + range + ")";
}
if (rdbCustomValue)
{
worksheet.Cells[row++, column] = CustomCutoffValue;
}
var cutoffValue = (double)worksheet.Cells[row - 1, column].Value; // lees de cutoffvalue vanuit excel en sla ze op in variabele 'cutoffvalue'
int amountOfRuns = calculateRuns(worksheet, selectedDataSet, variable.getRange(), cutoffValue); // roep functie calculateRuns aan en sla het resultaat op in amountofruns
worksheet.WriteFunction(row++, column, "COUNTIF(" + range + ",\"<\"&" + AddressConverter.CellAddress(row - 2, column) + ")"); // schrijf functie voor het berekenen van #above cutoff naar worksheet
worksheet.WriteFunction(row++, column, "COUNTIF(" + range + ",\">\"&" + AddressConverter.CellAddress(row - 3, column) + ")"); // schrijf functie voor het berekenen van #below cutoff naar worksheet
worksheet.Cells[row++, column] = amountOfRuns; // schrijf het resultaat van functie calculate Runs naar worksheet
worksheet.WriteFunction(row++, column, "(2*" + AddressConverter.CellAddress(row - 4, column) + "*" + AddressConverter.CellAddress(row - 3, column) + ")/(" + AddressConverter.CellAddress(row - 6, column) + ")"); // schrijf overige functies naar worksheet
worksheet.WriteFunction(row++, column, "SQRT(((" + AddressConverter.CellAddress(row - 2, column) + "-1)*(" + AddressConverter.CellAddress(row - 2, column) + "-2))/" + AddressConverter.CellAddress(row - 7, column) + ")");
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);
}
