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