Hi, Debraj Roy!
I must say that I don't use too much What If Analysis or Solver except for having an idea towards which values involved cell should go to. As NARAYANK991 wrote, if it's mathematically solvable why using Excel features instead of Excel formulas?
BTW, are you surprised about Redmond guys developing strange things or at least not logical at a first glance? I'm not at all, in fact it's what I'd expect from them.
The Goal Seeker method has nearly nothing customizable and it's not affected by the Options, Formulas, Calculation Options, Enable Iterative Calculation, and changing Max Iterations No. or Max Change.
The Solver method lets you use three variants (GRG Nonlinear, Simplex LP and Evolutionary) even only the first works with this example when I'd expected that second one were more accurate. Here you have in Options more parameters (Constraints Precision, Omit Integer Constraints, an Integer Optimality which I supposed would do the job but didn't, and for GRG Convergency and others). Despite of this, playing with all parameters, setting Optimality to 0, Convergency to 1E-9, and all you can imagine, the same value 12,00...0751 displayed in A4).
Conclusion? It works up to a certain extent which we'd go on ignoring it as first Goal Seek is uncustomizable and second Solver is reluctant to get customized. For a general idea of WTF (what the function
) should a cell value approach go ahead with these built-in features. For critical calculations, well you can always use any suitable numeric method as Newton-Raphson, Min Squares, Succesive Approximations, etc., so always insert a worksheet Work in those workbooks and use it whenever any strange calculated value is returned by Excel.
Hope it helps.
Regards!
