DE Histograms Release 1999

DE Histograms Release 2000.0726

Overview
Features
Specifications
Installation
Demonstration
Accuracy

This Excel Add-In provides fast and easy graphical and statistical data analysis.
It allows your product or process to "speak for itself". This Release has been designed for Excel 97 for Windows.

(c) Copyright Delphi Delco Electronics Systems 1997 - 2000 This software may be freely shared world-wide. All we ask is that you let us know how this has benefited you and your business. Thank you.

Kevin Hankins Applied Statistics Product Test & Validation MS R117 Delphi Delco Electronics Systems POB 9005 KOKOMO IN 46904-9005 765-451-7670 kevin.t.hankins@delphiauto.com

"Sharing of software or other experience does not imply its fitness for your purpose, nor imply or express any warranty."

Back to ASSUME Resources

Overview

Are you responsible for a product, process, or part?
Continuous improvement principles encourage you to know and improve your
product.
Now this Excel Add-In allows you to graphically and statistically analyze
your data. Your product or process "speaks for itself".
If your data is "continuous" numeric data that can be fit by one or more
Normal distributions, read on.

The Features of this Add-In are very advanced:
* Full Excel capability
* Spec-Table Driven
* Smart X-Axis features (no more hunting for off-scale data)
* Up to 65,535 data points per Test * Condition
* Up to 65,535 Tests (limited by available Memory)
* Graphical comparison of any number of Conditions
* Advanced Statistics automatically deal with outliers
    (which drastically inflate estimates of standard deviation, and skew
the average, in most other programs)
See the Features section for a more complete list.

Output Histograms appear in a new Workbook containing a Summary
Sheet followed by one Sheet per page of 8 histograms.

One Histogram is output for each Test in the Spec Table and for each
Condition.

A Tukey rank-based robust outlier detection method is applied,
then Normal statistics are calculated on the remaining Main Mode points.

If there is more than one Condition, t-test and F-test statistics are
reported,
with the first Condition as the reference distribution. Conditions with
significantly different Average or Variance are highlighted:
   * Green on Yellow for improved Variance
   * Red on Yellow for different Average, or increased Variance
   * Significance level is set to 0.05 divided by the number of tests
       (in order to control overall Type I Error rate)

Features

"DE Histograms" Features:
* Your data is safe: All calculations are done in a separate workbook
* Comprehensive Output is placed in a new Excel Workbook and includes:
     * Full Excel capabilities (viewing, filtering, saving, printing, editing ...)
     * Statistical results are presented in both tabular and graphical form
     * All traceability information (reference to data, spec table)
     * AutoFilter (point-and-click selection of tests or conditions of interest)
     * Results are presented in 1-page-wide view (ready for printing)
     * File-size estimate
* Fast - Thousands of data points per second
* Built-in Help Dialog and Demo Data
* Demo Data can be modified for more extensive evaluation of program capabilities
     * Includes a random number generator with which to simulate new data
* Automatic handling of Outliers
* Handles all types of data including formulas, text, and error values
* Spec-Table driven (Spec Limits and other options)
     * Tests may be in any order and are individually selectable
     * The following user-specified Spec-Table Columns drive this program:
          * Test
          * Name
          * Sort
          * SpecLo, SpecHi
          * HistSigma
          * Title
          * ChartLo, ChartHi
          * Units
          * Avg, StDev, Cp (used to turn off statistics if desired)
* Spec Table and Data may be anywhere, on any sheet(s) in any workbook(s)
     * "New Spec/Data Tables" option allows a quick-start
     * Multiple Spec Tables may be used (on different Sheets)
* Handles all input errors (such as non-numeric data)
* Filters are observed, providing useful selection of spec rows or data rows
* All user inputbox inputs pop up as defaults for the next Run
* Histogram X-Axis Features:
      * All data is always shown
      * No truncation or round-off error
      * X-Axis scale is automatically locked across Conditions (user may override)
* Smart pagination
* Smart Tabular output
* t-test and F-test statistics are reported (if there is more than one Condition)
* Number format is aligned non-scientific if possible
* Explanatory Field Comments are included
* Smart Graphical output
* The Histograms for a given Condition are always at the same place on a page, for #Conditions=1 thru 8
* Yaxis scale is not affected by a high number of off-chart outliers
* Smart format of very small or very large numbers
* Titles may be included for each Test, Condition, and Run
* Professional Documentation:
     * All output contains consistent DateTime and Release Code Stamps
     * Release Code is traceable

Specifications

General Specifications:
* Requires: Excel (Excel 97 or later) for Windows
* Subject to available Memory
* Only the author's current version of Excel will be supported
* Full fast-response Support is limited to Delphi Automotive Systems, its
Suppliers, and Customers
* Up to 32,767 Tests per Project
* Any number of Conditions
* Measurements may be either Periodic or non-Periodic
* This Release allows any of the following Tests Table Column Headers for the
Min and Max limits:
"speclo", "min", "limit min", "lim min", "lspl", "_lspl", "Spec Limit Lo",
"Spec Lo Limit",
"Spec Lim Lo", "Spec Lo Lim", "Low Limit", "Lo Limit", "Low Lim", "Lo
Lim", "LimLo",
"LoLim", "Lim Lo", "Limit Lo", "Lim Low", "Limit Low", "lsl", "_lsl_",
"_lsl"
"spechi", "max", "limit max", "lim max", "uspl", "_uspl", "Spec Limit Hi",
"Spec Hi Limit",
"Spec Lim Hi", "Spec Hi Lim", "High Limit", "Hi Limit", "High Lim", "Hi
Lim", "LimHi",
"HiLim", "Lim Hi", "Limit Hi", "Lim High", "Limit High", "usl", "_usl_",
"_usl"
These Headers are then carried thru to the Main Table and Graphical Output.

Specifications, Analysis of Data in Excel:
* Up to 65,535 data points per Test * Condition
* Any number of Data Range Areas (limited by 255-Character InputBox) per
Condition

Specifications, OLAP Analysis of Data In Files:
* Up to 65,500 Standard Data Files (in each OLAP List)
* Up to 32,767 File-level Conditions (in each file)
* Up to 32,767 Part-level Conditions (for each part)
* Up to 65,500 Bins per Project
* Any number of Parts or Times/Cycles per file
(One "part" may also be multiple measurements of one part.)

Installation

Everything needed to run this program is contained in one file Dehist.xla. Unfortunately it does not seem possible to download this successfully using many browsers, so Right-click here and select the Save .. As option to begin downloading a zipped version of this file. Once downloading is complete you will need to unzip the file.
Open EXCEL. If you do not have an active worksheet, Click on File - New (in the Menu).
Open dehist.xla

(Due to the smart toolbars in Excel 2000, it is no longer recommended to load
this from Tools-AddIns.)

If a security dialog comes up during installation of DE Histograms, click on
"Enable Macros".
This tells you that you are loading Visual Basic code.

Following Installation, the Active Help System should appear.

"DE Histograms" should now appear on the right side of the main CommandBar
(toolbar).

Open your data workbook(s) prior to Analysis.
Click on Window to return to any open Workbook.

Click on "DE Histograms" in the Menu (at the top). Then the options are:
* Analyze Data in Files
* Analyze Data in Excel
* Help

Verify the proper Release at the top of the Help dialog or the bottom of any
output Sheet.

Demonstration Session

Open Excel.
If a worksheet does not appear, Click File-New from the menu at the top
of your screen.

All procedures are run by clicking one of the three options from the menu Data-Analysis. (Standard Alt-key functionality may also be used, as indicated by the underlined characters in the menus.)

If Analysis does not appear in the Data drop down menu, return to the Installation section above and repeat the installation process.
Click Data-Analysis-Help (from the menu).
A Help Dialog is displayed, and may be printed by Clicking on the Print button.
Then Click OK.
Click Data-Analysis-Demo Data.
A message is displayed, alerting you that "This will add a sheet containing example Spec and data tables.”
Click OK. A Sheet named "demo_d" has now been added to your workbook. This sheet contains a Spec table and two data tables. This is all that you will need to run this demonstration of the DE Histograms routine.

The Spec Table is in the upper part of this Sheet. The data tables are below. All you need to do is tell Excel where these tables are. (Note: These Tables are in normal Excel Sheets, and so are fully changeable by you. You are encouraged to try out the various features of DE Histograms by changing the data.)

DE Histograms is driven by the Spec Table. The Spec Table lists the Tests that may be charted, as well as many features that are customized by you, such as Spec Limits and Test description Fields.
Click Data-Analysis-Histograms from the menu. An input box is displayed which asks you to: "Click in any cell in the desired Sort Column of the desired Spec Table".
Click in any cell of the Sort Column. Clicking any one cell tells the procedure where the Spec Table is, and the location of the Sort Column. (The sort column establishes the analysis sequence for the data and the sequence for showing the histograms.) Excel will automatically determine the extent of the Table (and column) by looking around the selected cell.
(You may proceed or stop at any input box by clicking on either OK or Cancel.)
Click OK. An input box is displayed - "Please enter how many Conditions are to be analyzed". Enter a number. For example, if you have data representing two temperatures, enter 2.
Click OK. An input box is displayed which asks you to - "Please enter a name for Condition #1". You enter <'-40C>.
Click OK. An input box is displayed which asks you to - "Click in any cell in the Data Table for -40C (Condition #1)". You click on any cell in the Table at the lower-left.
An input box is displayed which asks you to - "Please enter a name for Condition #2". You enter <25C>.
Click OK. An input box is displayed which asks you to - "Click in any cell in the Data Table for 25C (Condition #2)". You click on any cell in the Table at the lower-right, which represents this temperature.
Click OK. Now, Excel has all the information it needs to statistically analyze your information and make histograms. It generates the statistical estimates and histograms in a new Workbook and then activates that Workbook. This may take several seconds (a few thousand points per second). The status bar at the bottom of your screen advises you of the progress.

The output consists of several sheets in the output Workbook: a) The Main Sheet summarizes all statistics by Test and Condition. b) All other Sheets (H(1) thru H(*)) contain the histograms and statistics. These are in Picture Format to save disk space if the Workbook is saved.

Note: The main Table on the Main Sheet has an autofilter: Click on any of the drop-down icons in the header row to filter your data. Excel also recognizes any autofiltering that has been done on the input Spec or Data Tables

You may do anything you want with the output Workbook, such as Print Entire Workbook, Print selected Sheets, copy and paste graphs, Save As, or Close without saving. Also, the Main Sheet is formatted so as to make it easy to use for presentations or for input to other procedures.

To save your input box responses as defaults for the next Session, you need to save the Workbook that contains your Spec Table.

Note: For analysis of your own data, you may use the Data-Analysis-New Spec/Data Tables option.

Several users have commented that this combined with a writeable CD-ROM and the freely available 2MB Excel Viewer available at: http://technet.microsoft.com/cdonline/content/complete/desk/office/excel/tools/xlvw95.htm is a good solution for data-retention archival requirements.

Accuracy

The accuracy of the Avg and StDev statistics of Release 1999.0125 has been tested using all nine Statistical Reference Datasets (StRD) of the US National Institutes of Science and Technology (NIST). For each of these 18 values, the error divided by the Avg value is less than 10ppb (less than 1E-08). A report is available on request: "Accuracy of DE Histograms Release 1999.0125" from the author (see below).

No numerical results have been changed with this Release.