Jupiter6 said:
[...] What no one has mentioned is the SOLVER in Excel kicks ASS. [...]
Har, har. Just waited for a mention of that one :)
Excel's solver is good, I'll even say "world class", in a certain sense. But there are two crucial problems with it. (Also, make no mistake, CAS tool like Matlab, MathCAD, etc. will contain such solvers without exception. And there are free libraries of such methods too, for use in custom codes.)
Firstly, while it uses an algorithm which is one of the several well-proven for general optimization, all algorithms of that kind (gradient-based) suffer one theoretical boundary. A boundary which, unfortunatelly, frequently gets lost to a practicing engineer. This boundary is the following: gradient-based methods can find only a
local optimum of the goal, i.e. such which is optimal to any near-designs (those obtained by slight variations in design parameters), but without any guarantee to global optimality. Think of an x-y function plot where there are two "valleys" (or "humps"); gradient descent (or ascent) will give you one of them, depending on the starting point, without clue to the existence of the other, which may be better. Excel Solver's users see this effect when it gives different results with different initial cell values. Furthermore, if the goal to optimize is non-smooth (think of that x-y plot with a break in it, e.g. x-segment of undefined y-value), gradient descent methods guarantee exactly nothing for the final value they provide.
Secondly, while Excel's solver can be applied to anything that provides a value to a cell, like some complicated external add-on, thusfar I've seen in the wild, it was always used on the custom-made computations made in basic Excel itself; the number of design parameters ("by changing cells...") was usually single-digit at anyone case. This all amounts to an unusual observation: in the time Excel's Solver will take to crunch the problem, if it were written in e.g. Fortran instead, one could
brute force it, do a complete parameter sweep -- the only optimizing "method" guaranteed to give global best solution. (Back to x-y plot, brute force means: subdivide x-axis into problem-significant division -- e.g. airplane wing span into ~10 cm segments -- compute for each x, pick best. Don't think now "But I can do that in Excel too!" as I'm speaking of, say, 5-parameter combinations, each varied for every other, and a worksheet that will take a second or so to evaluate for one combination.)
In my masters thesis I was faced with exactly the problem described above: multi-parameter, multi-optima goal function, and non-smooth at many segments (and aircraft conceptual design computation, on basis of Raymer, Roskam, etc.) I was to thoroughly test a specialized optimizer, having 5-figures/year (or was it 6 for companies?) license fee, on this simple, but conceptually significant problem (the real one would have CFD/CSM simulations in the background, for high-fidelity results, but still displaying same general behavior).
And then I wondered: can I actually know what the best solutions were beforehand, so that I have a hard point to benchmark against? The answer, if I used Excel (or, frankly even Matlab or another CAS in this case) would be plain and simple --
no way. However, I estimated that I could write the basic computation (one set of params in, result out) in C for the same or less time it would take me with anything else (after all, there was just one way to handle these packs of formulas); and that afterwards I can brute force the problem in several hours using 8-16 CPUs (of which there were plenty to harness around the university labs). Using a scripting language (Perl at that time, today I'd use Python), it took me a
week to patch a script which will read XML setup of the computation -- with freely exchangable subproblems (e.g. different powerplant, drag models), physical units, nested parameter variations, consistency guards -- and use it to build out of the basic C code both the evaluation model for the 5-figure optimizer, and an MPI-parallel brute force sweeper for getting to the benchmark hardline.
In short, in less time than it would take me to wade through columns just to set up a spaghetti-like computation in Excel, I had a safe-guarded, readable, modular, and maintainable computation, and unlimited-CPU brute force sweeper guaranteeing global optima. In the end, it left me with spare time and means to play around with what-if scenarios, like "how about if a/c was hydrogen-fueled?" :) (I should also note that the call of the Dark Side for the user of the 5-figure heavyweight was not small -- it had a "charming" feature to happily accept an Excel spreadsheet as an evaluation model...)
--
Chusslove Illich (Часлав Илић)
