Iwicka said:
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