How to solve for Weibull distribution parameters using Excel's Solver?

[Iwicka]

Homework Statement

Asked to find Weibull distribution parameters, \theta and \tau, using Excel's Solver feature. I know that the 25th percentile is 1500 and the 75th percentile is 250000.

Homework Equations

Weibull CDF = 1-exp(-(x/\theta)^\tau)

The Attempt at a Solution

I have solved the solution by paper to yeild: \theta=86384.36181, \tau=0.307375855.

In Excel's Solver:
Objective cell is the formula: =EXP(-(1500/C2)^C3)-0.75
Optimize value of: 0
By Changing Cells: C2 and C3, which are my \theta and \tau values respectively.
Constraints: cell with formula: EXP(-(250000/C2)^C3)-0.25 constrained to be 0.

The solver is not able to find the solution. What am I doing wrong?

 

[Ray Vickson]

Homework Statement

Asked to find Weibull distribution parameters, \theta and \tau, using Excel's Solver feature. I know that the 25th percentile is 1500 and the 75th percentile is 250000.

Homework Equations

Weibull CDF = 1-exp(-(x/\theta)^\tau)

The Attempt at a Solution

I have solved the solution by paper to yeild: \theta=86384.36181, \tau=0.307375855.

In Excel's Solver:
Objective cell is the formula: =EXP(-(1500/C2)^C3)-0.75
Optimize value of: 0
By Changing Cells: C2 and C3, which are my \theta and \tau values respectively.
Constraints: cell with formula: EXP(-(250000/C2)^C3)-0.25 constrained to be 0.

The solver is not able to find the solution. What am I doing wrong?

The EXCEL Solver can sometimes choke on highly nonlinear problems, because it tries to solve equations using quasi-Newton methods but with derivatives replaced by finite differences; or it may try to perform a sequence of successive quadratic fits, etc. So, your problem has two issues: (i) difficult nonlinearity; and (ii) bad scaling. One of the EXCEL Solver options is an "automatic scaling" setting, but I would not trust it---in fact, it seemed to not work when I tried it; instead, I would scale manually (using percentiles 1.5 and 250 instead of 1500 and 250000). Also, rather than solving the equations, I would suggest minimizing the sum of squared deviations.

Even so, I could not get it to work until I re-cast the problem: for
W(x) = 1 - \exp(-(x/a)^b), (using a and b instead of θ and τ) we can solve W(x) = p as x = x(p), where
x(p) = a\, \exp\left( \frac{\ln(-\ln(1-p))}{b}\right).
Now minimizing the sum of squares (x(.25) - 1.5)^2 + (x(.75) - 250)^2 seems to work well: it gives a = 86.14852 and b = 0.306587316 (close to what you want, re-scaled). By re-starting the Solver from this point and re-setting some of the parameters, better accuracy can perhaps be obtained; however, there is a fundamental limit to the attainable precision, due to the Solver's use of finite-differences in place of analytical derivatives.

RGV

 

[Iwicka]

Thank you for replying Ray, I appreciate it.

Here's how I ended up solving the problem. The Weibull distribution has a VaR(p) formula, which is the "Value at Risk"; essentially it directly translates into the X value of the distribution that corresponds to a percentile for the distribution.

The formula for the Weibull is: VaR(p) = \theta(-LN(1-p))^1/\tau, where p is the percentile.

Then I entered this formula into my Solver as the objective using the same methods I prescribed earlier and voila! It worked.

I hope this helps anyone else that has a similar problem.