Distribution Fitting Software & Articles

How To Fit Distributions in Excel

Excel has become the de facto standard application for data analysis and presentation across a variety of industries, so if you deal with random data of any kind, chances are your data is stored in Excel workbooks. However, analyzing probability data in Excel can be tricky as the standard Excel package includes no facilities for fitting probability distributions to data. That is when EasyFitXL, the distribution fitting add-in for Excel, comes in handy.

Contents
Visual Distribution Fitting Using EasyFitXL
Distribution Fitting Using The Worksheet Functions

Visual Distribution Fitting Using EasyFitXL

The easiest way to fit distributions in Excel is using the visual distribution fitting capability of EasyFitXL. The process of fitting distributions is very similar to that of EasyFit. The major difference is that your input data is contained in a worksheet, and you don't need to switch from Excel to EasyFit in order to analyze it. With EasyFitXL, you also have more options for using the analysis results: in addition to calculating probabilities and making estimates & projections, EasyFitXL lets you create advanced worksheet models and VBA applications dealing with uncertainty.

How To Specify Your Data For Distribution Fitting

EasyFitXL accepts the same data formats as EasyFit, however, there are a few Excel-specific requirements you should keep in mind when specifying your input data:

• you must turn off any filters you have in a worksheet (analyzing data in filtered columns is not supported)
• if the data consists of two columns (like the X-DENSITY data), both columns must be in the same worksheet

First, you need to select the range of cells containing the data you wish to analyze. This can be either one or two columns, depending on the format of your data. The next step is to choose EasyFitXL|Fit Distributions from the main menu of Excel. As a result, the Input Data window will appear:

The input data fields will be prefilled based on the range of cells you select, so it only remains for you to specify whether your data is continuous or discrete. Once you are done, click OK to start the distribution fitting process.

Selecting The Best Fitting Distribution

After the distributions are fitted, EasyFitXL will display the Fitting Results window that lets you compare the distributions and select the best model:

For details on how to interpret the distribution fitting results, see the other two tutorials: goodness of fit tests and distribution graphs.

Applying The Best Fitting Distribution

Once you select the distribution that best fits to your data, you can apply it to perform specific calculations using the built-in distribution viewer tool — right click on a distribution and select StatAssist from the popup menu:

StatAssist lets you view graphs and evaluate various functions of the selected distribution. The two major uses of StatAssist are calculating probabilities and making estimates & projections.

Distribution Fitting Using The Worksheet Functions

Sometimes you already know which distribution fits to your woksheet data well, and you only need to estimate the distribution parameters. In this case, rather than using the visual fitting capability, you can use the worksheet fitting function available in EasyFitXL. This function works as a regular Excel function; it accepts your input data and returns the resulting distribution model:

=DistFit(Distribution;Data;[Options])

where:

• Distribution is the code name of the distribution you want to fit to your data (e.g. "Weibull"; see the Help File for a complete list of supported distributions and their code names);
• Data is the input data set you want to analyze — this can be either a cell range reference (A1:C10) or an array ({1, 4.5, 7, 3.2, 6});
• Options is an optional text parameter allowing to set some of the distribution parameters to fixed values to prevent them from being estimated. For instance, specifying "gamma=0" when fitting the Gamma distribution will force EasyFitXL to set =0 and fit the two-parameter Gamma distribution (it will only estimate the and parameters). Note that this option only works with specific distributions and specific parameters — please refer to the documentation to learn which options can be specified.

For example, assuming the data set {1,3,6,5,1,1,1,8,1,1} is contained in cells A1:A10, specifying in a sheet

=DistFit("Logarithmic"; A1:A10)

will return Logarithmic(0.834208) meaning the Logarithmic distribution with parameter =0.834208.

Using The Fitting Results

The return value of the DistFit function can be passed to other worksheet functions provided by EasyFitXL to calculate various properties of the selected distribution.

Conclusion

EasyFitXL makes it easy for you to fit probability distributions to data contained in Excel worksheets. Most times you will use the visual fitting capability to select the best fitting distribution based on the goodness of fit tests and graphs. In certain cases, you can also use the DistFit function allowing to fit the specified distribution to sheet data and display the resulting distribution parameters right in a worksheet.

See also: EasyFitXL worksheet function reference