On Dec 29, 5:31 pm, Allamarein <matteo.diplom...@gmail.com> wrote: > Thanks Ray.You wrote: > > "You might be much better off looking at the problem of minimizing L = > | > F(77.8)-.05| + |F(80.3) - .5| + |F(82.1)-.95|, which you can > implement > as follows: minimize (z1 + z2 + z3), subject to the constraints z1 >= > F(77.8)-.05, z1 >= .05-F(77.8), etc. In this formulation the > variables > are m,s,z1,z2,z3, and if you have the quantity F(77.8)-.05 = > NORMDIST(77.8,m,s,TRUE)-0.05 in some cell such as B7, you just have > the two z1-constraints as z1 >= B7 (better written as z1-B7 >= 0)and > z1 >= -B7 (better written as z1+B7 >= 0). Note that if you use a x- > error measure and seek the least absolute-deviation fit, you would > have m - 3.2365s - .05 in cell B7, so you would have a purely linear > optimization problem. Solver would handle this by a much better > algorithm if you go to the "choose linear model" menu option. > However, > in your F(77.8)-.05 type of error, the resulting problem is not > linear, so EXCEL might not get a super-accurate solution." > > I didn't understand. In particular what is z and why I need that. > If you have time, please give me details about this procedure. > (it also might be that tomorrow morning it will be clearer..anyway I > wanted to post also to regreat you.) > Let's define L as: > L(s,m) = > abs(NORMDIST(77.8,m,s,TRUE)-0.05)+abs(NORMDIST(80.3,m,s,TRUE)-0.5)+abs(NORMDIST(82.1,m,s,TRUE)-0.95) > and solve that by Excel solver, handling m and s.
See remarks below.
> My costraints will be s>0 in order to avoid error that NORMDIST should > give me.
This is a bad idea: *strict* inequality constraints lead to ill-posed optimization problems. Anyway, Solver does not permit strict inequalities as far as I know. You need non-strict inequalities, such as s >= 0.001 or something similar.
> How you have suggested, I should handle on Solver options. > Probably my statement of the problem is based on not linear approach. > Apart that, T = 0.0012 could be a sufficient accurate zero for my > purposes. > In 240 computated cases the highest values that I found is T = > 0.01159. > Like I wrote, I will read better your post tomorrow. > In the meanwhile, very thanks, Ray.
You cannot just enter abs(NORMDIST(77.8,m,s,TRUE)-0.05)+abs(NORMDIST(80.3,m,s,TRUE)-0.5)+abs(NORMDIST(82.1,m,s,TRUE)-0.95) into EXCEL and ask Solver to minimize it: Solver wants smooth functions having at least continuous derivatives---even though it does not actually _use_ derivatives! The absolute-value function is not differentiable, because it fails to have a derivative at zero. In other words, you cannot just solve the problem by setting the derivatives to zero! Therefore, you need some way to represent absolute-values in the minimization, but in terms of differentiable functions. That is where the zi come in.
Look at this little example. How can we represent an absolute-value such as |-9|, but in terms of smooth functions and smooth constraints? Consider the problem min z, subject to z >= -9 and z >= -(-9) = 9. Its solution is z = 9, which is just |-9|. In general, min z subject to z >= f(x) and z >= -f(x) has the solution z = |f(x)|. The objective z is certainly a smooth function, as are each of the separate constraints z >= f(x) and z >= -f(x), at least if f(x) is itself a smooth function. So, even though |f(x)| is not a smooth function, we can "smoothify" it using the variable z and the two constraints. Variable z will represent the absolute value at the optimal solution. So if we want to minimize |f1(x)| + |f2(x)| + ... + |fm(x)|, which is a *non-smooth* problem, we can convert it to a smooth problem: min z1 + z2 + ... + zm, subject to z1 >= f1(x), z1 >= -f1(x), z2 >= f2(x), z2 >= - f2(x), ...., zm >= fm(x), zm >= -fm(x). We can now use a standard constrained optimization package (such as the Solver) to handle the problem.