Include Top

How Do I Display Descriptive Statistics in Excel Using SigmaXL?


Descriptive/Summary Statistics

  1. Open Customer Data.xls (to access, click SigmaXL > Help > Open Help Data Set Folder or Start > Programs > SigmaXL > Sample Data). Click Sheet 1 Tab.
  2. Click SigmaXL > Statistical Tools > Descriptive Statistics.
  3. Check Use Entire Data Table, click Next.
  4. Select Overall Satisfaction, click Numeric Data Variable (Y) >>, select Customer Type, click Group Category (X1) >>, as shown:
  5. Descriptive Statistics

  6. Click OK.
  7. Descriptive Statistics are given for Customer Satisfaction grouped by Customer Type:

  8. Descriptive Statistics Result

    Which Customer Type has the highest mean satisfaction score? Clearly Type 2. However, we have to be careful concluding that there is a significant difference in satisfaction solely by looking at the Means. In the Analyze Phase, we will run tests of hypothesis to validate that Type 2 Customers are, in fact, significantly more satisfied.

    Tip: Click on Column B, click Window > Split, Window > Freeze Panes (Excel 2007/2010: View | Window | Split, Freeze Panes). This freezes Column A and allows you to scroll across the Descriptive Statistics for each level of the Group Category. This is particularly beneficial when there are a large number of columns.

  9. Click Recall SigmaXL Dialog menu or press F3 to recall last dialog. Change the format selected to Column Format as shown:

    Descriptive Statistics

  10. Click OK. Descriptive Statistics are given for Customer Satisfaction broken out by Customer Type in Column Format:

    Descriptive Statistics Column Format


Descriptive Statistics - Options

  1. Click Recall SigmaXL Dialog menu or press F3 to recall last dialog. Click Options. Check Select
    All and change Percentile Confidence Intervals to Percentile to display all Percentile values in
    the report.



    Tip: Select only those options that are of interest in order to minimize the size of the report.
    Here we are selecting all options for demonstration purposes. Note that when any option is
    checked, Row Format is automatically selected, Column Format and Group Category (X2) are
    greyed out. These display options are limited due to the amount of information displayed in
    the extended report.
  2. Click OK. Extended Descriptive Statistics are given for Customer Satisfaction grouped by
    Customer Type:



  3. The Additional Descriptive Statistics are:
    • 5% Trimmed Mean. The highest 5% and lowest 5% are excluded and mean calculated with
    the rest of the data. This gives a robust alternative to the Median as a measure of central
    tendency in the presence of outliers.

    • Standard Error of Mean StDev/N
    • Variance (StDev2)
    • Coefficient of Variation (100 * StDev/Mean)
    • Short Term StDev (MR-bar/d2)
     
  4. The Additional Normality Tests are:

    • Shapiro-Wilk (n <= 5000) and Kolmogorov-Smirnov-Lilliefors (KSL, n > 5000)
      • This is a popular alternative to Anderson Darling.
    • Doornik-Hansen (DH)
      • Univariate omnibus test based on Skewness and Kurtosis. (Note, the bivariate DH
        test is used in Correlation Matrix to test bivariate normality).
      • Best for data with ties, i.e. “chunky” data. Anderson-Darling, Shapiro-Wilk and KSL
        are severely affected by ties in the data and will trigger a low P-Value even if the
        data are normal.
      • See Appendix Doornik-Hansen (DH) Normality Test for further details and
        references.

  5. The Percentile Report gives 27 values from 0.135 to 99.865.

  6. The Percentile Ranges are:
    • 75 - 25 (50%, Interquartile Range IQR)
    • 90 - 10 (80%, Interdecile Range IDR)
    • 95 - 5 (90%, Span)
    • 97.5 - 2.5 (95%, +/- 1.96 Sigma Equivalent)
    • 99 - 1 (98%)
    • 99.5 - 0.5 (99%)
    • 99.865 - 0.135 (99.73%, +/- 3 Sigma Equivalent)


  7. The Percentile Confidence Intervals give 27 values from 0.135 to 99.865.
  8. The Quartile Confidence Intervals give 3 values: 25, 50 and 75.
  9. The Percentile Tolerance Intervals are 50%, 80%, 90%, 95%, 98%, 99%, and 99.73%.
  10. Confidence Intervals and Tolerance Intervals can be exact or interpolated. If exact, the actual
    exact confidence level will be a value greater than or equal to specified, due to percentile
    values being discrete in nature. The actual exact level will also be reported in this case. If
    interpolated, the result will be an interpolated estimate of the specified confidence level
    (typically 95.0%) and is the recommended setting. See Appendix Percentile (Nonparametric)
    Confidence and Tolerance Intervals for further details.
  11. If the Confidence Interval or Tolerance Interval cannot be computed due to inadequate sample
    size, a minimum sample size is reported.



  12. The Outlier (Boxplot Rules) Tests are: Potential 1.5(IQR), Likely 2.2(IQR), Extreme 3.0(IQR)
  13. Grubbs Outlier Test is more powerful at detecting a single outlier as maximum or minimum but
    assumes that the remainder of the data are normally distributed.
  14. The Randomness Runs Test is a nonparametric exact runs test.
  15. The Outlier and Randomness Tests use the same Green, Yellow, Red highlight given in the
    automatic assumptions report that are included in t-tests and ANOVA.
  16. Click Recall SigmaXL Dialog menu or press F3 to recall last dialog. Click Options. Uncheck
    Select All to clear the selections and check Percentile Confidence Intervals, select Exact and
    Percentile. Check Percentile Tolerance Intervals, and select Exact as shown:



  17. Click OK. The Percentile Confidence Intervals and Tolerance Intervals are displayed:


  18. The specified 95% is a guaranteed minimum. The exact confidence level is given with each
    reported interval.

Define, Measure, Analyze, Improve, Control

Lean Six Sigma Software Excel Add-in

Simulate, Optimize,
Realize

Lean Six Sigma Software Excel Add-in

Web Demos

Our CTO and Co-Founder, John Noguera, regularly hosts free Web Demos featuring SigmaXL and DiscoverSim
Click here to view some now!

Contact Us

Ph: 1.888.SigmaXL (744.6295)

Support: Support@SigmaXL.com

Sales: Sales@SigmaXL.com

Information: Information@SigmaXL.com