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(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 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 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);
}
}
}
