private void PrintVariables(_Worksheet _sheet, int _row, int _counter, Variable variable)
{
var function = Globals.ExcelAddIn.Application.WorksheetFunction;
int numberOfBins = model.useBins && model.bins > 0 ? model.bins : (int)function.RoundUp(Math.Sqrt(function.Count(variable.getRange())), 0);
// Write the needed data to the sheet.
for (int bin = 0; bin < numberOfBins; bin++)
{
var column = 1;
var range = variable.getRange().Address(true, true, true);
_sheet.Cells[_row, column++] = "Bin #" + bin;
_sheet.WriteFunction(_row, column, bin == 0 ? "MIN(" + range + ")" : AddressConverter.CellAddress(_row - 1, column + 1)); column++;
_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "+" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)"); column++;
_sheet.Cells[_row, column] = "=(" + AddressConverter.CellAddress(_row, column - 2) + "+" + AddressConverter.CellAddress(_row, column - 1) + ")/2"; column++;
_sheet.WriteFunction(_row, column, "COUNTIF(" + range + ",\"<=\"&" + AddressConverter.CellAddress(_row, column - 2) + ")-COUNTIF(" + range + ",\"<\"&" + AddressConverter.CellAddress(_row, column - 3) + ")"); column++;
_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "COUNT(" + range + ")"); column++;
_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)");
_row++;
}
// Create the chart.
var charts = (ChartObjects)_sheet.ChartObjects();
var chartObject = charts.Add(400, 225 * _counter, 100 * numberOfBins, 200);
var chart = chartObject.Chart;
chart.ChartType = XlChartType.xlColumnClustered;
chart.ChartWizard(Title: "Histogram - " + variable.name, HasLegend: false);
var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.Add(_sheet.Range[_sheet.Cells[_row - numberOfBins, 5], _sheet.Cells[_row - 1, 5]]);
series.ChartType = XlChartType.xlColumnClustered;
series.XValues = _sheet.Range[_sheet.Cells[_row - numberOfBins, 4], _sheet.Cells[_row - 1, 4]];
}
public void createOneWayAnova(List <Variable> variables)
{
_Worksheet sheet = WorksheetHelper.NewWorksheet("One-Way ANOVA");
sheet.Cells[1, 1] = "ANOVA Summary";
sheet.Cells[2, 1] = "Total Sample Size";
sheet.Cells[3, 1] = "Grand Mean";
sheet.Cells[4, 1] = "Pooled Std Dev";
sheet.Cells[5, 1] = "Pooled Variance";
sheet.Cells[6, 1] = "Number of Samples";
sheet.Cells[7, 1] = "Confidence Level";
sheet.Cells[9, 1] = "ANOVA Sample Stats";
sheet.Cells[10, 1] = "Sample Size";
sheet.Cells[11, 1] = "Sample Mean";
sheet.Cells[12, 1] = "Sample Std Dev";
sheet.Cells[13, 1] = "Sample Variance";
sheet.Cells[14, 1] = "Pooling Weight";
sheet.Cells[17, 1] = "One-Way ANOVA Table";
sheet.Cells[18, 1] = "Between Variation";
sheet.Cells[19, 1] = "Within Variation";
sheet.Cells[20, 1] = "Total Variation";
sheet.Cells[23, 1] = "Confidence Interval Tests";
sheet.Cells[16, 2] = "Sum of";
sheet.Cells[17, 2] = "Squares";
sheet.Cells[16, 3] = "Degrees of";
sheet.Cells[17, 3] = "Freedom";
sheet.Cells[16, 4] = "Mean";
sheet.Cells[17, 4] = "Squares";
sheet.Cells[17, 5] = "F-Ratio";
sheet.Cells[17, 6] = "p-Value";
sheet.Cells[22, 2] = "Difference";
sheet.Cells[23, 2] = " of Means";
int col = 1;
foreach (Variable variable in variables)
{
col++;
var range = variable.getRange().Address(true, true, true);
sheet.Cells[9, col] = variable.name;
sheet.WriteFunction(10, col, "COUNT(" + range + ")");
sheet.WriteFunction(11, col, "AVERAGE(" + range + ")");
sheet.WriteFunction(12, col, "STDEV.S(" + range + ")");
sheet.WriteFunction(13, col, "VAR.S(" + range + ")");
}
sheet.WriteFunction(2, 2, "SUM(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + ")");
sheet.WriteFunction(3, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + "," + AddressConverter.CellAddress(11, 2, false, false) + ":" + AddressConverter.CellAddress(11, col, false, false) + ")/" + AddressConverter.CellAddress(2, 2, false, false));
sheet.Cells[6, 2] = variables.Count;
sheet.Cells[7, 2] = model.confidenceLevel;
((Range)sheet.Cells[7, 2]).NumberFormat = "0.00%";
col = 1;
foreach (Variable variable in variables)
{
col++;
sheet.WriteFunction(14, col, "(" + AddressConverter.CellAddress(10, col, false, false) + "-1)/(B2-B6)");
}
sheet.WriteFunction(5, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(13, 2, false, false) + ":" + AddressConverter.CellAddress(13, col, false, false) + "," + AddressConverter.CellAddress(14, 2, false, false) + ":" + AddressConverter.CellAddress(14, col, false, false) + ")");
sheet.WriteFunction(4, 2, "SQRT(" + AddressConverter.CellAddress(5, 2, false, false) + ")");
sheet.WriteFunction(18, 2, "SUMPRODUCT(" + AddressConverter.CellAddress(10, 2, false, false) + ":" + AddressConverter.CellAddress(10, col, false, false) + ",(" + AddressConverter.CellAddress(11, 2, false, false) + ":" + AddressConverter.CellAddress(11, col, false, false) + "-B3)^2)");
sheet.WriteFunction(19, 2, "(B2-" + variables.Count + ")*B5");
sheet.WriteFunction(20, 2, "B18+B19");
sheet.Cells[18, 3] = variables.Count - 1;
sheet.WriteFunction(19, 3, "B2-" + variables.Count);
sheet.WriteFunction(20, 3, "C18+C19");
sheet.WriteFunction(18, 4, "B18/C18");
sheet.WriteFunction(19, 4, "B19/C19");
sheet.WriteFunction(18, 5, "D18/D19");
sheet.WriteFunction(18, 6, "F.DIST.RT(E18,C18,C19)");
int row = 24;
int c = 0;
for (int i = 0; i < variables.Count; i++)
{
for (int j = i + 1; j < variables.Count; j++)
{
c++;
Variable var1 = variables[i];
Variable var2 = variables[j];
sheet.Cells[row, 1] = var1.name + " - " + var2.name;
sheet.WriteFunction(row, 2, AddressConverter.CellAddress(11, i + 2, false, false) + "-" + AddressConverter.CellAddress(11, j + 2, false, false));
col = 3;
if (model.noCorrection)
{
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-(ABS(T.INV((1-B7)/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+(ABS(T.INV((1-B7)/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
}
if (model.bonferroni)
{
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-(ABS(T.INV(((1-B7)/(B6*(B6-1)/2))/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "+(ABS(T.INV(((1-B7)/(B6*(B6-1)/2))/2,C19)))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
}
if (model.scheffe)
{
sheet.WriteFunction(row, col++, AddressConverter.CellAddress(row, 2, false, false) + "-SQRT((B6-1)*F.INV.RT(1-B7,B6-1,C19))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
sheet.WriteFunction(row, col, AddressConverter.CellAddress(row, 2, false, false) + "+SQRT((B6-1)*F.INV.RT(1-B7,B6-1,C19))*SQRT(D19*(1/" + AddressConverter.CellAddress(10, i + 2, false, false) + "+1/" + AddressConverter.CellAddress(10, j + 2, false, false) + "))");
}
row++;
}
}
col = 3;
if (model.noCorrection)
{
sheet.Cells[22, col] = "No Correction";
sheet.Range[AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)].Merge();
sheet.Cells[23, col] = "Lower";
sheet.Cells[23, col + 1] = "Upper";
sheet.Range[AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range[AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)].Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot;
col += 2;
}
if (model.bonferroni)
{
sheet.Cells[22, col] = "Bonferroni";
sheet.Range[AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)].Merge();
sheet.Cells[23, col] = "Lower";
sheet.Cells[23, col + 1] = "Upper";
sheet.Range[AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range[AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)].Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot;
col += 2;
}
if (model.scheffe)
{
sheet.Cells[22, col] = "Scheffe";
sheet.Range[AddressConverter.CellAddress(22, col, false, false), AddressConverter.CellAddress(22, col + 1, false, false)].Merge();
sheet.Cells[23, col] = "Lower";
sheet.Cells[23, col + 1] = "Upper";
sheet.Range[AddressConverter.CellAddress(23, col, false, false), AddressConverter.CellAddress(23, col + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range[AddressConverter.CellAddress(24, col, false, false), AddressConverter.CellAddress(23 + c, col, false, false)].Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlDot;
}
((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 3]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 4]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 5]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 6]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 7]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 8]).EntireColumn.AutoFit();
sheet.Range["B1", "J200"].Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Range["A1", "B1"].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range["A9", AddressConverter.CellAddress(9, variables.Count + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range["A17", "F17"].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range["A23", "B17"].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range["B3", "B5"].NumberFormat = "0.0000";
sheet.Range["B11", AddressConverter.CellAddress(14, variables.Count + 1, false, false)].NumberFormat = "0.000";
sheet.Range["B18", "B20"].NumberFormat = "0.0000";
sheet.Range["D18", "E19"].NumberFormat = "0.0000";
sheet.Range["B24", AddressConverter.CellAddress(23 + c, 2, false, false)].NumberFormat = "0.0000";
sheet.Range["C24", AddressConverter.CellAddress(23 + c, 8, false, false)].NumberFormat = "0.000000";
sheet.Range["C24", AddressConverter.CellAddress(23 + c, 8, false, false)].Cells.HorizontalAlignment = XlHAlign.xlHAlignRight;
Globals.ExcelAddIn.Application.ActiveWindow.DisplayGridlines = false;
}
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);
}
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);
}
public void estimateSampleSize()
{
//var doCalculate = new SummaryStatisticsBool(meanSampleSize: rdbMean.Checked, proportionSampleSize: rdbProportion.Checked, differenceOfMeansSampleSize: rdbDifferenceOfMeans.Checked, differenceOfProportionsSampleSize: rdbDifferenceOfProportions.Checked);
//new SampleSize().Print(doCalculate, (int)nudConfidenceLevel.Value, txtMarginOfError.Text, txtEstimate1.Text, txtEstimate2.Text);
double marginOfError, estimated1, estimated2 = 0.0, confidenceLevel = ((double)model.confidenceLevel) / 100.0;
if (!double.TryParse(model.marginOfError.Replace(" ", "").Replace(".", ",").Trim(), out marginOfError) && !double.TryParse(model.marginOfError.Replace(" ", "").Replace(",", ".").Trim(), out marginOfError))
{
MessageBox.Show("The Margin of Error is not a valid number.", "NoruST - Sample Size", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!double.TryParse(model.estimation1.Replace(" ", "").Replace(",", ".").Trim(), out estimated1) && !double.TryParse(model.estimation1.Replace(" ", "").Replace(".", ",").Trim(), out estimated1))
{
MessageBox.Show("The Estimation is not a valid number.", "NoruST - Sample Size", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (model.diffProportion && !double.TryParse(model.estimation2.Replace(" ", "").Replace(",", ".").Trim(), out estimated2) && !double.TryParse(model.estimation2.Replace(" ", "").Replace(".", ",").Trim(), out estimated2))
{
MessageBox.Show("The 2nd Estimation is not a valid number.", "NoruST - Sample Size", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
_Worksheet sheet = WorksheetHelper.NewWorksheet("Sample Size");
int row = 1, column = 1;
string txtValue = "Sample Size for ";
txtValue += model.mean ? "Mean" : model.proportion ? "Proportion" : model.diffMean ? "Difference of Means" : "Difference of Proportions";
sheet.Cells[row++, column] = txtValue;
sheet.Cells[row, column] = "Confidence Level";
var confidenceLevelRow = row;
sheet.Cells[row, column + 1] = confidenceLevel;
((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] = marginOfError;
txtValue = "Estimated ";
txtValue += model.mean ? "Standard Deviation" : model.proportion ? "Proportion" : model.diffMean ? "Common Standard Deviation" : "Proportion 1";
sheet.Cells[row, column] = txtValue;
var estimate1Row = row;
sheet.Cells[row++, column + 1] = estimated1;
var estimate2Row = 0;
if (model.diffProportion)
{
sheet.Cells[row, column] = "Estimated Proportion 2";
estimate2Row = row;
sheet.Cells[row++, column + 1] = estimated2;
}
sheet.Cells[row, column] = "Sample Size";
if (model.mean)
{
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 (model.proportion)
{
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 (model.diffMean)
{
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 (model.diffProportion)
{
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)");
}
((Range)sheet.Cells[1, 1]).EntireColumn.AutoFit();
((Range)sheet.Cells[1, 2]).EntireColumn.AutoFit();
}
public void createCorrelationCovariance(List <Variable> variables, bool correlation, bool covariance)
{
_Worksheet sheet = correlation && covariance?WorksheetHelper.NewWorksheet("Correlation and Covariance") : correlation?WorksheetHelper.NewWorksheet("Correlation") : WorksheetHelper.NewWorksheet("Covariance");
int correlationRow = 2;
int covarianceRow = correlation ? 4 + variables.Count : 2;
if (correlation)
{
sheet.Cells[correlationRow - 1, 1] = "Linear Correlation Table";
for (int i = 0; i < variables.Count; i++)
{
Variable varCol = variables[i];
var varColRange = varCol.getRange().Address(true, true, true);
sheet.Cells[correlationRow + i, 1] = varCol.name;
sheet.Cells[correlationRow - 1, 2 + i] = varCol.name;
for (int j = i; j < variables.Count; j++)
{
Variable varRow = variables[j];
var varRowRange = varRow.getRange().Address(true, true, true);
if (i == j)
{
sheet.Cells[correlationRow + i, 2 + i] = 1.0;
}
else
{
sheet.WriteFunction(correlationRow + j, 2 + i, "CORREL(" + varColRange + "," + varRowRange + ")");
sheet.WriteFunction(correlationRow + i, 2 + j, AddressConverter.CellAddress(correlationRow + j, 2 + i, false, false));
}
}
}
}
if (covariance)
{
sheet.Cells[covarianceRow - 1, 1] = "Covariance Table";
for (int i = 0; i < variables.Count; i++)
{
Variable varCol = variables[i];
var varColRange = varCol.getRange().Address(true, true, true);
sheet.Cells[covarianceRow + i, 1] = varCol.name;
sheet.Cells[covarianceRow - 1, 2 + i] = varCol.name;
for (int j = i; j < variables.Count; j++)
{
Variable varRow = variables[j];
var varRowRange = varRow.getRange().Address(true, true, true);
if (i == j)
{
sheet.WriteFunction(covarianceRow + i, 2 + i, "VAR(" + varColRange + ")");
}
else
{
sheet.WriteFunction(covarianceRow + j, 2 + i, "COVAR(" + varRowRange + "," + varColRange + ")");
sheet.WriteFunction(covarianceRow + i, 2 + j, AddressConverter.CellAddress(covarianceRow + j, 2 + i, false, false));
}
}
}
}
sheet.Range["B1", "ZZ200"].Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (correlation)
{
sheet.Range[AddressConverter.CellAddress(correlationRow - 1, 1, false, false), AddressConverter.CellAddress(correlationRow - 1, variables.Count + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range[AddressConverter.CellAddress(correlationRow, 2, false, false), AddressConverter.CellAddress(correlationRow + variables.Count - 1, variables.Count + 1, false, false)].NumberFormat = "0.000";
}
if (covariance)
{
sheet.Range[AddressConverter.CellAddress(covarianceRow - 1, 1, false, false), AddressConverter.CellAddress(covarianceRow - 1, variables.Count + 1, false, false)].Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
sheet.Range[AddressConverter.CellAddress(covarianceRow, 2, false, false), AddressConverter.CellAddress(covarianceRow + variables.Count - 1, variables.Count + 1, false, false)].NumberFormat = "0.000";
}
for (int i = 1; i <= variables.Count + 1; i++)
{
((Range)sheet.Cells[1, i]).EntireColumn.AutoFit();
}
Globals.ExcelAddIn.Application.ActiveWindow.DisplayGridlines = false;
}
private void PrintVariables(_Worksheet _sheet, int _row, int _counter, Variable variable, DataSet dataSet)
{
var function = Globals.ExcelAddIn.Application.WorksheetFunction;
int numberOfBins = model.useBins && model.bins > 0 ? model.bins : (int)function.RoundUp(Math.Sqrt(function.Count(variable.getRange())), 0);
_sheet.Cells[100, 100] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")";
int length = Convert.ToInt32((_sheet.Cells[100, 100] as Range).Value);
_sheet.Cells[100, 100] = "";
int count = 0;
int i = 0;
int j = 0;
double temp;
double[] yData = new double[length];
string ran = variable.Range.ToString();
Array arr = dataSet.getWorksheet().Range[ran].Value;
double[] vals = new double[length];
foreach (var item in arr)
{
temp = Convert.ToDouble(item);
yData[count] = temp;
count++;
}
List <double> Y = new List <double>(yData);
Y.Sort();
yData = Y.ToArray();
double start = yData[0];
double end = yData[length - 1];
double step = (end - start) / numberOfBins;
List <List <double> > finalList = new List <List <double> >();
i = 0;
double startVal = start;
double endVal = startVal + step;
while (i < numberOfBins)
{
List <double> tempList = calcVal(finalList, yData, startVal, endVal);
finalList.Add(tempList);
startVal = endVal;
endVal = endVal + step;
i++;
}
startVal = start;
endVal = startVal + step;
for (int bin = 0; bin < numberOfBins; bin++)
{
var column = 1;
var range = variable.getRange().Address(true, true, true);
_sheet.Cells[_row, column++] = "Bin #" + bin;
_sheet.Cells[_row, column++] = startVal;
_sheet.Cells[_row, column++] = endVal;
_sheet.Cells[_row, column++] = startVal + (endVal - startVal) / 2.0;
//_sheet.WriteFunction(_row, column, bin == 0 ? "MIN(" + range + ")" : AddressConverter.CellAddress(_row - 1, column + 1)); column++;
//_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "+" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)"); column++;
//_sheet.Cells[_row, column] = "=(" + AddressConverter.CellAddress(_row, column - 2) + "+" + AddressConverter.CellAddress(_row, column - 1) + ")/2"; column++;
_sheet.WriteFunction(_row, column, "COUNTIF(" + range + ",\"<=\"&" + AddressConverter.CellAddress(_row, column - 2) + ")-COUNTIF(" + range + ",\"<\"&" + AddressConverter.CellAddress(_row, column - 3) + ")"); column++;
_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "COUNT(" + range + ")");
double mean = (_sheet.Cells[_row, column] as Range).Value;
column++;
_sheet.Cells[_row, column] = mean / (endVal - startVal);
startVal = endVal;
endVal = endVal + step;
//_sheet.WriteFunction(_row, column, AddressConverter.CellAddress(_row, column - 1) + "/" + "ROUND((MAX(" + range + ")-MIN(" + range + "))/" + numberOfBins + ",0)");
_row++;
}
// Create the chart.
var charts = (ChartObjects)_sheet.ChartObjects();
var chartObject = charts.Add(400, 225 * _counter, 100 * numberOfBins, 200);
var chart = chartObject.Chart;
chart.ChartType = XlChartType.xlColumnClustered;
chart.ChartWizard(Title: "Histogram - " + variable.name, HasLegend: false);
var seriesCollection = (SeriesCollection)chart.SeriesCollection();
var series = seriesCollection.Add(_sheet.Range[_sheet.Cells[_row - numberOfBins, 5], _sheet.Cells[_row - 1, 5]]);
series.ChartType = XlChartType.xlColumnClustered;
series.XValues = _sheet.Range[_sheet.Cells[_row - numberOfBins, 4], _sheet.Cells[_row - 1, 4]];
}
