private void addDataSetOfCurrentSelection()
        {
            _Worksheet workSheet = Globals.ExcelAddIn.getActiveWorksheet();
            Range      range     = Globals.ExcelAddIn.getCurrentSelectionRange();

            if (model.hasIntersectionWith(range))
            {
                if (DataSetManagerForm.ignoreIntersection(model.getFirstIntersectingDataSetWith(range)))
                {
                    return;
                }
            }

            if (range.Rows.Count == 1 && range.Columns.Count == 1)
            {
                range = Globals.ExcelAddIn.getExpandedCurrentRange();
                range.Select();
            }

            bool addNewDataSet = (range.Rows.Count != 1 || range.Columns.Count != 1) && DataSetManagerForm.addNewDataSet(range);

            if (!addNewDataSet)
            {
                return;
            }
            DataSet newDataSet = DataSetFactory.create(workSheet, range, "Data Set " + (model.numberOfDataSets() + 1), COLUMNS, true);

            model.addDataSet(newDataSet);
            view.selectDataSet(newDataSet);
        }
        public void addNewDataSet()
        {
            DataSet newDataSet = DataSetFactory.create(Globals.ExcelAddIn.getActiveWorksheet(), null, "Data Set " + (model.numberOfDataSets() + 1), COLUMNS, true);

            model.addDataSet(newDataSet);
            view.selectDataSet(newDataSet);
        }
Exemple #3
0
        private void PrintCategories2(_Worksheet _sheet, int rowIn, DataSet dataSet)
        {
            double alpha   = (100.0 - model.confidence) / 200.0;
            double norm    = _sheet.Application.WorksheetFunction.NormSInv(alpha);
            var    pValue  = (_sheet.Cells[3, 2] as Range).Value;
            var    pValue2 = (_sheet.Cells[3, 3] as Range).Value;
            double p       = 1.0 - Convert.ToDouble(pValue);
            double p2      = 1.0 - Convert.ToDouble(pValue2);
            double n       = Convert.ToDouble((_sheet.Cells[2, 2] as Range).Value);
            double n2      = Convert.ToDouble((_sheet.Cells[2, 3] as Range).Value);
            double sqrt    = Math.Sqrt((p * (1.0 - p) / n) + (p2 * (1.0 - p2) / n2));
            var    row     = rowIn;

            _sheet.Cells[row, 1]   = "Confidence level";
            _sheet.Cells[row++, 2] = model.confidence.ToString();

            _sheet.Cells[row, 1] = "Lower limit";
            double tmp = p - p2 - Math.Abs((norm * sqrt));

            _sheet.Cells[row++, 2] = tmp;

            _sheet.Cells[row, 1] = "Upper limit";
            tmp = p - p2 + Math.Abs((norm * sqrt));
            _sheet.Cells[row++, 2] = tmp;
        }
        public double[,] calcXdata(int length, DataSet dataSet, List <Variable> variablesI)
        {
            System.Diagnostics.Debug.WriteLine("lengte xdata is = {0}", length);
            int count = 0;

            double[,] xData = new double[length, variablesI.Count + 1];
            //bereken (Xi-Xgem)
            while (count < length)
            {
                xData[count, 0] = 1;
                count++;
            }
            count = 0;
            foreach (Variable var in variablesI)
            {
                int      i    = 0;
                string   ran  = variablesI[count].Range.ToString();
                Array    arr  = dataSet.getWorksheet().Range[ran].Value;
                double[] vals = new double[length];
                foreach (var item in arr)
                {
                    if (item.GetType() == typeof(string))
                    {
                        MessageBox.Show("Please use numeric data", "Invalid data type error");
                        break;
                    }
                    double temp = Convert.ToDouble(item);
                    xData[i, count + 1] = temp;
                    //xData[count,i] = (temp - meansI[count]);
                    i++;
                }
                count++;
            }
            return(xData);
        }
Exemple #5
0
        public void createConfidence(List <Variable> variables, DataSet dataSet)
        {
            _Worksheet worksheet = WorksheetHelper.NewWorksheet("Confidence");

            if (variables.Count == 1)
            {
                foreach (Variable variable in variables)
                {
                    worksheet.Cells[1, 1] = "Conf. intervals (Proportion)";
                    worksheet.Cells[1, 2] = variable.name;
                    worksheet.Cells[2, 1] = "Sample size";
                    worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variable.Range + ")";
                    worksheet.Cells[3, 1] = "Sample proportion";
                    worksheet.Cells[3, 2] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variable.Range + "," + 0 + ") / " + (worksheet.Cells[2, 2] as Range).Value;
                    PrintCategories(worksheet, 4, dataSet);
                }
            }
            else if (variables.Count == 2)
            {
                worksheet.Cells[1, 1] = "Conf. intervals (Proportion)";
                worksheet.Cells[1, 2] = variables[0].name;
                worksheet.Cells[1, 3] = variables[1].name;
                worksheet.Cells[2, 1] = "Sample size";
                worksheet.Cells[2, 2] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")";
                worksheet.Cells[2, 3] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + ")";
                worksheet.Cells[3, 1] = "Sample proportion";
                worksheet.Cells[3, 2] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + "," + 0 + ") / " + (worksheet.Cells[2, 2] as Range).Value;
                worksheet.Cells[3, 3] = "=COUNTIF(" + dataSet.getWorksheet().Name + "!" + variables[1].Range + "," + 0 + ") / " + (worksheet.Cells[2, 3] as Range).Value;
                PrintCategories2(worksheet, 4, dataSet);
            }
        }
        private void PrintCategories(_Worksheet _sheet, int rowIn, String input, DataSet dataSet)
        {
            double alpha = (double)model.alpha;
            int    tail  = 1;

            if (input.Equals("equal"))
            {
                alpha = alpha / 200.0;
                tail  = 2;
            }
            else
            {
                alpha = alpha / 100.0;
                tail  = 1;
            }
            double mean = (_sheet.Cells[3, 2] as Range).Value;
            double s    = (_sheet.Cells[4, 2] as Range).Value;
            double n    = (_sheet.Cells[2, 2] as Range).Value;
            double den  = (s / Math.Sqrt(n));
            double mu   = Convert.ToDouble(model.Null);
            double nom  = mean - mu;
            double t    = nom / den;
            var    row  = rowIn;

            _sheet.Cells[row, 1]   = "Hypothesized mean";
            _sheet.Cells[row++, 2] = model.Null;

            _sheet.Cells[row, 1] = "Alternative Hypothesis";
            if (input.Equals("equal"))
            {
                _sheet.Cells[row++, 2] = "'=/= " + model.Null;
            }
            else
            {
                _sheet.Cells[row++, 2] = "> " + model.Null;
            }
            _sheet.Cells[row, 1]   = "Alpha";
            _sheet.Cells[row++, 2] = (double)model.alpha / 100.0;

            _sheet.Cells[row, 1]   = "t-Test Statistic";
            _sheet.Cells[row++, 2] = t;

            double p = _sheet.Application.WorksheetFunction.TDist(Math.Abs(t), n - 1, tail);

            _sheet.Cells[row, 1]   = "p-Value";
            _sheet.Cells[row++, 2] = p;

            _sheet.Cells[row, 1] = "Null Hypothesis";
            if (p <= alpha)
            {
                _sheet.Cells[row, 2] = "Reject";
            }
            else
            {
                _sheet.Cells[row, 2] = "Accept";
            }
        }
        public void setVariableNamesInFirstRowOrColumn(DataSet dataSet, bool variableNamesInFirstRowOrColumn)
        {
            if (dataSet == null)
            {
                return;
            }
            DataSet newDataSet = DataSetFactory.create(dataSet.getWorksheet(), dataSet.getRange(), dataSet.getName(), dataSet.getRangeLayout(),
                                                       variableNamesInFirstRowOrColumn);

            model.swapDataSets(dataSet, newDataSet);
        }
Exemple #8
0
 public bool checkInput(DataSet dataSet)
 {
     if (dataSet != null)
     {
         createUnstacked(dataSet);
         return(true);
     }
     else
     {
         MessageBox.Show("Please correct all fields to generate interaction", "Interaction error");
     }
     return(false);
 }
Exemple #9
0
 public bool checkInput(DataSet dataSet, List <Variable> variables, bool rdbMovingAverage, bool rdbSimpleExponentialSmoothing, bool rdbHoltsExponentialSmoothing, bool rdbWintersExponentialSmoothing, bool doOptimizeParameters, int numberOfForecasts, int numberOfHoldouts, int seasonalPeriod, int span, string level, string trend, string seasonality)
 {
     if (variables.Count != 0 && double.TryParse(seasonality, out _seasonality) && double.TryParse(trend, out _trend) && double.TryParse(level, out _level))
     {
         Print(dataSet, variables, rdbMovingAverage, rdbSimpleExponentialSmoothing, rdbHoltsExponentialSmoothing, rdbWintersExponentialSmoothing, doOptimizeParameters, numberOfForecasts, numberOfHoldouts, seasonalPeriod, span, level, trend, seasonality);
         return(true);
     }
     else
     {
         MessageBox.Show("Please correct all fields to generate Forecast");
         return(false);
     }
 }
 public bool checkInput(DataSet dataSet)
 {
     if (dataSet != null)
     {
         createUnstacked(dataSet);
         return(true);
     }
     else
     {
         MessageBox.Show("Please correct all fields to calculate unstacked", "Unstacked error");
     }
     return(false);
 }
 public bool checkInput(List <Variable> variablesX, DataSet dataSet)
 {
     if (dataSet != null)
     {
         _Worksheet sheet = WorksheetHelper.NewWorksheet("Time Series Graph");
         generateChart(variablesX, sheet, dataSet);
         return(true);
     }
     else
     {
         MessageBox.Show("Please correct all fields to generate Time Series Graph", "Time Series Graph error");
     }
     return(false);
 }
Exemple #12
0
        private double[] CalculateSumOfSquares(DataSet dataSet, List <Variable> valueArrayX, Variable valueY, Matrix <double> beta)
        {
            var sumOfSquare = new double[2];

            for (var i = 0; i < dataSet.rangeSize(); i++)
            {
                var fittedValue = beta[0, 0];
                for (var j = 0; j < valueArrayX.Count; j++)
                {
                    fittedValue = fittedValue + beta[j + 1, 0] * dataSet.getValuesArray(valueArrayX[j])[i];
                }
                sumOfSquare[0] = sumOfSquare[0] + (fittedValue - _functions.Average(valueY.getRange())) * (fittedValue - _functions.Average(valueY.getRange()));
                sumOfSquare[1] = sumOfSquare[1] + (dataSet.getValuesArray(valueY)[i] - fittedValue) * (dataSet.getValuesArray(valueY)[i] - fittedValue);
            }
            return(sumOfSquare);
        }
        public bool checkInput(List <Variable> variablesX, DataSet dataSet)
        {
            string ran = variablesX[0].Range.ToString();
            Array  arr = dataSet.getWorksheet().Range[ran].Value;

            if (dataSet != null)
            {
                _Worksheet sheet = WorksheetHelper.NewWorksheet("Autocorrelation");
                generateChart(arr, variablesX, sheet, dataSet);
                return(true);
            }
            else
            {
                MessageBox.Show("Please correct all fields to generate Autocorrelation", "Autocorrelation error");
            }
            return(false);
        }
Exemple #14
0
        public bool checkInput(bool rdbAllObservations, bool rdbObservationsInRange, DataSet dataSet, string uiTextBox_StopIndex, string uiTextBox_StartIndex, bool rdbPlotAllObservations, bool rdbPlotObservationsInRange, string uiTextBox_PlotStopIndex, string uiTextBox_PlotStartIndex)
        {
            int startindex = Convert.ToInt16(uiTextBox_StartIndex);
            int stopindex  = Convert.ToInt16(uiTextBox_StopIndex);

            int plotstartindex = Convert.ToInt16(uiTextBox_PlotStartIndex);
            int plotstopindex  = Convert.ToInt16(uiTextBox_PlotStopIndex);

            if ((rdbAllObservations || rdbObservationsInRange) && (rdbPlotAllObservations || rdbPlotObservationsInRange) && (dataSet != null) && (startindex <= stopindex) && (startindex >= 0 && stopindex >= 0) && (plotstartindex <= plotstopindex) && (plotstartindex >= 0 && plotstopindex >= 0))
            {
                offset = 320;

                if (rdbAllObservations)
                {
                    startindex = 0;
                    stopindex  = dataSet.amountOfVariables() - 2;
                }

                if (rdbObservationsInRange && stopindex >= dataSet.amountOfVariables())
                {
                    stopindex = dataSet.amountOfVariables() - 2;
                }

                if (rdbPlotAllObservations)
                {
                    plotstartindex = 0;
                    plotstopindex  = dataSet.amountOfVariables() - 2;
                }

                if (rdbPlotObservationsInRange && plotstopindex >= dataSet.amountOfVariables())
                {
                    plotstopindex = dataSet.amountOfVariables() - 2;
                }

                _Worksheet sheet = WorksheetHelper.NewWorksheet("XR Chart");

                generateXRChart(startindex, stopindex, plotstartindex, plotstopindex, dataSet, offset, sheet);

                return(true);
            }
            else
            {
                MessageBox.Show("Please correct all fields to generate X/R-Chart", "XR-Chart error");
            }
            return(false);
        }
        public void createHistograms(List <Variable> variables, DataSet dataSet)
        {
            _Worksheet worksheet = WorksheetHelper.NewWorksheet("Histogram");
            int        row       = 1;
            int        counter   = 0;

            // Create the Histogram.
            foreach (Variable variable in variables)
            {
                PrintCategories(worksheet, row++, 1);
                PrintVariables(worksheet, row++, counter++, variable, dataSet);

                if (row < 15 * counter)
                {
                    row = 15 * counter;
                }
            }
        }
Exemple #16
0
        private double CalculateStdErrorEstimation(DataSet dataSet, List <Variable> valueArrayX, Variable valueY, Matrix <double> beta)
        {
            double n     = dataSet.rangeSize();
            double ssRes = 0;

            for (var i = 0; i < n; i++)
            {
                var fittedValue = beta[0, 0];
                for (var j = 0; j < valueArrayX.Count; j++)
                {
                    fittedValue = fittedValue + beta[j + 1, 0] * dataSet.getValuesArray(valueArrayX[j])[i];
                }
                ssRes = ssRes + (dataSet.getValuesArray(valueY)[i] - fittedValue) * (dataSet.getValuesArray(valueY)[i] - fittedValue);
            }
            var stdErrorEstimation = Math.Sqrt(ssRes / (n - 1 - valueArrayX.Count));

            return(stdErrorEstimation);
        }
Exemple #17
0
        private double CalculateRsquare(DataSet dataSet, List <Variable> valueArrayX, Variable valueY, Matrix <double> beta)
        {
            double ssTot = 0;
            double ssRes = 0;

            for (var i = 0; i < dataSet.rangeSize(); i++)
            {
                ssTot = ssTot + (dataSet.getValuesArray(valueY)[i] - _functions.Average(valueY.getRange())) * (dataSet.getValuesArray(valueY)[i] - _functions.Average(valueY.getRange()));
                var fittedValue = beta[0, 0];
                for (var j = 0; j < valueArrayX.Count; j++)
                {
                    fittedValue = fittedValue + beta[j + 1, 0] * dataSet.getValuesArray(valueArrayX[j])[i];
                }
                ssRes = ssRes + (dataSet.getValuesArray(valueY)[i] - fittedValue) * (dataSet.getValuesArray(valueY)[i] - fittedValue);
            }
            var rSquare = 1 - ssRes / ssTot;

            return(rSquare);
        }
Exemple #18
0
        private void PrintCategories(_Worksheet _sheet, int rowIn, String input, DataSet dataSet)
        {
            double alpha    = (100.0 - model.mean) / 200.0;
            double norm     = _sheet.Application.WorksheetFunction.NormSInv(alpha);
            var    stdValue = (_sheet.Cells[4, 2] as Range).Value;
            double std      = Convert.ToDouble(stdValue);
            double sqrt     = Math.Sqrt(Convert.ToDouble((_sheet.Cells[2, 2] as Range).Value));
            double avr      = Convert.ToDouble((_sheet.Cells[3, 2] as Range).Value);
            var    row      = rowIn;

            _sheet.Cells[row, 1] = "Confidence level (" + input + ")";
            if (input.Equals("mean"))
            {
                _sheet.Cells[row++, 2] = model.mean.ToString();
            }
            else
            {
                _sheet.Cells[row++, 2] = model.std.ToString();
            }
            _sheet.Cells[row, 1] = "Lower limit";
            if (input.Equals("mean"))
            {
                double tmp = avr - Math.Abs((norm * std / sqrt));
                _sheet.Cells[row++, 2] = tmp;
            }
            else
            {
                double tmp = std - Math.Abs((norm * std / sqrt));
                _sheet.Cells[row++, 2] = tmp;
            }

            _sheet.Cells[row, 1] = "Upper limit";
            if (input.Equals("mean"))
            {
                double tmp = avr + Math.Abs((norm * std / sqrt));
                _sheet.Cells[row++, 2] = tmp;
            }
            else
            {
                double tmp = std + Math.Abs((norm * std / sqrt));
                _sheet.Cells[row++, 2] = tmp;
            }
        }
Exemple #19
0
        public bool checkInput(List <Variable> variables, DataSet dataSet, string uiTextBox_LowerLimit, string uiTextBox_UpperLimit)
        {
            double LSL = Convert.ToDouble(uiTextBox_LowerLimit);
            double USL = Convert.ToDouble(uiTextBox_UpperLimit);

            if ((dataSet != null) && (LSL < USL))
            {
                _Worksheet sheet = WorksheetHelper.NewWorksheet("Process Capability");

                generateProcessCapability(variables, LSL, USL, dataSet, sheet);

                return(true);
            }
            else
            {
                MessageBox.Show("Please correct all fields to generate X/R-Chart, LSL should be smaller than USL", "XR-Chart error");
            }
            return(false);
        }
Exemple #20
0
        public bool checkInput(DataSet dataSet, string uiTextBox_LowerLimit, string uiTextBox_UpperLimit)
        {
            int LSL = Convert.ToInt16(uiTextBox_LowerLimit);
            int USL = Convert.ToInt16(uiTextBox_UpperLimit);

            if ((dataSet != null) && (LSL < USL))
            {
                _Worksheet sheet = WorksheetHelper.NewWorksheet("Process Capability");

                generateProcessCapability(LSL, USL, dataSet, sheet);

                return(true);
            }
            else
            {
                MessageBox.Show("Please correct all fields to generate X/R-Chart", "XR-Chart error");
            }
            return(false);
        }
        private int calculateRuns(_Worksheet sheet, DataSet dataSet, Range range, double cutoff)
        {
            int runs = 1;

            double[,] array = RangeHelper.To2DDoubleArray(range);
            double[] array2 = new double[array.Length];
            for (int i = 0; i < array.Length; i++)
            {
                array2[i] = array[i, 0];
            }
            for (int i = 0; i < array.Length - 1; i++)
            {
                if (Math.Sign(array2[i] - cutoff) != Math.Sign(array2[i + 1] - cutoff))
                {
                    runs++;
                }
            }

            return(runs);
        }
        public double[] calcYdata(List <Variable> variablesD, DataSet dataSet, int length)
        {
            //bereken (Yi-Ygem)
            int count = 0;

            double[] yData = new double[length];
            foreach (Variable var in variablesD)
            {
                string   ran  = variablesD[0].Range.ToString();
                Array    arr  = dataSet.getWorksheet().Range[ran].Value;
                double[] vals = new double[length];
                foreach (var item in arr)
                {
                    double temp = Convert.ToDouble(item);
                    yData[count] = temp;
                    //temp2[count] = (temp - meansD);
                    count++;
                }
            }
            return(yData);
        }
        public void generateChart(List <Variable> variablesX, _Worksheet sheet, DataSet dataSet)
        {
            int offsetY = 0;

            foreach (Variable variableX in variablesX)
            {
                int offsetX     = 0;
                var charts      = (ChartObjects)sheet.ChartObjects();
                var chartObject = charts.Add(offsetX * 450, offsetY * 250, 450, 250);
                var chart       = chartObject.Chart;
                chart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
                chart.ChartWizard(Title: "Time Series " + dataSet.getName() + " - " + variableX.name, HasLegend: false);
                var seriesCollection = (SeriesCollection)chart.SeriesCollection();
                var series           = seriesCollection.Add();
                series.Values      = variableX.getRange();
                series.MarkerStyle = XlMarkerStyle.xlMarkerStyleCircle;

                offsetX++;

                offsetY++;
            }
        }
 public void deleteDataSet(DataSet dataSet)
 {
     model.removeDataSet(dataSet);
 }
Exemple #25
0
        public void generateProcessCapability(int LSL, int USL, DataSet dataSet, _Worksheet sheet)
        {
            int    index = 0;
            int    row = 1;
            int    column = 1;
            double correctionFactor1, correctionFactor2;

            double[] averages                = new double[dataSet.amountOfVariables()];
            double[] Rvalues                 = new double[dataSet.amountOfVariables()];
            double[] averageOfAverages       = new double[dataSet.amountOfVariables()];
            double[] xChartUpperControlLimit = new double[dataSet.amountOfVariables()];
            double[] xChartLowerControlLimit = new double[dataSet.amountOfVariables()];
            double[] rChartUpperControlLimit = new double[dataSet.amountOfVariables()];
            double[] rChartLowerControlLimit = new double[dataSet.amountOfVariables()];
            double[] averageOfRvalues        = new double[dataSet.amountOfVariables()];
            int[]    ArrayIndex              = new int[dataSet.amountOfVariables()];
            double[] constantC4              = new double[25] {
                0.0, 0.7979, 0.8862, 0.9213, 0.9400, 0.9515, 0.9594, 0.9650, 0.9693, 0.9727, 0.9754, 0.9776, 0.9794, 0.9810, 0.9823, 0.9835, 0.9845, 0.9854, 0.9862, 0.9869, 0.9876, 0.9882, 0.9887, 0.9892, 0.9896
            };
            double[] constantD2 = new double[25] {
                0.0, 1.128, 1.693, 2.059, 2.326, 2.534, 2.704, 2.847, 2.970, 3.078, 3.173, 3.258, 3.336, 3.407, 3.472, 3.532, 3.588, 3.640, 3.689, 3.735, 3.778, 3.819, 3.858, 3.895, 3.931
            };
            sheet.Cells[row, column]     = "Index";
            sheet.Cells[row, column + 1] = "Observation";
            sheet.Cells[row, column + 2] = "Average";
            sheet.Cells[row, column + 3] = "Max";
            sheet.Cells[row, column + 4] = "Min";
            sheet.Cells[row, column + 5] = "R";

            for (index = 1; index < dataSet.amountOfVariables(); index++)
            {
                row++;
                sheet.Cells[row, column]     = index - 1;
                sheet.Cells[row, column + 1] = dataSet.getVariables()[index].name;
                sheet.Cells[row, column + 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")";
                sheet.Cells[row, column + 3] = "=MAX(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")";
                sheet.Cells[row, column + 4] = "=MIN(" + dataSet.getWorksheet().Name + "!" + dataSet.getVariables()[index].Range + ")";
                sheet.Cells[row, column + 5] = (double)(sheet.Cells[row, column + 3] as Range).Value - (double)(sheet.Cells[row, column + 4] as Range).Value;
                ArrayIndex[index - 1]        = index;
                var cellValue = (double)(sheet.Cells[row, column + 2] as Range).Value;
                if (cellValue < -214682680)
                {
                    cellValue = 0;                         // if cellValue is the result of a division by 0, set value to 0
                }
                averages[index - 1] = cellValue;
                cellValue           = (double)(sheet.Cells[row, column + 5] as Range).Value;
                Rvalues[index - 1]  = cellValue;
            }

            // create variable to limit amount of subsamples in 1 measurement to 25
            int safe;

            if (dataSet.rangeSize() > 24)
            {
                safe = 24;
            }
            else
            {
                safe = dataSet.rangeSize();
            }

            if (dataSet.getVariableNamesInFirstRowOrColumn())
            {
                correctionFactor1 = constantC4[safe - 1];
                correctionFactor2 = constantD2[safe - 1];
            }
            else
            {
                correctionFactor1 = constantC4[safe];
            }
            correctionFactor2 = constantD2[safe];

            double sigma = Rvalues.Average() / correctionFactor2;
            double Cp    = (USL - LSL) / (6 * sigma);
            double Cpk   = Math.Min((USL - averages.Average()) / (3 * sigma), (averages.Average() - LSL) / (3 * sigma));

            row    = 1;
            column = 1;
            sheet.Cells[row, column + 7]  = "LSL";
            sheet.Cells[row, column + 8]  = "USL";
            sheet.Cells[row, column + 9]  = "Cp";
            sheet.Cells[row, column + 10] = "Cpk";
            row++;
            sheet.Cells[row, column + 7]  = LSL;
            sheet.Cells[row, column + 8]  = USL;
            sheet.Cells[row, column + 9]  = Cp;
            sheet.Cells[row, column + 10] = Cpk;
        }
Exemple #26
0
        public bool checkInput(DataSet dataSet, List <Variable> independentVariables, Variable dependentVariable)
        {
            if (dataSet == null || independentVariables.Count() == 0)
            {
                MessageBox.Show(" Please correct all fields to perform logistic regression. Make sure that only one independent variable is selected.");
                return(false);
            }
            // declare function to use function of excel
            var functions = Globals.ExcelAddIn.Application.WorksheetFunction;
            var sheet     = WorksheetHelper.NewWorksheet("Logistic Regression");

            var matrixX = Matrix <double> .Build.Dense(dataSet.rangeSize(), independentVariables.Count + 1);

            for (var i = 0; i < dataSet.rangeSize(); i++)
            {
                matrixX[i, 0] = 1;
                for (var j = 0; j < independentVariables.Count; j++)
                {
                    matrixX[i, j + 1] = dataSet.getValuesArray(independentVariables[j])[i];
                }
            }

            var matrixY = Matrix <double> .Build.Dense(dataSet.rangeSize(), 1);

            for (var i = 0; i < dataSet.rangeSize(); i++)
            {
                matrixY[i, 0] = dataSet.getValuesArray(dependentVariable)[i];
            }


            // calculate theta iteratively using newton's method, starting from zero matrix
            var startTheta = Matrix <double> .Build.Dense(independentVariables.Count + 1, 1);

            var theta = NewtonsMethod(matrixX, matrixY, startTheta);


            // calculate classification matrix
            var classificationMatrix = CalculateClassificationMatrix(matrixX, matrixY, theta);

            // calculate summary classification
            var summaryClassificationPercentages = SummaryClassification(classificationMatrix);

            var expThetaX = theta.Transpose() * matrixX.Transpose();


            var matrixV = Matrix <double> .Build.Dense(dataSet.rangeSize(), dataSet.rangeSize());

            for (var i = 0; i < dataSet.rangeSize(); i++)
            {
                var prob = 1 / (1 + Math.Exp(-expThetaX[0, i]));
                matrixV[i, i] = prob * (1 - prob);
            }

            var matrixCovar = (matrixX.Transpose() * matrixV * matrixX).Inverse();

            // locations of tables
            const int title                     = 1;
            const int summaryName               = 3;
            const int name                      = summaryName + 6;
            const int betaCoeffName             = 2;
            const int stdErrorName              = betaCoeffName + 1;
            const int waldValueName             = stdErrorName + 1;
            const int pValueName                = waldValueName + 1;
            const int lowerLimitName            = pValueName + 1;
            const int upperLimitName            = lowerLimitName + 1;
            var       classificationMatrixName  = name + 3 + independentVariables.Count;
            var       summaryClassificationName = classificationMatrixName + 4;
            var       dataName                  = summaryClassificationName + 5;
            var       probName                  = independentVariables.Count + 2;

            // names of variables on sheet
            sheet.Cells[title, 1] = "Logistic Regression";

            sheet.Cells[summaryName, 1]     = "Summary";
            sheet.Cells[summaryName + 1, 1] = "Null Deviance";
            sheet.Cells[summaryName + 2, 1] = "Model Deviance";
            sheet.Cells[summaryName + 3, 1] = "Improvement";
            sheet.Cells[summaryName + 4, 1] = "P Value";

            sheet.Cells[name + 1, 1]          = "Constant";
            sheet.Cells[name, betaCoeffName]  = "Beta Coefficient";
            sheet.Cells[name, stdErrorName]   = "Standard Error";
            sheet.Cells[name, waldValueName]  = "Wald Value";
            sheet.Cells[name, pValueName]     = "p Value";
            sheet.Cells[name, lowerLimitName] = "Lower Limit (95%)";
            sheet.Cells[name, upperLimitName] = "Upper Limit (95%)";

            ((Range)sheet.Cells[name + 1, 1]).EntireColumn.AutoFit();
            ((Range)sheet.Cells[name, betaCoeffName]).EntireColumn.AutoFit();
            ((Range)sheet.Cells[name, stdErrorName]).EntireColumn.AutoFit();
            ((Range)sheet.Cells[name, waldValueName]).EntireColumn.AutoFit();
            ((Range)sheet.Cells[name, pValueName]).EntireColumn.AutoFit();
            ((Range)sheet.Cells[name, lowerLimitName]).EntireColumn.AutoFit();
            ((Range)sheet.Cells[name, upperLimitName]).EntireColumn.AutoFit();


            for (var i = 0; i < independentVariables.Count; i++)
            {
                sheet.Cells[name + i + 2, 1] = independentVariables[i].name;
            }

            sheet.Cells[classificationMatrixName, 1]     = "Classification Matrix";
            sheet.Cells[classificationMatrixName, 2]     = "1";
            sheet.Cells[classificationMatrixName, 3]     = "0";
            sheet.Cells[classificationMatrixName, 4]     = "Percentage Correct";
            sheet.Cells[classificationMatrixName + 1, 1] = "1";
            sheet.Cells[classificationMatrixName + 2, 1] = "0";

            sheet.Cells[summaryClassificationName, 1]     = "Classification Summary";
            sheet.Cells[summaryClassificationName, 2]     = "Percentage";
            sheet.Cells[summaryClassificationName + 1, 1] = "Correct";
            sheet.Cells[summaryClassificationName + 2, 1] = "Base";
            sheet.Cells[summaryClassificationName + 3, 1] = "Improvement";
            sheet.Cells[summaryClassificationName + 1, 2] = summaryClassificationPercentages[0];
            sheet.Cells[summaryClassificationName + 2, 2] = summaryClassificationPercentages[1];
            sheet.Cells[summaryClassificationName + 3, 2] = summaryClassificationPercentages[2];

            ((Range)sheet.Cells[summaryClassificationName, 1]).EntireColumn.AutoFit();
            ((Range)sheet.Cells[9, 1]).EntireRow.AutoFit();

            sheet.Cells[dataName, 1] = "";
            for (var i = 0; i < independentVariables.Count; i++)
            {
                sheet.Cells[dataName, 2 + i] = independentVariables[i].name;
            }
            sheet.Cells[dataName, probName]     = "Probability";
            sheet.Cells[dataName, probName + 1] = "Forecast";
            sheet.Cells[dataName, probName + 2] = "Class";

            for (var i = 0; i < dataSet.rangeSize(); i++)
            {
                sheet.Cells[dataName + 1 + i, 1] = i + 1;
                for (var j = 0; j < independentVariables.Count; j++)
                {
                    sheet.Cells[dataName + 1 + i, 2 + j] = matrixX[i, j + 1];
                }
                var prob = 1 / (1 + Math.Exp(-expThetaX[0, i]));
                sheet.Cells[dataName + 1 + i, probName] = prob;
                if (prob > 0.5)
                {
                    sheet.Cells[dataName + 1 + i, probName + 1] = 1;
                }
                else
                {
                    sheet.Cells[dataName + 1 + i, probName + 1] = 0;
                }
                sheet.Cells[dataName + 1 + i, probName + 2] = matrixY[i, 0];
            }
            double dF = dataSet.rangeSize() - independentVariables.Count() - 1;

            // print out beta coefficients and std error of coefficient
            for (var i = 0; i < theta.RowCount; i++)
            {
                sheet.Cells[name + i + 1, betaCoeffName] = theta[i, 0];
                var stdError = Math.Sqrt(matrixCovar[i, i]);
                sheet.Cells[name + i + 1, stdErrorName]  = stdError;
                sheet.Cells[name + i + 1, waldValueName] = theta[i, 0] / stdError;
                var pValue = functions.TDist(Math.Abs(theta[i, 0] / stdError), dF, 2);
                sheet.Cells[name + i + 1, pValueName] = pValue;
                var confidenceConstant = 1.96;
                var lower = theta[i, 0] - stdError * confidenceConstant;
                var upper = theta[i, 0] + stdError * confidenceConstant;
                sheet.Cells[name + i + 1, lowerLimitName] = lower;
                sheet.Cells[name + i + 1, upperLimitName] = upper;
            }

            //print out classification matrix
            sheet.Cells[classificationMatrixName + 1, 2] = classificationMatrix[0];
            sheet.Cells[classificationMatrixName + 1, 3] = classificationMatrix[1];
            var n0 = Convert.ToDouble(classificationMatrix[0]) + Convert.ToDouble(classificationMatrix[1]);

            sheet.Cells[classificationMatrixName + 1, 4] = Convert.ToDouble(classificationMatrix[0]) / (Convert.ToDouble(classificationMatrix[0]) + Convert.ToDouble(classificationMatrix[1]));
            sheet.Cells[classificationMatrixName + 2, 2] = classificationMatrix[2];
            sheet.Cells[classificationMatrixName + 2, 3] = classificationMatrix[3];
            var n1 = Convert.ToDouble(classificationMatrix[2]) + Convert.ToDouble(classificationMatrix[3]);

            sheet.Cells[classificationMatrixName + 2, 4] = Convert.ToDouble(classificationMatrix[3]) / (Convert.ToDouble(classificationMatrix[2]) + Convert.ToDouble(classificationMatrix[3]));

            // summary measures
            var    nullDeviance  = 2 * (-n0 * Math.Log(n0 / (n0 + n1)) - n1 * Math.Log(n1 / (n0 + n1)));
            double modelDeviance = 0;

            for (var i = 0; i < dataSet.rangeSize(); i++)
            {
                var    prob   = 1 / (1 + Math.Exp(-expThetaX[0, i]));
                double yValue = matrixY[i, 0];
                modelDeviance = modelDeviance + yValue * Math.Log(1 / prob) + (1 - yValue) * Math.Log(1 / (1 - prob));
            }

            modelDeviance = modelDeviance * 2;

            sheet.Cells[summaryName + 1, 2] = nullDeviance;
            sheet.Cells[summaryName + 2, 2] = modelDeviance;
            sheet.Cells[summaryName + 3, 2] = nullDeviance - modelDeviance;
            sheet.Cells[summaryName + 4, 2] = "P Value";

            return(true);
        }
Exemple #27
0
        public void generateProcessCapability(List <Variable> variables, double LSL, double USL, DataSet dataSet, _Worksheet sheet)
        {
            int    index = 0;
            int    row = 1;
            int    column = 1;
            double correctionFactor1, correctionFactor2;

            double[] averages                = new double[dataSet.amountOfVariables()];
            double[] Rvalues                 = new double[dataSet.amountOfVariables()];
            double[] averageOfAverages       = new double[dataSet.amountOfVariables()];
            double[] xChartUpperControlLimit = new double[dataSet.amountOfVariables()];
            double[] xChartLowerControlLimit = new double[dataSet.amountOfVariables()];
            double[] rChartUpperControlLimit = new double[dataSet.amountOfVariables()];
            double[] rChartLowerControlLimit = new double[dataSet.amountOfVariables()];
            double[] averageOfRvalues        = new double[dataSet.amountOfVariables()];
            int[]    ArrayIndex              = new int[dataSet.amountOfVariables()];
            double[] constantC4              = new double[25] {
                0.0, 0.7979, 0.8862, 0.9213, 0.9400, 0.9515, 0.9594, 0.9650, 0.9693, 0.9727, 0.9754, 0.9776, 0.9794, 0.9810, 0.9823, 0.9835, 0.9845, 0.9854, 0.9862, 0.9869, 0.9876, 0.9882, 0.9887, 0.9892, 0.9896
            };
            double[] constantD2 = new double[25] {
                0.0, 1.128, 1.693, 2.059, 2.326, 2.534, 2.704, 2.847, 2.970, 3.078, 3.173, 3.258, 3.336, 3.407, 3.472, 3.532, 3.588, 3.640, 3.689, 3.735, 3.778, 3.819, 3.858, 3.895, 3.931
            };
            sheet.Cells[row, column]     = "Index";
            sheet.Cells[row, column + 1] = "Observation";
            sheet.Cells[row, column + 2] = "Average";
            sheet.Cells[row, column + 3] = "STD DEV";
            sheet.Cells[row, column + 4] = "Max";
            sheet.Cells[row, column + 5] = "Min";
            sheet.Cells[row, column + 6] = "R";

            sheet.Cells[100, 100] = "=ROWS(" + dataSet.getWorksheet().Name + "!" + variables[0].Range + ")";
            int length = Convert.ToInt32((sheet.Cells[100, 100] as Range).Value);

            sheet.Cells[100, 100] = "";

            double        temp    = 0;
            List <double> allVals = new List <double>();

            double[] all = new double[length * variables.Count];
            foreach (Variable var in variables)
            {
                string   ran   = var.Range.ToString();
                Array    arr   = dataSet.getWorksheet().Range[ran].Value;
                double[] vals  = new double[length];
                int      count = 0;
                foreach (var item in arr)
                {
                    temp        = Convert.ToDouble(item);
                    vals[count] = temp;
                    count++;
                }
                allVals.AddRange(vals);
            }
            all = allVals.ToArray();

            double totMean = 0;
            double totStd  = 0;

            for (index = 0; index < variables.Count; index++)
            {
                row++;
                sheet.Cells[row, column]     = index;
                sheet.Cells[row, column + 1] = variables[index].name;
                sheet.Cells[row, column + 2] = "=AVERAGE(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")";
                sheet.Cells[row, column + 3] = "=STDEV(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")";
                sheet.Cells[row, column + 4] = "=MAX(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")";
                sheet.Cells[row, column + 5] = "=MIN(" + dataSet.getWorksheet().Name + "!" + variables[index].Range + ")";
                sheet.Cells[row, column + 6] = (double)(sheet.Cells[row, column + 4] as Range).Value - (double)(sheet.Cells[row, column + 5] as Range).Value;
                totMean           = totMean + Convert.ToDouble((sheet.Cells[row, column + 2] as Range).Value);
                totStd            = totStd + (Convert.ToDouble((sheet.Cells[row, column + 3] as Range).Value));
                ArrayIndex[index] = index;
                var cellValue = (double)(sheet.Cells[row, column + 2] as Range).Value;
                if (cellValue < -214682680)
                {
                    cellValue = 0;                         // if cellValue is the result of a division by 0, set value to 0
                }
                averages[index] = cellValue;
                cellValue       = (double)(sheet.Cells[row, column + 6] as Range).Value;
                Rvalues[index]  = cellValue;
            }
            totMean = all.Average();
            totStd  = sheet.Application.WorksheetFunction.StDev(all);
            //totMean = totMean / variables.Count;
            //totStd = totStd / variables.Count;

            if (dataSet.getVariableNamesInFirstRowOrColumn())
            {
                correctionFactor1 = constantC4[variables.Count - 1];
                correctionFactor2 = constantD2[variables.Count - 1];
            }
            else
            {
                correctionFactor1 = constantC4[variables.Count];
            }
            correctionFactor2 = constantD2[variables.Count];


            System.Diagnostics.Debug.WriteLine(totStd);
            double sigma  = totStd;
            double Cp     = (USL - LSL) / (6 * sigma);
            double Cpk    = Math.Min((USL - totMean) / (3 * sigma), (totMean - LSL) / (3 * sigma));
            double Pbelow = sheet.Application.WorksheetFunction.NormDist(LSL, totMean, totStd, true);
            double Pabove = 1.0 - sheet.Application.WorksheetFunction.NormDist(USL, totMean, totStd, true);

            row    = 1;
            column = 1;
            sheet.Cells[row, column + 8]  = "LSL";
            sheet.Cells[row, column + 9]  = "USL";
            sheet.Cells[row, column + 10] = "Cp";
            sheet.Cells[row, column + 11] = "Cpk";
            sheet.Cells[row, column + 12] = "P(below LSL)";
            sheet.Cells[row, column + 13] = " Per million";
            sheet.Cells[row, column + 14] = "P(above USL)";
            sheet.Cells[row, column + 15] = " Per million";

            row++;
            sheet.Cells[row, column + 8]  = LSL;
            sheet.Cells[row, column + 9]  = USL;
            sheet.Cells[row, column + 10] = Cp;
            sheet.Cells[row, column + 11] = Cpk;
            sheet.Cells[row, column + 12] = Pbelow;
            sheet.Cells[row, column + 13] = Pbelow * 1000000;
            sheet.Cells[row, column + 14] = Pabove;
            sheet.Cells[row, column + 15] = Pabove * 1000000;
        }
Exemple #28
0
        // vanaf hier copy van oude code

        public void Print(DataSet dataSet, List <Variable> variables, bool rdbMovingAverage, bool rdbSimpleExponentialSmoothing, bool rdbHoltsExponentialSmoothing, bool rdbWintersExponentialSmoothing, bool doOptimizeParameters, int numberOfForecasts, int numberOfHoldouts, int seasonalPeriod, int span, string level, string trend, string seasonality)
        {
            // Create new sheet
            var sheet = WorksheetHelper.NewWorksheet("Forecast");

            // use variables
            _doOptimizeParameters = doOptimizeParameters;
            _numberOfForecasts    = numberOfForecasts;
            _numberOfHoldouts     = numberOfHoldouts;
            _seasonalPeriod       = seasonalPeriod;
            _span = span;

            // variables for writing on sheet and writing of title
            int title = 1;
            int forcastingConstants = 3;
            int summaryName         = forcastingConstants + 5;
            int row = summaryName + 5;

            sheet.Cells[title, 1] = "Forecast ";
            sheet.Cells[forcastingConstants, 1] = "Forecasting Constants";
            sheet.Cells[summaryName, 1]         = "Summary";
            sheet.Cells[summaryName + 1, 1]     = "Mean Absolute Error";
            sheet.Cells[summaryName + 2, 1]     = "Root Mean Squared Error";
            sheet.Cells[summaryName + 3, 1]     = "Mean Absolute Percentage Error";
            ((Range)sheet.Cells[row, 1]).EntireColumn.AutoFit();

            // variables to count number of forecast, holdouts and data
            var nForecasts = Convert.ToDouble(numberOfForecasts);
            var nHoldouts  = Convert.ToDouble(numberOfHoldouts);
            var nData      = Convert.ToDouble(dataSet.rangeSize());


            int column = 2;

            foreach (Variable variable in variables)
            {
                row             = 1;
                double[,] array = RangeHelper.To2DDoubleArray(variable.getRange());
                double[] array2 = new double[array.Length];
                for (int i = 0; i < array.Length; i++)
                {
                    array2[i] = array[i, 0];     // hier moet nog iets komen waardoor het mogelijk wordt om variabelen in rijen te verwerken
                }

                //Print data
                column++;
                sheet.Cells[row++, column] = variable.name;
                for (int i = 0; i < array2.Length; i++)
                {
                    sheet.Cells[row++, column] = array2[i];
                }
                row = 1;
                column++;
                // Plot next figure below the data and the forecast
                int rowFigure = row + variables.Count + Convert.ToInt16(nForecasts) + 2;
                // Calculate moving average forecast if box is checked
                if (rdbMovingAverage)
                {
                    EvaluateMovingAverage(array2, row, column, sheet);
                    string name = "Forecast Moving Average: " + variable.name;
                    //var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]];
                    var rangeData     = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]];
                    var rangeForecast = sheet.Range[sheet.Cells[row + 2, column], sheet.Cells[row + nData + nForecasts, column]];
                    new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name);
                }

                // Calculate exponential smoothing (simple) forecast if box is checked
                if (rdbSimpleExponentialSmoothing)
                {
                    CalculateSimple(array2, row, sheet, column);
                    string name = "Forecast Exponential smoothing (Simple): " + variable.name;
                    //var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]];
                    var rangeData     = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]];
                    var rangeForecast = sheet.Range[sheet.Cells[row + 2, column + 1], sheet.Cells[row + nData + nForecasts, column + 1]];
                    new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name);
                }


                // Calculate exponential smoothing (holt) forecast if box is checked
                if (rdbHoltsExponentialSmoothing)
                {
                    CalculateHolt(array2, row, sheet, column);
                    string name = "Forecast Exponential smoothing (Holt): " + variable.name;
                    //var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]];
                    var rangeData     = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]];
                    var rangeForecast = sheet.Range[sheet.Cells[row + 2, column + 2], sheet.Cells[row + nData + nForecasts, column + 2]];
                    new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name);
                }

                // Calculate exponential smoothing (winters) forecast if box is checked
                if (rdbWintersExponentialSmoothing)
                {
                    CalculateWinter(array2, row, sheet, column);
                    string name = "Forecast Exponential smoothing (Winter): " + variable.name;
                    //var rangeLabels = sheet.Range[sheet.Cells[row + 1, column], sheet.Cells[row + nData + nForecasts, column]];
                    var rangeData     = sheet.Range[sheet.Cells[row + 2, column - 1], sheet.Cells[row + nData + nForecasts, column - 1]];
                    var rangeForecast = sheet.Range[sheet.Cells[row + 2, column + 3], sheet.Cells[row + nData + nForecasts, column + 3]];
                    new TimeSeriesGraph().CreateNewGraph2(sheet, rowFigure, rangeData, rangeForecast, name);
                }
                column = column + 6;
            }
        }
Exemple #29
0
        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);
        }