public void ApplyAnalysisToDataset(IExcelSheet sheet)
{
sheet[ExcelColumnIndex.J, 1].SetValue(ExcelStringsPhaseOnePartOne.NormalDistributionSolutionColumnName);
sheet.GetOrCreateCenterizedCell(ExcelColumnIndex.K, 1);
sheet.AddMergedRegion(ExcelColumnIndex.J, 1, ExcelColumnIndex.K, 1);
sheet[ExcelColumnIndex.J, 2].SetValue(ExcelStringsPhaseOnePartOne.PreliminarySampleSize);
sheet[ExcelColumnIndex.J, 3].SetValue(ExcelStringsPhaseOnePartOne.SampleMean);
sheet[ExcelColumnIndex.J, 4].SetValue(ExcelStringsPhaseOnePartOne.SampleVariance);
sheet[ExcelColumnIndex.J, 5].SetValue(ExcelStringsPhaseOnePartOne.SampleDeviation);
sheet[ExcelColumnIndex.J, 6].SetValue(ExcelStringsPhaseOnePartOne.VariationCoefficient);
sheet[ExcelColumnIndex.J, 7].SetValue(ExcelStringsPhaseOnePartOne.CalculatedSampleSize);
string lastValueRowIndex = _args.LaunchesNumber.SkipHeader().ToString();
sheet[ExcelColumnIndex.K, 2].SetFormula("$F$6");
sheet[ExcelColumnIndex.K, 3].SetFormula(sheet.FormulaProvider.Average($"$A$2:$A${lastValueRowIndex}"));
sheet[ExcelColumnIndex.K, 4].SetFormula(sheet.FormulaProvider.Var($"$A$2:$A${lastValueRowIndex}"));
sheet[ExcelColumnIndex.K, 5].SetFormula(sheet.FormulaProvider.StdDev($"$A$2:$A${lastValueRowIndex}"));
sheet[ExcelColumnIndex.K, 6].SetFormula(ManualFormulaProvider.VariationCoefficient("$K$3", "$K$5"));
sheet[ExcelColumnIndex.K, 7].SetFormula(sheet.FormulaProvider.RoundUp("3.8416 * $K$6^2 / $F$9^2", "0"));
sheet.AutoSizeColumn(ExcelColumnIndex.J);
sheet.AutoSizeColumn(ExcelColumnIndex.K, useMergedCells: true);
}
private static void FillBasicColumns(IExcelSheet sheet, ParametersPack args,
ref int currentColumnIndex)
{
int rowIndex = 1;
var sampleSizeColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[sampleSizeColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.SampleSizeColumnName);
var theoreticalMinColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[theoreticalMinColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.TheoreticalMinColumnName);
var theoreticalAverageColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[theoreticalAverageColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.TheoreticalAverageColumnName);
var theoreticalMaxColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[theoreticalMaxColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.TheoreticalMaxColumnName);
++rowIndex;
for (int launchesNumber = args.StartValue; launchesNumber <= args.ExtrapolationSegmentValue;
launchesNumber += args.Step)
{
sheet[sampleSizeColumnIndex, rowIndex].SetValue(launchesNumber);
string minFormula = ManualFormulaProvider.Min(
sheet, sampleSizeColumnIndex, rowIndex, args.AlgorithmType
);
sheet[theoreticalMinColumnIndex, rowIndex].SetFormula(minFormula);
string averageFormula = ManualFormulaProvider.Average(
sheet, sampleSizeColumnIndex, rowIndex, args.AlgorithmType
);
sheet[theoreticalAverageColumnIndex, rowIndex].SetFormula(averageFormula);
string maxFormula = ManualFormulaProvider.Max(
sheet, sampleSizeColumnIndex, rowIndex, args.AlgorithmType
);
sheet[theoreticalMaxColumnIndex, rowIndex].SetFormula(maxFormula);
++rowIndex;
}
sheet.AutoSizeColumn(sampleSizeColumnIndex);
sheet.AutoSizeColumn(theoreticalMinColumnIndex);
sheet.AutoSizeColumn(theoreticalAverageColumnIndex);
sheet.AutoSizeColumn(theoreticalMaxColumnIndex);
}
private static void FillAdditionalParametersColumns(IExcelSheet sheet, ParametersPack args)
{
// Descriptive cells.
sheet[ExcelColumnIndex.I, 1].SetValue(ExcelStringsPhaseOnePartTwo.AdditionalParametersColumnName);
sheet[ExcelColumnIndex.I, 2].SetValue(ExcelStringsPhaseOnePartTwo.InputDataSize);
sheet[ExcelColumnIndex.I, 3].SetValue(ExcelStringsPhaseOnePartTwo.MinFunc);
sheet[ExcelColumnIndex.I, 4].SetValue(ExcelStringsPhaseOnePartTwo.AverageFunc);
sheet[ExcelColumnIndex.I, 5].SetValue(ExcelStringsPhaseOnePartTwo.MaxFunc);
sheet[ExcelColumnIndex.I, 6].SetValue(ExcelStringsPhaseOnePartTwo.ExperimentsNumber);
sheet[ExcelColumnIndex.I, 7].SetValue(ExcelStringsPhaseOnePartTwo.ConfidenceFactor);
sheet[ExcelColumnIndex.I, 8].SetValue(ExcelStringsPhaseOnePartTwo.SignificanceLevel);
sheet[ExcelColumnIndex.I, 9].SetValue(ExcelStringsPhaseOnePartTwo.Epsilon);
sheet.AutoSizeColumn(ExcelColumnIndex.I);
// Value cells.
sheet[ExcelColumnIndex.J, 1].SetValue(ExcelStringsPhaseOnePartTwo.AdditionalParametersValuesColumnName);
sheet[ExcelColumnIndex.J, 2].SetValue(args.StartValue);
string minFormula = ManualFormulaProvider.Min(
sheet, ExcelColumnIndex.J, 2, args.AlgorithmType
);
sheet[ExcelColumnIndex.J, 3].SetFormula(minFormula);
string averageFormula = ManualFormulaProvider.Average(
sheet, ExcelColumnIndex.J, 2, args.AlgorithmType
);
sheet[ExcelColumnIndex.J, 4].SetFormula(averageFormula);
string maxFormula = ManualFormulaProvider.Max(
sheet, ExcelColumnIndex.J, 2, args.AlgorithmType
);
sheet[ExcelColumnIndex.J, 5].SetFormula(maxFormula);
sheet[ExcelColumnIndex.J, 6].SetValue(args.LaunchesNumber);
sheet[ExcelColumnIndex.J, 7].SetValue(double.Parse(ExcelStringsPhaseOnePartTwo.ConfidenceFactorValue));
string formulaJ8 = string.Format(
ExcelStringsPhaseOnePartTwo.SignificanceLevelFormula,
sheet[ExcelColumnIndex.J, 7].Address
);
sheet[ExcelColumnIndex.J, 8].SetFormula(formulaJ8);
sheet[ExcelColumnIndex.J, 9].SetValue(double.Parse(ExcelStringsPhaseOnePartTwo.EpsilonValue));
sheet.AutoSizeColumn(ExcelColumnIndex.J);
}
private static void FillAdditionalDataColumn(IExcelSheet sheet, ParametersPack args,
ref int currentColumnIndex)
{
var additionalDataColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[additionalDataColumnIndex, 1].SetValue(ExcelStringsPhaseTwo.AdditionalDataColumnName);
sheet[additionalDataColumnIndex, 2].SetValue(args.LaunchesNumber);
sheet[additionalDataColumnIndex, 3].SetValue(args.Step);
sheet[additionalDataColumnIndex, 4].SetValue(double.Parse(ExcelStringsPhaseTwo.ConfidenceFactorValue));
string significanceLevelFormula = string.Format(
ExcelStringsPhaseTwo.SignificanceLevelFormula,
sheet[additionalDataColumnIndex, 4].Address
);
sheet[additionalDataColumnIndex, 5].SetFormula(significanceLevelFormula);
// Provide help information to see which columns contain data.
string operationsRange = $"{GetFirstDataRowIndex().ToString()}:" +
$"{GetLastDataRowIndex(args).ToString()}";
sheet[additionalDataColumnIndex, 6].SetValue(operationsRange);
string normalizedRange = $"{GetFirstNormalizedDataRowIndex(args).ToString()}:" +
$"{GetLastNormalizedDataRowIndex(args).ToString()}";
sheet[additionalDataColumnIndex, 7].SetValue(normalizedRange);
sheet.AutoSizeColumn(additionalDataColumnIndex);
}
private static void FillLaunchesHeader(IExcelSheet sheet, ParametersPack args,
ref int currentColumnIndex)
{
for (int launchesNumber = args.StartValue; launchesNumber <= args.EndValue;
launchesNumber += args.Step)
{
var launchesColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[launchesColumnIndex, 1].SetValue(launchesNumber);
sheet.AutoSizeColumn(launchesColumnIndex);
}
}
public void ApplyAnalysisToDataset(IExcelSheet sheet)
{
sheet[ExcelColumnIndex.B, 1].SetValue(ExcelStringsPhaseOnePartTwo.NormalizedColumnName);
sheet[ExcelColumnIndex.L, 1].SetValue(ExcelStringsPhaseOnePartTwo.NormalDistributionSolutionColumnName);
sheet.GetOrCreateCenterizedCell(ExcelColumnIndex.M, 1);
sheet.AddMergedRegion(ExcelColumnIndex.L, 1, ExcelColumnIndex.M, 1);
sheet[ExcelColumnIndex.L, 2].SetValue(ExcelStringsPhaseOnePartTwo.SampleMean);
sheet[ExcelColumnIndex.L, 3].SetValue(ExcelStringsPhaseOnePartTwo.SampleVariance);
sheet[ExcelColumnIndex.L, 4].SetValue(ExcelStringsPhaseOnePartTwo.SampleDeviation);
sheet[ExcelColumnIndex.L, 5].SetValue(ExcelStringsPhaseOnePartTwo.VariationCoefficient);
sheet[ExcelColumnIndex.L, 6].SetValue(ExcelStringsPhaseOnePartTwo.TheoreticalMin);
sheet[ExcelColumnIndex.L, 7].SetValue(ExcelStringsPhaseOnePartTwo.TheoreticalMax);
sheet[ExcelColumnIndex.L, 8].SetValue(ExcelStringsPhaseOnePartTwo.Span);
sheet[ExcelColumnIndex.L, 9].SetValue(ExcelStringsPhaseOnePartTwo.NormalizedMean);
sheet[ExcelColumnIndex.L, 10].SetValue(ExcelStringsPhaseOnePartTwo.NormalizedVarience);
sheet[ExcelColumnIndex.L, 11].SetValue(ExcelStringsPhaseOnePartTwo.Alpha);
sheet[ExcelColumnIndex.L, 12].SetValue(ExcelStringsPhaseOnePartTwo.Beta);
string lastValueRowIndex = _args.LaunchesNumber.SkipHeader().ToString();
sheet[ExcelColumnIndex.M, 2].SetFormula(sheet.FormulaProvider.Average($"$A$2:$A${lastValueRowIndex}"));
sheet[ExcelColumnIndex.M, 3].SetFormula(sheet.FormulaProvider.Var($"$A$2:$A${lastValueRowIndex}"));
sheet[ExcelColumnIndex.M, 4].SetFormula(sheet.FormulaProvider.StdDev($"$A$2:$A${lastValueRowIndex}"));
sheet[ExcelColumnIndex.M, 5].SetFormula(ManualFormulaProvider.VariationCoefficient("$M$2", "$M$4"));
sheet[ExcelColumnIndex.M, 6].SetFormula("$J$3");
sheet[ExcelColumnIndex.M, 7].SetFormula("$J$5");
sheet[ExcelColumnIndex.M, 8].SetFormula(ManualFormulaProvider.Span("$M$6", "$M$7"));
sheet[ExcelColumnIndex.M, 9].SetFormula(sheet.FormulaProvider.Average($"$B$2:$B${lastValueRowIndex}"));
sheet[ExcelColumnIndex.M, 10].SetFormula(sheet.FormulaProvider.Var($"$B$2:$B${lastValueRowIndex}"));
sheet[ExcelColumnIndex.M, 11].SetFormula(ManualFormulaProvider.Alpha("$M$9", "$M$10"));
sheet[ExcelColumnIndex.M, 12].SetFormula(ManualFormulaProvider.Beta("$M$9", "$M$10"));
sheet.AutoSizeColumn(ExcelColumnIndex.B);
sheet.AutoSizeColumn(ExcelColumnIndex.L);
sheet.AutoSizeColumn(ExcelColumnIndex.M, useMergedCells: true);
_histogramBuilder.CreateHistogramData(sheet);
}
private static void FillOperationColumn(IExcelSheet sheet)
{
sheet[ExcelColumnIndex.A, 1].SetValue(ExcelStringsPhaseOnePartTwo.OperationColumnName);
sheet.AutoSizeColumn(ExcelColumnIndex.A);
}
public void CreateHistogramData(IExcelSheet sheet)
{
sheet[ExcelColumnIndex.D, 1].SetValue(ExcelStringsPhaseOnePartTwo.PocketColumnName);
sheet[ExcelColumnIndex.E, 1].SetValue(ExcelStringsPhaseOnePartTwo.EmpiricalFrequencyColumnName);
sheet[ExcelColumnIndex.F, 1].SetValue(ExcelStringsPhaseOnePartTwo.RelativeFrequencyColumnName);
sheet[ExcelColumnIndex.G, 1].SetValue(ExcelStringsPhaseOnePartTwo.TheoreticalFrequencyColumnName);
sheet[ExcelColumnIndex.H, 1].SetValue(ExcelStringsPhaseOnePartTwo.Chi2ValueColumnName);
sheet[ExcelColumnIndex.I, 10].SetValue(ExcelStringsPhaseOnePartTwo.MinimumValue);
sheet[ExcelColumnIndex.I, 11].SetValue(ExcelStringsPhaseOnePartTwo.MaximumValue);
sheet[ExcelColumnIndex.I, 12].SetValue(ExcelStringsPhaseOnePartTwo.IntervalLength);
sheet[ExcelColumnIndex.I, 13].SetValue(ExcelStringsPhaseOnePartTwo.HistogramSemisegmentsNumber);
sheet[ExcelColumnIndex.I, 14].SetValue(ExcelStringsPhaseOnePartTwo.Chi2Observable);
sheet[ExcelColumnIndex.I, 15].SetValue(ExcelStringsPhaseOnePartTwo.FreedomDegreesNumber);
sheet[ExcelColumnIndex.I, 16].SetValue(ExcelStringsPhaseOnePartTwo.Chi2Critical);
sheet[ExcelColumnIndex.I, 17].SetValue(ExcelStringsPhaseOnePartTwo.CheckTestFucntion);
string lastValueRowIndex = _args.LaunchesNumber.SkipHeader().ToString();
string normalizedValueRange = $"$B$2:$B${lastValueRowIndex}";
sheet[ExcelColumnIndex.J, 10].SetFormula(sheet.FormulaProvider.Min(normalizedValueRange));
sheet[ExcelColumnIndex.J, 11].SetFormula(sheet.FormulaProvider.Max(normalizedValueRange));
string scottFormula = ManualFormulaProvider.ScottFormula(
sheet.FormulaProvider, normalizedValueRange, "$J$6"
);
sheet[ExcelColumnIndex.J, 12].SetFormula(scottFormula);
sheet[ExcelColumnIndex.J, 13].SetFormula(
sheet.FormulaProvider.RoundUp("($J$11 - $J$10) / $J$12", "0")
);
IExcelCellValueHolder histogramSegmentsNumber = sheet.EvaluateCell(ExcelColumnIndex.J, 13);
int histogramSegmentsNumberInt = Convert.ToInt32(histogramSegmentsNumber.NumericValue);
string histogramSegmentsNumberIndex =
histogramSegmentsNumberInt.UseOneBasedIndexing().SkipHeader().ToString();
CreateIntervalData(sheet, histogramSegmentsNumberInt, normalizedValueRange);
string chi2Formula = ManualFormulaProvider.Chi2(
sheet.FormulaProvider, $"$H$2:$H${histogramSegmentsNumberIndex}", "$J$6"
);
sheet[ExcelColumnIndex.J, 14].SetFormula(chi2Formula);
sheet[ExcelColumnIndex.J, 15].SetFormula("$J$13 - 1 - 2");
sheet[ExcelColumnIndex.J, 16].SetFormula(sheet.FormulaProvider.ChiInv("$J$8", "$J$15"));
string testFormula = sheet.FormulaProvider.ChiTest(
$"$F$2:$F${histogramSegmentsNumberIndex}",
$"$G$2:$G${histogramSegmentsNumberIndex}"
);
sheet[ExcelColumnIndex.J, 17].SetFormula(testFormula);
sheet.AutoSizeColumn(ExcelColumnIndex.D);
sheet.AutoSizeColumn(ExcelColumnIndex.E);
sheet.AutoSizeColumn(ExcelColumnIndex.F);
sheet.AutoSizeColumn(ExcelColumnIndex.G);
sheet.AutoSizeColumn(ExcelColumnIndex.H);
sheet.AutoSizeColumn(ExcelColumnIndex.I);
sheet.AutoSizeColumn(ExcelColumnIndex.J);
}
private void FillAnalysisColumns(IExcelSheet sheet, ref int currentColumnIndex)
{
int rowIndex = 1;
var nColumnColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[nColumnColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.NColumnName);
var alphaNColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[alphaNColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.AlphaNColumnName);
var betaNColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[betaNColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.BetaNColumnName);
var analysisValuesColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[analysisValuesColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.AnalysisValuesColumnName);
// Remain blank column.
++currentColumnIndex;
var leftYQuantileColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[leftYQuantileColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.LeftYQuantileColumnName);
var complexityFunctionColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[complexityFunctionColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.ComplexityFunctionColumnName);
var comparisonColumnIndex = currentColumnIndex.AsEnum <ExcelColumnIndex>();
sheet[comparisonColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.ComparisonColumnName);
IExcelCellHolder confidenceFactorCell = GetConfidenceFactorCell(sheet);
IModelledFunction alphaOptimalFunction = GetOptimalFunction(sheet, _alphaColumn);
string formulaForAlphaFunction = ManualFormulaProvider.GetFormulaForFunction(
sheet.FormulaProvider, alphaOptimalFunction.Type
);
IModelledFunction betaOptimalFunction = GetOptimalFunction(sheet, _betaColumn);
string formulaForBetaFunction = ManualFormulaProvider.GetFormulaForFunction(
sheet.FormulaProvider, betaOptimalFunction.Type
);
++rowIndex;
for (int launchesNumber = _args.StartValue; launchesNumber <= _args.ExtrapolationSegmentValue;
launchesNumber += _args.Step)
{
IExcelCellHolder nColumnCell = sheet[nColumnColumnIndex, rowIndex];
nColumnCell.SetValue(launchesNumber);
string alphaNFormula = alphaOptimalFunction.ToFormulaString(
nColumnCell.Address, formulaForAlphaFunction
);
IExcelCellHolder alphaNCell = sheet[alphaNColumnIndex, rowIndex];
alphaNCell.SetFormula(alphaNFormula);
string betaNFormula = betaOptimalFunction.ToFormulaString(
nColumnCell.Address, formulaForBetaFunction
);
IExcelCellHolder betaNCell = sheet[betaNColumnIndex, rowIndex];
betaNCell.SetFormula(betaNFormula);
string leftYQuantileFormula = sheet.FormulaProvider.BetaInv(
confidenceFactorCell.Address, alphaNCell.Address, betaNCell.Address
);
IExcelCellHolder leftYQuantileCell = sheet[leftYQuantileColumnIndex, rowIndex];
leftYQuantileCell.SetFormula(leftYQuantileFormula);
string theoreticalMinAddress = sheet[_theoreticalMinColumn, rowIndex].Address;
string theoreticalMaxAddress = sheet[_theoreticalMaxColumn, rowIndex].Address;
string complexityFunctionFormula = ManualFormulaProvider.ConfidenceComplexity(
leftYQuantileCell.Address, theoreticalMinAddress, theoreticalMaxAddress
);
IExcelCellHolder complexityCell = sheet[complexityFunctionColumnIndex, rowIndex];
complexityCell.SetFormula(complexityFunctionFormula);
string comparisonFormula = $"{theoreticalMaxAddress} / {complexityCell.Address}";
sheet[comparisonColumnIndex, rowIndex].SetFormula(comparisonFormula);
++rowIndex;
}
string alphaPearsonFormula = sheet.FormulaProvider.Pearson(
$"{sheet[_alphaColumn, 2].Address}:" +
$"{sheet[_alphaColumn, _lastSegmentValueRowIndex].Address}",
$"{sheet[alphaNColumnIndex, 2].Address}:" +
$"{sheet[alphaNColumnIndex, _lastSegmentValueRowIndex].Address}"
);
sheet[analysisValuesColumnIndex, 2].SetFormula(alphaPearsonFormula);
string betaPearsonFormula = sheet.FormulaProvider.Pearson(
$"{sheet[_betaColumn, 2].Address}:" +
$"{sheet[_betaColumn, _lastSegmentValueRowIndex].Address}",
$"{sheet[betaNColumnIndex, 2].Address}:" +
$"{sheet[betaNColumnIndex, _lastSegmentValueRowIndex].Address}"
);
sheet[analysisValuesColumnIndex, 3].SetFormula(betaPearsonFormula);
sheet.AutoSizeColumn(nColumnColumnIndex);
sheet.AutoSizeColumn(alphaNColumnIndex);
sheet.AutoSizeColumn(betaNColumnIndex);
sheet.AutoSizeColumn(analysisValuesColumnIndex);
sheet.AutoSizeColumn(leftYQuantileColumnIndex);
sheet.AutoSizeColumn(complexityFunctionColumnIndex);
sheet.AutoSizeColumn(comparisonColumnIndex);
}
private void FillStatisticalColumns(IExcelSheet sheet, ref int currentColumnIndex)
{
int rowIndex = 1;
var sampleMeanColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[sampleMeanColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.SampleMeanColumnName);
var sampleVarianceColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[sampleVarianceColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.SampleVarianceColumnName);
var sampleDeviationColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[sampleDeviationColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.SampleDeviationColumnName);
var normalizedMeanColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[normalizedMeanColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.NormalizedMeanColumnName);
var normalizedVarienceColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[normalizedVarienceColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.NormalizedVarienceColumnName);
var alphaColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[alphaColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.AlphaColumnName);
var betaColumnIndex = currentColumnIndex++.AsEnum <ExcelColumnIndex>();
sheet[betaColumnIndex, rowIndex].SetValue(ExcelStringsPhaseTwo.BetaColumnName);
++rowIndex;
int launchesColumnIndex = 0;
int firstRowIndex = ExcelWrapperForPhaseTwo.GetFirstDataRowIndex();
int lastRowIndex = ExcelWrapperForPhaseTwo.GetLastDataRowIndex(_args);
int firstNormalizedRowIndex = ExcelWrapperForPhaseTwo.GetFirstNormalizedDataRowIndex(_args);
int lastNormalizedRowIndex = ExcelWrapperForPhaseTwo.GetLastNormalizedDataRowIndex(_args);
for (int launchesNumber = _args.StartValue; launchesNumber <= _args.EndValue;
launchesNumber += _args.Step)
{
var launchesColumnIndexEnum = launchesColumnIndex++.AsEnum <ExcelColumnIndex>();
string dataRange =
$"{sheet[launchesColumnIndexEnum, firstRowIndex].Address}:" +
$"{sheet[launchesColumnIndexEnum, lastRowIndex].Address}";
string sampleMeanFormula = sheet.FormulaProvider.Average(dataRange);
sheet[sampleMeanColumnIndex, rowIndex].SetFormula(sampleMeanFormula);
string sampleVarienceFormula = sheet.FormulaProvider.Var(dataRange);
sheet[sampleVarianceColumnIndex, rowIndex].SetFormula(sampleVarienceFormula);
string sampleDeviationFormula = sheet.FormulaProvider.StdDev(dataRange);
sheet[sampleDeviationColumnIndex, rowIndex].SetFormula(sampleDeviationFormula);
string normalizedDataRange =
$"{sheet[launchesColumnIndexEnum, firstNormalizedRowIndex].Address}:" +
$"{sheet[launchesColumnIndexEnum, lastNormalizedRowIndex].Address}";
string normalizedMeanFormula = sheet.FormulaProvider.Average(normalizedDataRange);
sheet[normalizedMeanColumnIndex, rowIndex].SetFormula(normalizedMeanFormula);
string normalizedVarienceFormula = sheet.FormulaProvider.Var(normalizedDataRange);
sheet[normalizedVarienceColumnIndex, rowIndex].SetFormula(normalizedVarienceFormula);
string normalizedMeanAddress = sheet[normalizedMeanColumnIndex, rowIndex].Address;
string normalizedVarienceAddress = sheet[normalizedVarienceColumnIndex, rowIndex].Address;
string alphaFormula = ManualFormulaProvider.Alpha(
normalizedMeanAddress, normalizedVarienceAddress
);
sheet[alphaColumnIndex, rowIndex].SetFormula(alphaFormula);
string betaFormula = ManualFormulaProvider.Beta(
normalizedMeanAddress, normalizedVarienceAddress
);
sheet[betaColumnIndex, rowIndex].SetFormula(betaFormula);
++rowIndex;
}
sheet.AutoSizeColumn(sampleMeanColumnIndex);
sheet.AutoSizeColumn(sampleVarianceColumnIndex);
sheet.AutoSizeColumn(sampleDeviationColumnIndex);
sheet.AutoSizeColumn(normalizedMeanColumnIndex);
sheet.AutoSizeColumn(normalizedVarienceColumnIndex);
sheet.AutoSizeColumn(alphaColumnIndex);
sheet.AutoSizeColumn(betaColumnIndex);
}
