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]];
}
/// <summary>
/// Set the alpha value of the mean confidence interval.
/// </summary>
/// <param name="row">The row in which the mean confidence level is in.</param>
/// <param name="column">The column in which the mean confidence level is in.</param>
public void SetMeanAlpha(int row, int column)
{
MeanAlpha = "1 - " + AddressConverter.CellAddress(row, column);
MeanConfidenceInterval = "CONFIDENCE.T(" + MeanAlpha + "," + StandardDeviation + "," + Count + ")";
MeanLowerLimit = Mean + "-" + MeanConfidenceInterval;
MeanUpperLimit = Mean + "+" + MeanConfidenceInterval;
}
/// <summary>
/// Set the alpha value of the standard deviation confidence interval.
/// </summary>
/// <param name="row">The row in which the standard deviation confidence level is in.</param>
/// <param name="column">The column in which the standard deviation confidence level is in.</param>
public void SetStandardDeviationAlpha(int row, int column)
{
StandardDeviationAlpha = "1 - " + AddressConverter.CellAddress(row, column);
StandardDeviationConfidenceIntervalLowerLimit = "CHISQ.INV(1-(" + StandardDeviationAlpha + ")/2," + DegreesOfFreedom + ")";
StandardDeviationConfidenceIntervalUpperLimit = "CHISQ.INV((" + StandardDeviationAlpha + ")/2," + DegreesOfFreedom + ")";
StandardDeviationLowerLimit = StandardDeviation + "*SQRT((" + DegreesOfFreedom + ")/" + StandardDeviationConfidenceIntervalLowerLimit + ")";
StandardDeviationUpperLimit = StandardDeviation + "*SQRT((" + DegreesOfFreedom + ")/" + StandardDeviationConfidenceIntervalUpperLimit + ")";
}
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 void createChiKwadraatPlot(DataSet dataSet)
{
string range = model.range.Replace("$", null);
//string range = model.range;
//string[] splitted = range.Split(':');
string first = range.Split(':')[0];
string second = range.Split(':')[1];
/*if (splitted[0].Length > 4)
* {
* int i = splitted[0].Length;
* first = splitted[0][1] + Convert.ToString(splitted[0][3]);
* int j = 4;
* while (j < i)
* {
* first = first + Convert.ToString(splitted[0][j]);
* j++;
* }
* }
* else
* {
* first = splitted[0][1] + Convert.ToString(splitted[0][3]);
* }
* if (splitted[1].Length > 4)
* {
* int i = splitted[1].Length;
* second = splitted[1][1] + Convert.ToString(splitted[1][3]);
* int j = 4;
* while (j < i)
* {
* second = second + Convert.ToString(splitted[1][j]);
* j++;
* }
* }
* else
* {
* second = splitted[1][1] + Convert.ToString(splitted[1][3]);
* }*/
_Worksheet sheet = WorksheetHelper.NewWorksheet("Chi Square");
Range from = dataSet.getWorksheet().Range[first, second];
int rangeWidth = from.Columns.Count;
int rangeHeight = from.Rows.Count;
//original Counts
sheet.Cells[2, 1] = "Original Counts";
//sheet.get_Range("A2").Cells.HorizontalAlignment = XlHAlign.xlHAlignFill;
sheet.get_Range("A2", AddressConverter.CellAddress(2, rangeWidth + 2, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
Range to = sheet.Range[sheet.Cells[3, 2], sheet.Cells[3, 2].offset(rangeHeight, rangeWidth)];
from.Copy(to);
int col = 2;
while (col < rangeWidth + 2)
{
int row = 3;
double sum = 0;
while (row < rangeHeight + 3)
{
sum += Convert.ToDouble((sheet.Cells[row, col] as Range).Value);
row++;
}
sheet.Cells[3 + rangeHeight, col] = sum;
col++;
}
sheet.Cells[2, col] = "Total";
int roww = 3;
while (roww < rangeHeight + 4)
{
int coll = 2;
double summ = 0;
while (coll < rangeWidth + 2)
{
summ += Convert.ToDouble((sheet.Cells[roww, coll] as Range).Value);
coll++;
}
sheet.Cells[roww, 2 + rangeWidth] = summ;
roww++;
}
sheet.Cells[roww - 1, 1] = "Total";
// percentage of Rows
sheet.Cells[rangeHeight + 6, 1] = "Percentage of Rows";
sheet.get_Range("A2", AddressConverter.CellAddress(rangeHeight + 6, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowb = rangeHeight + 7;
while (rowb < rangeHeight + rangeHeight + 7)
{
int cola = 2;
while (cola < rangeWidth + 2)
{
double proda = 0;
proda = Convert.ToDouble((sheet.Cells[rowb - rangeHeight - 4, cola] as Range).Value) / Convert.ToDouble((sheet.Cells[rowb - rangeHeight - 4, rangeWidth + 2] as Range).Value);
sheet.Cells[rowb, cola] = Math.Round(proda * 100, 2);
//sheet.Range[rowb, cola].NumberFormat = "0.00%";
cola++;
}
rowb++;
}
//Percentage of Colums
sheet.Cells[rangeHeight + rangeHeight + 9, 1] = "Percentage of Colums";
sheet.get_Range("A2", AddressConverter.CellAddress(2 * rangeHeight + 9, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowd = rangeHeight + rangeHeight + 10;
while (rowd < rangeHeight + rangeHeight + rangeHeight + 10)
{
int colc = 2;
while (colc < rangeWidth + 2)
{
double prodc = 0;
prodc = Convert.ToDouble((sheet.Cells[rowd - rangeHeight - rangeHeight - 7, colc] as Range).Value) / Convert.ToDouble((sheet.Cells[3 + rangeHeight, colc] as Range).Value);
sheet.Cells[rowd, colc] = Math.Round(prodc * 100, 2);
//sheet.Range[rowd, colc].NumberFormat = "0.00%";
colc++;
}
rowd++;
}
// Expected Counts
sheet.Cells[3 * (rangeHeight) + 12, 1] = "Expected Counts";
sheet.get_Range("A2", AddressConverter.CellAddress(3 * rangeHeight + 12, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowe = 3 * rangeHeight + 13;
while (rowe < 4 * (rangeHeight) + 13)
{
int cole = 2;
while (cole < rangeWidth + 2)
{
double prode = 0;
prode = Convert.ToDouble((sheet.Cells[rangeHeight + 3, cole] as Range).Value)
* Convert.ToDouble((sheet.Cells[rowe - 3 * (rangeHeight) - 10, rangeWidth + 2] as Range).Value)
/ Convert.ToDouble((sheet.Cells[rangeHeight + 3, rangeWidth + 2] as Range).Value);
sheet.Cells[rowe, cole] = prode;
cole++;
}
rowe++;
}
//distance ftom expected
sheet.Cells[4 * rangeHeight + 15, 1] = "Distance from Expected";
sheet.get_Range("A2", AddressConverter.CellAddress(4 * rangeHeight + 15, rangeWidth + 1, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowf = 4 * rangeHeight + 16;
while (rowf < 5 * rangeHeight + 16)
{
int colf = 2;
while (colf < rangeWidth + 2)
{
double prodf = 0;
double tussenprod = Convert.ToDouble((sheet.Cells[rowf - 4 * rangeHeight - 13, colf] as Range).Value)
- Convert.ToDouble((sheet.Cells[rowf - rangeHeight - 3, colf] as Range).Value);
prodf = Math.Pow(tussenprod, 2)
/ Convert.ToDouble((sheet.Cells[rowf - rangeHeight - 3, colf] as Range).Value);
sheet.Cells[rowf, colf] = Math.Round(prodf, 4);
colf++;
}
rowf++;
}
//chi square dings
sheet.Cells[5 * rangeHeight + 18, 1] = "Chi-Square Statistic";
sheet.Cells[5 * rangeHeight + 19, 1] = "CHi-Square";
sheet.Cells[5 * rangeHeight + 20, 1] = "p-value";
//=1-CHIKW.VERD(B39;3*3;1)
sheet.get_Range("A2", AddressConverter.CellAddress(5 * rangeHeight + 18, 2, false, false)).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
int rowg = 4 * rangeHeight + 16;
double som = 0;
while (rowg < 5 * rangeHeight + 16)
{
int colg = 2;
while (colg < rangeWidth + 2)
{
som += Convert.ToDouble((sheet.Cells[rowg, colg] as Range).Value);
colg++;
}
rowg++;
}
sheet.Cells[5 * rangeHeight + 19, 2] = som;
sheet.Cells[5 * rangeHeight + 20, 2] = sheet.Application.WorksheetFunction.ChiSq_Dist(som, (rangeHeight - 1) * (rangeWidth - 1), false);
//autofit
((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();
//((Range)sheet.Cells[1, 9]).EntireColumn.AutoFit();
//((Range)sheet.Cells[1, 10]).EntireColumn.AutoFit();
// Merge Headers
sheet.Range[sheet.Cells[1, 2], sheet.Cells[1, rangeWidth + 2]].Merge();
sheet.Range[sheet.Cells[rangeHeight + 5, 2], sheet.Cells[rangeHeight + 5, rangeWidth + 1]].Merge();
sheet.Range[sheet.Cells[2 * rangeHeight + 8, 2], sheet.Cells[2 * rangeHeight + 8, rangeWidth + 1]].Merge();
sheet.Range[sheet.Cells[3 * rangeHeight + 11, 2], sheet.Cells[3 * rangeHeight + 11, rangeWidth + 1]].Merge();
sheet.Range[sheet.Cells[4 * rangeHeight + 14, 2], sheet.Cells[4 * rangeHeight + 14, rangeWidth + 1]].Merge();
// Build Headers
var header = "";
if (model.hasRowTitle)
{
header += "Rows: {0}";
}
if (model.hasRowTitle && model.hasColTitle)
{
header += " / ";
}
if (model.hasColTitle)
{
header += "Columns: {1}";
}
header = String.Format(header, model.rowTitle, model.colTitle);
// Fill in Headers
sheet.Cells[1, 2] = header;
sheet.Cells[rangeHeight + 5, 2] = header;
sheet.Cells[2 * rangeHeight + 8, 2] = header;
sheet.Cells[3 * rangeHeight + 11, 2] = header;
sheet.Cells[4 * rangeHeight + 14, 2] = header;
// Center Headers
sheet.Cells[1, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[rangeHeight + 5, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[2 * rangeHeight + 8, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[3 * rangeHeight + 11, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
sheet.Cells[4 * rangeHeight + 14, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
// Row and column titles
if (model.hasRowColHeaders)
{
String[] rowTitles = new String[rangeHeight];
String[] colTitles = new String[rangeWidth];
for (int i = 0; i < rangeHeight; i++)
{
rowTitles[i] = dataSet.getWorksheet().Range[first].Offset[i, -1].Value;
}
for (int i = 0; i < rangeWidth; i++)
{
colTitles[i] = dataSet.getWorksheet().Range[first].Offset[-1, i].Value;
}
for (int i = 0; i < rangeHeight; i++)
{
sheet.Cells[3 + i, 1] = rowTitles[i];
sheet.Cells[7 + rangeHeight + i, 1] = rowTitles[i];
sheet.Cells[10 + 2 * rangeHeight + i, 1] = rowTitles[i];
sheet.Cells[13 + 3 * rangeHeight + i, 1] = rowTitles[i];
sheet.Cells[16 + 4 * rangeHeight + i, 1] = rowTitles[i];
}
for (int i = 0; i < rangeWidth; i++)
{
sheet.Cells[2, 2 + i] = colTitles[i];
sheet.Cells[6 + rangeHeight, 2 + i] = colTitles[i];
sheet.Cells[9 + 2 * rangeHeight, 2 + i] = colTitles[i];
sheet.Cells[12 + 3 * rangeHeight, 2 + i] = colTitles[i];
sheet.Cells[15 + 4 * rangeHeight, 2 + i] = colTitles[i];
}
}
}
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;
}
/// <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);
}
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]];
}
