1. Limited time only! Sign up for a free 30min personal tutor trial with Chegg Tutors
    Dismiss Notice
Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

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

  1. Nov 2, 2012 #1
    1. The problem statement, all variables and given/known data

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

    2. Relevant equations

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

    3. The attempt at a solution

    I have solved the solution by paper to yeild: [itex]\theta[/itex]=86384.36181, [itex]\tau[/itex]=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 [itex]\theta[/itex] and [itex]\tau[/itex] 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?
  2. jcsd
  3. Nov 2, 2012 #2

    Ray Vickson

    User Avatar
    Science Advisor
    Homework Helper

    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
    [tex] W(x) = 1 - \exp(-(x/a)^b),[/tex] (using a and b instead of θ and τ) we can solve W(x) = p as x = x(p), where
    [tex] x(p) = a\, \exp\left( \frac{\ln(-\ln(1-p))}{b}\right).[/tex]
    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.

  4. Nov 4, 2012 #3
    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) = [itex]\theta[/itex](-LN(1-p))1/[itex]\tau[/itex], 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.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook