/// <summary>
/// Print the Summary Statistics to a new <see cref="_Worksheet"/>.
/// </summary>
/// <param name="dataSet">The <see cref="DataSet"/> which needs it Correlation and/or Covariance printed.</param>
/// <param name="doInclude">A <see cref="List{T}"/> of <see cref="bool"/>s that corresponds to which <see cref="Models.Data"/> in the <see cref="DataSet.DataList"/> should be included.</param>
/// <param name="doCalculate">A collection of <see cref="bool"/>s that indicate which summary statistic has to be calculated.</param>
/// <param name="meanConfidenceLevel">(Optional) The confidence level for the <see cref="SummaryStatistics.Mean"/>. Default is 0.</param>
/// <param name="standardDeviationConfidenceLevel">(Optional) The confidence level for the <see cref="SummaryStatistics.StandardDeviation"/>. Default is 0.</param>
/// <param name="row">(Optional) The first row the data will be written to. Default is 1.</param>
/// <param name="column">(Optional) The first column the data will be written to. Default is 1.</param>
/// <param name="sheet">(Optional) An existing <see cref="_Worksheet"/>. A new one will be created when left blank. Default is null.</param>
/// <param name="sheetName">(Optional) The name of the sheet. Default is 'One-Variable Summary'.</param>
/// <param name="doIsNumericCheck">(Optional) Set to 'false' if the IsNumeric check should be suppressed. Default is true.</param>
/// <returns>A <see cref="bool"/> that indicates if the print was successful or not.</returns>
public bool Print(DataSet dataSet, List <bool> doInclude, SummaryStatisticsBool doCalculate, int meanConfidenceLevel = 0, int standardDeviationConfidenceLevel = 0, int row = 1, int column = 1, _Worksheet sheet = null, string sheetName = "One-Variable Summary", bool doIsNumericCheck = true)
{
// Save the variables as a field variables.
_doCalculate = doCalculate;
_column = column;
// Check the input so everything is working like intended.
var check = new CheckInput(dataSet, sheetName, doInclude, DefaultCheck.Numeric, doIsNumericCheck: doIsNumericCheck, doCalculate: doCalculate);
if (!check.Successful)
{
return(false);
}
ValuesArray = check.Values;
// Create a new sheet for the chart if none was provided.
Sheet = sheet ?? WorksheetHelper.NewWorksheet(sheetName);
// Print the categories to the sheet if there is at least 1 Set of Data selected.
if (ValuesArray.Count > 0)
{
_row = row;
PrintCategories(sheetName);
}
// Print the calculated variables for each DataSets Data.
foreach (var data in ValuesArray)
{
_row = row;
PrintValues(data, meanConfidenceLevel, standardDeviationConfidenceLevel);
}
return(true);
}
/// <summary>
/// Print the Summary Statistics to a new <see cref="_Worksheet"/>.
/// </summary>
/// <param name="dataSet">The <see cref="DataSet"/> which needs it Correlation and/or Covariance printed.</param>
/// <param name="doIncludeCategory">A <see cref="List{T}"/> of <see cref="bool"/>s that corresponds to which <see cref="Models.Data"/> in the <see cref="DataSet.DataList"/> should be included as the category.</param>
/// <param name="doIncludeValue">A <see cref="List{T}"/> of <see cref="bool"/>s that corresponds to which <see cref="Models.Data"/> in the <see cref="DataSet.DataList"/> should be included as the value.</param>
/// <param name="doCalculate">A collection of <see cref="bool"/>s that indicate which summary statistic has to be calculated.</param>
/// <param name="meanConfidenceLevel">(Optional) The confidence level for the <see cref="SummaryStatistics.Mean"/>. Default is 0.</param>
/// <param name="standardDeviationConfidenceLevel">(Optional) The confidence level for the <see cref="SummaryStatistics.StandardDeviation"/>. Default is 0.</param>
/// <param name="row">(Optional) The first row the data will be written to. Default is 1.</param>
/// <param name="column">(Optional) The first column the data will be written to. Default is 1.</param>
/// <param name="sheet">(Optional) An existing <see cref="_Worksheet"/>. A new one will be created when left blank. Default is null.</param>
/// <param name="sheetName">(Optional) The name of the sheet. Default is 'One-Variable Summary'.</param>
/// <param name="doIsNumericCheck">(Optional) Set to 'false' if the IsNumeric check should be suppressed. Default is true.</param>
/// <returns>A <see cref="bool"/> that indicates if the print was successful or not.</returns>
public bool Print(DataSet dataSet, List <bool> doIncludeCategory, List <bool> doIncludeValue, SummaryStatisticsBool doCalculate, int meanConfidenceLevel = 0, int standardDeviationConfidenceLevel = 0, int row = 1, int column = 1, _Worksheet sheet = null, string sheetName = "One-Variable Summary", bool doIsNumericCheck = true)
{
// Save the variables as a field variables.
_doCalculate = doCalculate;
_column = column;
// Check the input so everything is working like intended.
var check = new CheckInput(dataSet, sheetName, doIncludeCategory, DefaultCheck.Nonnumeric, -1, doIncludeValue, DefaultCheck.Numeric, 1, doCalculate, isCategory: true, doIsNumericCheck: doIsNumericCheck);
if (!check.Successful)
{
return(false);
}
var categories = check.Categories;
ValuesArray = check.Values;
// Create a new sheet for the chart if none was provided.
Sheet = sheet ?? WorksheetHelper.NewWorksheet(sheetName);
// Create a new DataSet.
dataSet = DataSet.GetByCategories(Sheet, categories[0], ValuesArray, row, _column + 1);
// Create an array that holds the Sets of Data that need a Summary Statistic.
ValuesArray = dataSet.DataList;
// Print the categories to the sheet if there is at least 1 Set of Data selected.
if (ValuesArray.Count > 0)
{
_row = dataSet.Range.Row + dataSet.Range.Rows.Count;
PrintCategories(sheetName);
}
// Print the calculated variables for each DataSets Data.
foreach (var valuesArray in ValuesArray)
{
_row = dataSet.Range.Row + dataSet.Range.Rows.Count;
PrintValues(valuesArray, meanConfidenceLevel, standardDeviationConfidenceLevel);
}
return(true);
}
/// <summary>
/// Print the values to the <see cref="_Worksheet"/>.
/// </summary>
/// <param name="doCalculate">A collection of <see cref="bool"/>s that indicate which summary statistic has to be calculated.</param>
private void PrintValues(SummaryStatisticsBool doCalculate)
{
// Save the initial row.
var initialRow = _row;
// Calculate the Correlation and/or Covariance of the data.
var ranges = new List <Range>();
foreach (var valuesArray in _valuesArray)
{
ranges.Add(valuesArray.Range);
}
var calc = new Models.CorrelationCovariance();
// Write the values to she sheet.
for (var i = 0; i < _valuesArray.Count; i++)
{
_row = initialRow;
if (calc.HasCorrelations)
{
_sheet.Cells[_row, _column] = _valuesArray[i].Name;
for (var j = 0; j < calc.Dimension; j++)
{
_sheet.Cells[++_row, _column] = "=" + calc.Correlations[i * calc.Dimension + j];
}
_row += 2;
}
if (calc.HasCovariances)
{
_sheet.Cells[_row, _column] = _valuesArray[i].Name;
for (var j = 0; j < calc.Dimension; j++)
{
_sheet.Cells[++_row, _column] = "=" + calc.Covariances[i * calc.Dimension + j];
}
}
_column++;
}
}
/// <summary>
/// Print the Correlation and/or Covariance to a new <see cref="Worksheet"/>.
/// </summary>
/// <param name="dataSet">The <see cref="DataSet"/> which needs it Correlation and/or Covariance printed.</param>
/// <param name="doInclude">A <see cref="List{T}"/> of <see cref="bool"/>s that corresponds to which <see cref="Models.Data"/> in the <see cref="DataSet.DataList"/> should be included.</param>
/// <param name="doCalculate">A collection of <see cref="bool"/>s that indicate which summary statistic has to be calculated.</param>
/// <param name="row">(Optional) The first row the data will be written to. Default is 1.</param>
/// <param name="column">(Optional) The first column the data will be written to. Default is 1.</param>
/// <returns>A <see cref="bool"/> that indicates if the print was successful or not.</returns>
public bool Print(DataSet dataSet, List <bool> doInclude, SummaryStatisticsBool doCalculate, int row = 1, int column = 1)
{
// Check the input so everything is working like intended.
var check = new CheckInput(dataSet, "Correlation & Covariance", doInclude, DefaultCheck.Numeric, doCalculate: doCalculate);
if (!check.Successful)
{
return(false);
}
_valuesArray = check.Values;
// Save the variables as a field variables.
_row = row;
_column = column;
// Create a new sheet for the chart and print the categories to the sheet.
if (doCalculate.Correlation && doCalculate.Covariance)
{
PrintCategories("Correlation");
_row++;
PrintCategories("Covariance");
}
else if (doCalculate.Correlation)
{
PrintCategories("Correlation");
}
else
{
PrintCategories("Covariance");
}
_row = row;
_column++;
// Print the values to the sheet.
PrintValues(doCalculate);
return(true);
}
public bool Print(DataSet dataSet, List <bool> doInclude, SummaryStatisticsBool doCalculate, bool doOptimizeParameters, bool doUseLabels, int labelsId, int numberOfForecasts, int numberOfHoldouts, int seasonalPeriod, int span, string level, string trend, string seasonality)
{
_doOptimizeParameters = doOptimizeParameters;
_numberOfForecasts = numberOfForecasts;
_numberOfHoldouts = numberOfHoldouts;
_seasonalPeriod = seasonalPeriod;
_span = span;
if (!double.TryParse(level, out _level))
{
MessageBox.Show("The Level is not a valid number.", "Forecast", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
if (!double.TryParse(trend, out _trend))
{
MessageBox.Show("The Trend is not a valid number.", "Forecast", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
if (!double.TryParse(seasonality, out _seasonality))
{
MessageBox.Show("The Seasonality is not a valid number.", "Forecast", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
// Create new lists (reference) for Data and labels
var valuesArraysData = new List <Models.Data>();
var valuesArraysLabel = new List <Models.Data>();
// Create new sheet
var sheet = WorksheetHelper.NewWorksheet("Forecast");
// add data to the data list
for (var j = 0; j < dataSet.DataList.Count; j++)
{
if (!doInclude[j])
{
continue;
}
valuesArraysData.Add(dataSet.DataList[j]);
}
// add labels to labels list
valuesArraysLabel.Add(dataSet.DataList[labelsId]);
// 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";
sheet.Cells[row, 1] = "Label";
sheet.Cells[row, 2] = valuesArraysData[0].Name;
// variables to count number of forecast, holdouts and data
var nForecasts = Convert.ToDouble(numberOfForecasts);
var nHoldouts = Convert.ToDouble(numberOfHoldouts);
var nData = Convert.ToDouble(valuesArraysData[0].GetValuesList().Count);
// create array to put in labels
string[] arrayLabels = new string[valuesArraysLabel[0].GetValuesList().Count + Convert.ToInt16(nForecasts)];
// put in labels in array if label box is checked and print out labels in excel
if (doUseLabels)
{
for (int i = 0; i < valuesArraysLabel[0].GetValuesList().Count; i++)
{
arrayLabels[i] = valuesArraysLabel[0].GetValuesList()[i].ToString() + " ";
sheet.Cells[row + 1 + i, 1] = arrayLabels[i];
}
} // else put in 1, 2, 3, ... and print out labels in excel
else
{
for (int i = 0; i < valuesArraysLabel[0].GetValuesList().Count; i++)
{
arrayLabels[i] = Convert.ToString(i + 1);
sheet.Cells[row + 1 + i, 1] = arrayLabels[i];
}
}
// Add labels for forecast and print out labels in excel
double differenceLabels = Convert.ToDouble(arrayLabels[arrayLabels.Length - 1 - Convert.ToInt16(nForecasts)]) - Convert.ToDouble(arrayLabels[arrayLabels.Length - 2 - Convert.ToInt16(nForecasts)]);
for (int i = 0; i < nForecasts; i++)
{
int arrayPosition = valuesArraysLabel[0].GetValuesList().Count + i;
arrayLabels[arrayPosition] = Convert.ToString(Convert.ToDouble(arrayLabels[arrayPosition - 1]) + differenceLabels);
sheet.Cells[row + arrayPosition + 1, 1] = arrayLabels[arrayPosition];
}
// Print out Data in excel
for (int i = 0; i < valuesArraysData[0].GetValuesList().Count; i++)
{
sheet.Cells[row + 1 + i, 2] = valuesArraysData[0].GetValuesList()[i];
}
// Plot next figure below the data and the forecast
int rowFigure = row + valuesArraysLabel[0].GetValuesList().Count + Convert.ToInt16(nForecasts) + 2;
// Calculate moving average forecast if box is checked
if (doCalculate.MovingAverage)
{
EvaluateMovingAverage(valuesArraysData[0].GetValuesArray(), row, sheet);
string name = "Forecast Moving Average: " + valuesArraysData[0].Name;
var rangeLabels = sheet.Range[sheet.Cells[row + 1, 1], sheet.Cells[row + nData + nForecasts, 1]];
var rangeData = sheet.Range[sheet.Cells[row + 1, 2], sheet.Cells[row + nData + nForecasts, 2]];
var rangeForecast = sheet.Range[sheet.Cells[row + 1, 3], sheet.Cells[row + nData + nForecasts, 3]];
new TimeSeriesGraph().CreateNewGraph(sheet, rowFigure, rangeData, rangeForecast, rangeLabels, name);
}
// Calculate exponential smoothing (simple) forecast if box is checked
if (doCalculate.SimpleExponentialSmoothing)
{
CalculateSimple(valuesArraysData[0].GetValuesArray(), row, sheet);
string name = "Forecast Exponential smoothing (Simple): " + valuesArraysData[0].Name;
var rangeLabels = sheet.Range[sheet.Cells[row + 1, 1], sheet.Cells[row + nData + nForecasts, 1]];
var rangeData = sheet.Range[sheet.Cells[row + 1, 2], sheet.Cells[row + nData + nForecasts, 2]];
var rangeForecast = sheet.Range[sheet.Cells[row + 1, 4], sheet.Cells[row + nData + nForecasts, 4]];
new TimeSeriesGraph().CreateNewGraph(sheet, rowFigure, rangeData, rangeForecast, rangeLabels, name);
}
// Calculate exponential smoothing (holt) forecast if box is checked
if (doCalculate.HoltsExponentialSmoothing)
{
CalculateHolt(valuesArraysData[0].GetValuesArray(), row, sheet);
string name = "Forecast Exponential smoothing (Holt): " + valuesArraysData[0].Name;
var rangeLabels = sheet.Range[sheet.Cells[row + 1, 1], sheet.Cells[row + nData + nForecasts, 1]];
var rangeData = sheet.Range[sheet.Cells[row + 1, 2], sheet.Cells[row + nData + nForecasts, 2]];
var rangeForecast = sheet.Range[sheet.Cells[row + 1, 5], sheet.Cells[row + nData + nForecasts, 5]];
new TimeSeriesGraph().CreateNewGraph(sheet, rowFigure, rangeData, rangeForecast, rangeLabels, name);
}
// Calculate exponential smoothing (winters) forecast if box is checked
if (doCalculate.WintersExponentialSmoothing)
{
CalculateWinter(valuesArraysData[0].GetValuesArray(), row, sheet);
string name = "Forecast Exponential smoothing (Winter): " + valuesArraysData[0].Name;
var rangeLabels = sheet.Range[sheet.Cells[row + 1, 1], sheet.Cells[row + nData + nForecasts, 1]];
var rangeData = sheet.Range[sheet.Cells[row + 1, 2], sheet.Cells[row + nData + nForecasts, 2]];
var rangeForecast = sheet.Range[sheet.Cells[row + 1, 6], sheet.Cells[row + nData + nForecasts, 6]];
new TimeSeriesGraph().CreateNewGraph(sheet, rowFigure, rangeData, rangeForecast, rangeLabels, name);
}
return(true);
}
public bool Print(DataSet dataSet, List <bool> doInclude, SummaryStatisticsBool doCalculate, string customCutoff)
{
// Check if the margin of error and the estimates are numbers.
double customCutoffValue;
if (!double.TryParse(customCutoff, out customCutoffValue))
{
MessageBox.Show("The Custom Cutoff Value is not a valid number.", "NoruST - Runs Test for Randomness", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
var functions = Globals.ExcelAddIn.Application.WorksheetFunction;
var sheet = WorksheetHelper.NewWorksheet("Runs Test");
var valuesArrays = new List <Models.Data>();
for (var j = 0; j < dataSet.DataList.Count; j++)
{
// Check if the Set of Data needs a Box-Whisker Plot. It will go to the next Set if the current one doesn't need one.
if (!doInclude[j])
{
continue;
}
// If the Set of Data needs a Box-Whisker Plot, add it to the list.
valuesArrays.Add(dataSet.DataList[j]);
}
// Create several arrays to hold the data that is needed to create the charts.
var names = new string[valuesArrays.Count];
var observations = new double[valuesArrays.Count];
var cutoff = new double[valuesArrays.Count];
var bCutoff = new double[valuesArrays.Count];
var aCutoff = new double[valuesArrays.Count];
var runs = new double[valuesArrays.Count];
var expected = new double[valuesArrays.Count];
var stdDev = new double[valuesArrays.Count];
var zValue = new double[valuesArrays.Count];
var pValue = new double[valuesArrays.Count];
for (var i = 0; i < valuesArrays.Count; i++)
{
// Add them to their respective arrays.
names[i] = valuesArrays[i].Name;
if (doCalculate.Mean)
{
cutoff[i] = functions.Average(valuesArrays[i].Range);
}
else if (doCalculate.Median)
{
cutoff[i] = functions.Median(valuesArrays[i].Range);
}
else
{
throw new NotImplementedException();
}
bCutoff[i] = 0;
aCutoff[i] = 0;
var control = "";
// Calculate below and above cutoff, total number of runs
foreach (var value in valuesArrays[i].GetValuesArray())
{
if (value <= cutoff[i])
{
bCutoff[i]++;
if (control == "below")
{
}
else
{
runs[i]++;
control = "below";
}
}
else
{
aCutoff[i]++;
if (control == "above")
{
}
else
{
runs[i]++;
control = "above";
}
}
observations[i]++;
}
expected[i] = 2 * (bCutoff[i] * aCutoff[i]) / observations[i] + 1;
stdDev[i] = Math.Sqrt((expected[i] - 1) * (expected[i] - 2) / (observations[i] - 1));
zValue[i] = (runs[i] - expected[i]) / stdDev[i];
pValue[i] = 2 * (1 - functions.NormSDist(Math.Abs(zValue[i])));
sheet.Cells[1, i + 2] = names[i];
sheet.Cells[2, i + 2] = observations[i];
sheet.Cells[3, i + 2] = bCutoff[i];
sheet.Cells[4, i + 2] = aCutoff[i];
sheet.Cells[5, i + 2] = runs[i];
sheet.Cells[6, i + 2] = cutoff[i];
sheet.Cells[7, i + 2] = expected[i];
sheet.Cells[8, i + 2] = stdDev[i];
sheet.Cells[9, i + 2] = zValue[i];
sheet.Cells[10, i + 2] = pValue[i];
}
string cutoffName;
if (doCalculate.Mean)
{
cutoffName = "mean";
}
else if (doCalculate.Median)
{
cutoffName = "median";
}
else
{
cutoffName = "cutoff";
}
sheet.Cells[1, 1] = "name";
sheet.Cells[2, 1] = "observations";
sheet.Cells[3, 1] = "below " + cutoffName;
sheet.Cells[4, 1] = "above" + cutoffName;
sheet.Cells[5, 1] = "number of runs";
sheet.Cells[6, 1] = cutoffName;
sheet.Cells[7, 1] = "E(R)";
sheet.Cells[8, 1] = "stdDev(R)";
sheet.Cells[9, 1] = "z-Value";
sheet.Cells[10, 1] = "p-Value";
return(true);
}
/// <summary>
/// Print the Summary Statistics to a new <see cref="_Worksheet"/>.
/// </summary>
/// <param name="doCalculate">A collection of <see cref="bool"/>s that indicate which summary statistic has to be calculated.</param>
/// <param name="confidenceLevel">The confidence level.</param>
/// <param name="marginOfError">The margin of error.</param>
/// <param name="estimated1">The estimated Standard Deviation or Proportion.</param>
/// <param name="estimated2">The 2nd estimated proportion.</param>
/// <param name="row">(Optional) The first row the data will be written to. Default is 1.</param>
/// <param name="column">(Optional) The first column the data will be written to. Default is 1.</param>
/// <param name="sheetName">(Optional) The name of the sheet. Default is 'Sample Size'.</param>
/// <returns>A <see cref="bool"/> that indicates if the print was successful or not.</returns>
/// <remarks><see cref="estimated2"/> is only used for the calculation of the <see cref="SummaryStatisticsBool.DifferenceOfProportionsSampleSize"/>.</remarks>
public bool Print(SummaryStatisticsBool doCalculate, int confidenceLevel, string marginOfError, string estimated1, string estimated2, int row = 1, int column = 1, string sheetName = "Sample Size")
{
// Check if the margin of error and the estimates are numbers.
double marginOfErrorValue, estimated1Value, estimated2Value;
if (!double.TryParse(marginOfError, out marginOfErrorValue))
{
MessageBox.Show("The Margin of Error is not a valid number.", "NoruST - Sample Size", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
if (!double.TryParse(estimated1.Replace(" ", "").Replace(",", ".").Trim(), out estimated1Value))
{
MessageBox.Show("The Estimation is not a valid number.", "NoruST - Sample Size", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
if (doCalculate.DifferenceOfProportionsSampleSize && !double.TryParse(estimated2.Replace(" ", "").Replace(",", ".").Trim(), out estimated2Value))
{
MessageBox.Show("The 2nd Estimation is not a valid number.", "NoruST - Sample Size", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
// Create a new sheet for the chart if none was provided.
var sheet = WorksheetHelper.NewWorksheet(sheetName);
// Print the categories and calculated variables to the sheet.
if (doCalculate.MeanSampleSize)
{
sheet.Cells[row++, column] = "Sample Size for Mean";
}
if (doCalculate.ProportionSampleSize)
{
sheet.Cells[row++, column] = "Sample Size for Proportion";
}
if (doCalculate.DifferenceOfMeansSampleSize)
{
sheet.Cells[row++, column] = "Sample Size for Difference of Means";
}
if (doCalculate.DifferenceOfProportionsSampleSize)
{
sheet.Cells[row++, column] = "Sample Size for Difference of Proportions";
}
sheet.Cells[row, column] = "Confidence Level";
var confidenceLevelRow = row;
sheet.Cells[row, column + 1] = confidenceLevel / 100.0;
((Range)sheet.Cells[row++, column + 1]).NumberFormat = "0.00%";
sheet.Cells[row, column] = "Alpha";
var alphaRow = row;
sheet.Cells[row++, column + 1] = "=1-" + AddressConverter.CellAddress(confidenceLevelRow, column + 1);
sheet.Cells[row, column] = "Margin of Error";
var marginOfErrorRow = row;
sheet.Cells[row++, column + 1] = marginOfErrorValue;
if (doCalculate.MeanSampleSize)
{
sheet.Cells[row, column] = "Estimated Standard Deviation";
}
if (doCalculate.ProportionSampleSize)
{
sheet.Cells[row, column] = "Estimated Proportion";
}
if (doCalculate.DifferenceOfMeansSampleSize)
{
sheet.Cells[row, column] = "Estimated Common Standard Deviation";
}
if (doCalculate.DifferenceOfProportionsSampleSize)
{
sheet.Cells[row, column] = "Estimated Proportion 1";
}
var estimate1Row = row;
sheet.Cells[row++, column + 1] = estimated1;
var estimate2Row = 0;
if (doCalculate.DifferenceOfProportionsSampleSize)
{
sheet.Cells[row, column] = "Estimated Proportion 2";
estimate2Row = row;
sheet.Cells[row++, column + 1] = estimated2;
}
sheet.Cells[row, column] = "Sample Size";
if (doCalculate.MeanSampleSize)
{
sheet.WriteFunction(row, column + 1, "CEILING.MATH(T.INV.2T(" + AddressConverter.CellAddress(alphaRow, column + 1) + ",1000000)^2*" + AddressConverter.CellAddress(estimate1Row, column + 1) + "^2/" + AddressConverter.CellAddress(marginOfErrorRow, column + 1) + "^2)");
}
if (doCalculate.ProportionSampleSize)
{
sheet.WriteFunction(row, column + 1, "CEILING.MATH(T.INV.2T(" + AddressConverter.CellAddress(alphaRow, column + 1) + ",1000000)^2*" + AddressConverter.CellAddress(estimate1Row, column + 1) + "*(1-" + AddressConverter.CellAddress(estimate1Row, column + 1) + ")/" + AddressConverter.CellAddress(marginOfErrorRow, column + 1) + "^2)");
}
if (doCalculate.DifferenceOfMeansSampleSize)
{
sheet.WriteFunction(row, column + 1, "CEILING.MATH(2*T.INV.2T(" + AddressConverter.CellAddress(alphaRow, column + 1) + ",1000000)^2*" + AddressConverter.CellAddress(estimate1Row, column + 1) + "^2/" + AddressConverter.CellAddress(marginOfErrorRow, column + 1) + "^2)");
}
if (doCalculate.DifferenceOfProportionsSampleSize)
{
sheet.WriteFunction(row, column + 1, "CEILING.MATH(T.INV.2T(" + AddressConverter.CellAddress(alphaRow, column + 1) + ",1000000)^2*(" + AddressConverter.CellAddress(estimate1Row, column + 1) + "*(1-" + AddressConverter.CellAddress(estimate1Row, column + 1) + ")+" + AddressConverter.CellAddress(estimate2Row, column + 1) + "*(1-" + AddressConverter.CellAddress(estimate2Row, column + 1) + "))/" + AddressConverter.CellAddress(marginOfErrorRow, column + 1) + "^2)");
}
return(true);
}
/// <summary>
/// Print the Discriminant Analysis to a new <see cref="Microsoft.Office.Interop.Excel._Worksheet"/>.
/// </summary>
/// <param name="dataSet">The <see cref="DataSet"/> which needs (a) Scatterplot(s).</param>
/// <param name="doIncludeX">A <see cref="List{T}"/> of <see cref="bool"/>s that corresponds to which <see cref="Models.Data"/> in the <see cref="DataSet.DataList"/> should be included for X.</param>
/// <param name="doIncludeY">A <see cref="List{T}"/> of <see cref="bool"/>s that corresponds to which <see cref="Models.Data"/> in the <see cref="DataSet.DataList"/> should be included for Y.</param>
/// <param name="doCalculate">A collection of <see cref="bool"/>s that indicate which summary statistic has to be calculated.</param>
/// <param name="confidenceLevel">The confidence level.</param>
public bool Print(DataSet dataSet, List <bool> doIncludeX, List <bool> doIncludeY, SummaryStatisticsBool doCalculate, int confidenceLevel)
{
var valuesArraysX = new List <Models.Data>();
var valuesArraysY = new List <Models.Data>();
var sheet = WorksheetHelper.NewWorksheet("Regression");
// Loop to add X
for (var j = 0; j < dataSet.DataList.Count; j++)
{
// Check if the Set of Data is an X.
if (!doIncludeX[j])
{
continue;
}
var safe = true;
foreach (var value in dataSet.DataList[j].GetValuesList())
{
if (value != null)
{
continue;
}
MessageBox.Show(dataSet.DataList[j].Name + " has null data and will not be included.", "NoruST - Discriminant Analysis", MessageBoxButtons.OK, MessageBoxIcon.Warning);
safe = false;
}
// If the Set of Data is an X, add it to the list.
if (safe)
{
valuesArraysX.Add(dataSet.DataList[j]);
}
}
for (var j = 0; j < dataSet.DataList.Count; j++)
{
// Check if the Set of Data is an Y.
if (!doIncludeY[j])
{
continue;
}
// If the Set of Data is Y add to list.
valuesArraysY.Add(dataSet.DataList[j]);
// only one Y (currently) so break loop if Y is found.
break;
}
// create X matrix
var matrixX = Matrix <double> .Build.Dense(valuesArraysX[0].GetValuesList().Count, valuesArraysX.Count + 1);
// create 1 column with 1
for (var i = 0; i < valuesArraysX[0].GetValuesList().Count; i++)
{
matrixX[i, 0] = 1;
for (var j = 0; j < valuesArraysX.Count; j++)
{
matrixX[i, j + 1] = valuesArraysX[j].GetValuesArray()[i];
}
}
// create Y matrix
var matrixY = Matrix <double> .Build.Dense(valuesArraysY[0].GetValuesList().Count, 1);
// create 1 column with 1
for (var i = 0; i < valuesArraysY[0].GetValuesList().Count; i++)
{
matrixY[i, 0] = valuesArraysY[0].GetValuesArray()[i];
}
var matrixXt = matrixX.Transpose();
var matrixXtX = matrixXt * matrixX;
var matrixInv = matrixXtX.Inverse();
var matrixInvXt = matrixInv * matrixXt;
var matrixResult = matrixInvXt * matrixY;
// variables for sheet
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 + valuesArraysX.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;
// names of variables on sheet
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 (doCalculate.DisplayRegressionEquation)
{
sheet.Cells[summaryName, 5] = "Regression Equation";
var regressionEquation = "" + matrixResult[0, 0];
for (var i = 0; i < valuesArraysX.Count; i++)
{
regressionEquation = regressionEquation + " + (" + matrixResult[i + 1, 0] + "*" + valuesArraysX[i].Name + ")";
}
sheet.Cells[summaryName + 1, 5] = valuesArraysY[0].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] = valuesArraysX.Count;
var dF = valuesArraysY[0].GetValuesList().Count - 1 - valuesArraysX.Count;
sheet.Cells[anovaTableName + 2, degreesOfFreedomName] = dF;
sheet.Cells[anovaTableName, sumOfSquaresName] = "Sum of Squares";
var sumOfSquare = CalculateSumOfSquares(valuesArraysX, valuesArraysY, 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] / valuesArraysX.Count;
sheet.Cells[anovaTableName + 2, meanOfSquaresName] = sumOfSquare[1] / (valuesArraysY[0].GetValuesList().Count - 1 - valuesArraysX.Count);
sheet.Cells[anovaTableName, anovaFValueName] = "F";
sheet.Cells[anovaTableName, anovaPValueName] = "P-Value";
var fTest = (sumOfSquare[0] / valuesArraysX.Count) / (sumOfSquare[1] / (valuesArraysY[0].GetValuesList().Count - 1 - valuesArraysX.Count));
sheet.Cells[anovaTableName + 1, anovaFValueName] = fTest;
sheet.Cells[anovaTableName + 1, anovaPValueName] = _functions.FDist(fTest, valuesArraysX.Count, valuesArraysY[0].GetValuesList().Count - 1 - valuesArraysX.Count);
sheet.Cells[dataName, 1] = "Data";
sheet.Cells[dataName, 2] = "Y: " + valuesArraysY[0].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(valuesArraysX, valuesArraysY, matrixResult);
var adjRSquare = CalculateAdjRSquare(rSquare, matrixX);
var stdErrorEstimation = CalculateStdErrorEstimation(valuesArraysX, valuesArraysY, 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] = valuesArraysX[i - 1].Name;
}
var meanOfSquaresError = sumOfSquare[1] / (valuesArraysY[0].GetValuesList().Count - 1 - valuesArraysX.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 - confidenceLevel / 100.0, 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 < valuesArraysX.Count; i++)
{
sheet.Cells[dataName, 5 + i] = valuesArraysX[i].Name;
}
var nextFigure = CreateDataRegression(matrixX, matrixY, matrixResult, dataName, sheet);
if (doCalculate.FittedValuesVsActualYValues)
{
var rangeX = sheet.Range[sheet.Cells[dataName + 1, 2], sheet.Cells[dataName + valuesArraysY[0].GetValuesList().Count, 2]];
var rangeY = sheet.Range[sheet.Cells[dataName + 1, 3], sheet.Cells[dataName + valuesArraysY[0].GetValuesList().Count, 3]];
nextFigure = CreateNewFigure(rangeX, rangeY, nextFigure, "Fitted Values vs Actual Y-Values: " + valuesArraysY[0].Name, sheet);
}
if (doCalculate.ResidualsVsFittedValues)
{
var rangeX = sheet.Range[sheet.Cells[dataName + 1, 3], sheet.Cells[dataName + valuesArraysY[0].GetValuesList().Count, 3]];
var rangeY = sheet.Range[sheet.Cells[dataName + 1, 4], sheet.Cells[dataName + valuesArraysY[0].GetValuesList().Count, 4]];
nextFigure = CreateNewFigure(rangeX, rangeY, nextFigure, "Residuals vs Fitted Values", sheet);
}
if (doCalculate.ResidualsVsXValues)
{
var rangeY = sheet.Range[sheet.Cells[dataName + 1, 4], sheet.Cells[dataName + valuesArraysY[0].GetValuesList().Count, 4]];
for (var i = 0; i < valuesArraysX.Count; i++)
{
var rangeX = sheet.Range[sheet.Cells[dataName + 1, 5 + i], sheet.Cells[dataName + valuesArraysY[0].GetValuesList().Count, 5 + i]];
var nameX = valuesArraysX[i].Name;
nextFigure = CreateNewFigure(rangeX, rangeY, nextFigure, "Residuals vs " + nameX, sheet);
}
}
return(true);
}
