Weibull function in Excel
in [Functions]
|
Prev: Fill in Column with a series of repeating numbers
Next: Create Formula for Weighted Average in non-consecutive cells
From: Barbo on 4 Jun 2010 21:44 I have a survival dataset (see below). I want to fit to Weibull function S(t)=EXP(-alpha*Time^gamma). If I use Excel Solver, alpha=0.00367 and gamma=2.32. If I use a method tranforming probability to Ln(Ln(1/S(t)) which will be a linear function of Ln (t). But I got a different alpha and gamma. When I compared two alphas adn gammas, the Solver results had a better goodness of fit. What is the problem? THanks Month Probability of survival 0 1 1 1 2 0.92 3 0.90 4 0.88 5 0.83 6 0.76 7 0.74 8 0.69 9 0.57 10 0.48 11 0.39 12 0.27 13 0.27 14 0.13
From: Mike Middleton on 4 Jun 2010 23:36 Barbo - > What is the problem? < There is no problem. You should expect a difference. Your Solver method (which I prefer) minimizes sum of squared deviations between actual S and fitted S. The other method uses transformed values, so it does not yield a better fit. (Excel's trendline features use transformations to fit the logarithmic, power, and exponential functions. The approach appears to be a computational convenience. Better fits are obtained using Solver.) - Mike http://www.MikeMiddleton.com Mike(a)DecisionToolworks.com "Barbo" <Barbo(a)discussions.microsoft.com> wrote in message news:CFCE454F-D077-4526-BFE5-66902FFE0A12(a)microsoft.com... > I have a survival dataset (see below). I want to fit to Weibull function > S(t)=EXP(-alpha*Time^gamma). If I use Excel Solver, alpha=0.00367 and > gamma=2.32. If I use a method tranforming probability to Ln(Ln(1/S(t)) > which > will be a linear function of Ln (t). But I got a different alpha and > gamma. > When I compared two alphas adn gammas, the Solver results had a better > goodness of fit. What is the problem? THanks > > Month Probability of survival > 0 1 > 1 1 > 2 0.92 > 3 0.90 > 4 0.88 > 5 0.83 > 6 0.76 > 7 0.74 > 8 0.69 > 9 0.57 > 10 0.48 > 11 0.39 > 12 0.27 > 13 0.27 > 14 0.13 >
From: Barbo on 5 Jun 2010 09:51 Hi Mike, thanks for the reply. So if I want to find a function with best fit, adding a trendline is not an proper method. I got the weibull function estimates from someone else and then try to replicate the results in Excel. When I used Solver, most of the time I can get the parameter estimates with the best fit. I was told there is no feasible solution. How to properly use Solver? Thanks Barbo "Mike Middleton" wrote: > Barbo - > > > What is the problem? < > > There is no problem. You should expect a difference. > > Your Solver method (which I prefer) minimizes sum of squared deviations > between actual S and fitted S. > > The other method uses transformed values, so it does not yield a better fit. > > (Excel's trendline features use transformations to fit the logarithmic, > power, and exponential functions. The approach appears to be a computational > convenience. Better fits are obtained using Solver.) > > - Mike > http://www.MikeMiddleton.com > Mike(a)DecisionToolworks.com > > > "Barbo" <Barbo(a)discussions.microsoft.com> wrote in message > news:CFCE454F-D077-4526-BFE5-66902FFE0A12(a)microsoft.com... > > I have a survival dataset (see below). I want to fit to Weibull function > > S(t)=EXP(-alpha*Time^gamma). If I use Excel Solver, alpha=0.00367 and > > gamma=2.32. If I use a method tranforming probability to Ln(Ln(1/S(t)) > > which > > will be a linear function of Ln (t). But I got a different alpha and > > gamma. > > When I compared two alphas adn gammas, the Solver results had a better > > goodness of fit. What is the problem? THanks > > > > Month Probability of survival > > 0 1 > > 1 1 > > 2 0.92 > > 3 0.90 > > 4 0.88 > > 5 0.83 > > 6 0.76 > > 7 0.74 > > 8 0.69 > > 9 0.57 > > 10 0.48 > > 11 0.39 > > 12 0.27 > > 13 0.27 > > 14 0.13 > > > . >
From: Mike Middleton on 5 Jun 2010 19:19
Barbo - > How to properly use Solver? < Solver's success with nonlinear functions may depend on the initial values for the changing cells. For simple functions, like the Weibull, you may have some idea of reasonable initial values. - Mike http://www.MikeMiddleton.com Mike(a)DecisionToolworks.com "Barbo" <Barbo(a)discussions.microsoft.com> wrote in message news:BD2F239D-B24A-49BD-9274-B09576D1F741(a)microsoft.com... > Hi Mike, thanks for the reply. So if I want to find a function with best > fit, > adding a trendline is not an proper method. > > I got the weibull function estimates from someone else and then try to > replicate the results in Excel. When I used Solver, most of the time I can > get the parameter estimates with the best fit. I was told there is no > feasible solution. How to properly use Solver? Thanks > > Barbo > > > "Mike Middleton" wrote: > >> Barbo - >> >> > What is the problem? < >> >> There is no problem. You should expect a difference. >> >> Your Solver method (which I prefer) minimizes sum of squared deviations >> between actual S and fitted S. >> >> The other method uses transformed values, so it does not yield a better >> fit. >> >> (Excel's trendline features use transformations to fit the logarithmic, >> power, and exponential functions. The approach appears to be a >> computational >> convenience. Better fits are obtained using Solver.) >> >> - Mike >> http://www.MikeMiddleton.com >> Mike(a)DecisionToolworks.com >> >> >> "Barbo" <Barbo(a)discussions.microsoft.com> wrote in message >> news:CFCE454F-D077-4526-BFE5-66902FFE0A12(a)microsoft.com... >> > I have a survival dataset (see below). I want to fit to Weibull >> > function >> > S(t)=EXP(-alpha*Time^gamma). If I use Excel Solver, alpha=0.00367 and >> > gamma=2.32. If I use a method tranforming probability to Ln(Ln(1/S(t)) >> > which >> > will be a linear function of Ln (t). But I got a different alpha and >> > gamma. >> > When I compared two alphas adn gammas, the Solver results had a better >> > goodness of fit. What is the problem? THanks >> > >> > Month Probability of survival >> > 0 1 >> > 1 1 >> > 2 0.92 >> > 3 0.90 >> > 4 0.88 >> > 5 0.83 >> > 6 0.76 >> > 7 0.74 >> > 8 0.69 >> > 9 0.57 >> > 10 0.48 >> > 11 0.39 >> > 12 0.27 >> > 13 0.27 >> > 14 0.13 >> > >> . >> |