How Do I Display Descriptive Statistics in Excel Using SigmaXL?

Descriptive/Summary Statistics

1. Open Customer Data.xlsx (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. Click OK.


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



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. 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.


7. Click Recall SigmaXL Dialog menu or press F3 to recall last dialog. Change the format selected to Column Format as shown:
   
   
   
   
8. Click OK. Descriptive Statistics are given for Customer Satisfaction broken out by Customer Type in 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 centraltendency in the presence of outliers.
   • Standard Error of Mean (StDev/√𝑁)
   • 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.
    
    
Tip: The Tolerance Intervals given here are nonparametric, so the data does not have to be normal. However, if you have normal data, you can use the Tolerance Interval Calculator for Normal data, which will allow smaller sample sizes to be used.


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.