Using Spreadsheet Software to Compute Maximum Likelihood ...
Using Spreadsheet Software [MLE LOD Template.xls] to Compute Maximum Likelihood Estimates of the Means and Standard Deviations of a Lognormal Distribution with Data Below Detection Limits
We wish to maximize the likelihood of the observations which are assumed to follow a lognormal distribution. In maximizing the likelihood function, it is conventional to apply a logarithmic transformation to convert the product of likelihood terms to a sum. In order to illustrate the underlying formula structure of the spreadsheet, we have turned on the formula view in Excel.
For the data points with measured values, column B contains the logarithm of the likelihood for that observation. A crucial point is that one cannot work with symbolic equations in spreadsheet software. Numerical values must thus be substituted for μl and σ l. We thus insert an initial, or starter value, for the mean and standard deviation into the spreadsheet. These appear in cells E5 and E6. The Solver Module in the spreadsheet software will vary the values in E5 and E6 to maximize the sum of the log likelihoods in Column B. Convenient starting values for the estimates in E5 and E6 are the mean and standard deviation of the logarithms of the observed data values in Column A.
Now, all we know about the smallest data values is that they are less than the detection limit. The likelihoods for the logarithms of these data points are given by the area under the normal distribution curve up to log(Detection Limit). These are computed by the NORMDIST spreadsheet function. This function returns the normal cumulative distribution for the specified mean and standard deviation. The syntax is: NORMDIST(x, mean, standard_dev, cumulative), where
▪ x: is the value for which you want the probability (in this instance, since we are dealing with a normal distribution of the logarithms of the observed values, x is the logarithm of the measured value)
▪ mean: is the mean of the lognormal distribution.
▪ standard_dev: is the standard deviation of the lognormal distribution.
▪ cumulative: is a logical value that determines the form of the function. If cumulative is TRUE, NORMDIST returns the cumulative distribution function, which is what we desire.
The value of x is ln(Detection Limit), the mean and standard deviation are given by our starter values in E5 and E6 and 'cumulative' is set to TRUE, since we want the area under the curve up to ln(Detection Limit), given μl and σ l. The goal is to select values for E5 and E6 to maximize the sum of the likelihoods, which can be found in cell B19.
To maximize the sum of the likelihoods we select the Solver Tool from the Tools Menu. We are invited to select a Target Cell. Here, it is B19, the sum of the log likelihoods. We select the option to maximize the value of B19. We are also invited to select the cells to change in order to achieve this maximization. Here, they are E5 and E6, our initial estimates of µ and . Clicking on the Solve button initiates the computation and the values in E5 and E6 are replaced with the maximum likelihood estimates of the mean and standard deviation of the logarithms of data from the parent distribution of the data points in Column A. Cells F11 and F12 contain the formulae for the computation of the arithmetic mean, μa, and standard deviation, σa, of the hygiene data from the lognormal mean and standard deviations.
By substituting hygiene data values for the example data in this template, MLE estimates can be computed for any data set. If the measurements involve more than one detection limit, then the appropriate limits may be substituted for ln(x) in the NORMDIST spreadsheet function.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- how to compute dividends paid
- how to compute mortgage interest
- how to compute product cost
- how to compute dividends
- how to compute cash dividends
- how to compute net income
- how to compute coupon rate
- how to compute logarithms
- how to compute r correlation coefficient
- how to compute linear correlation coefficient
- how to compute the correlation coefficient
- how to compute r squared