Please explain how to reproduce that problem, so others can easily test it.

Load the spreadsheet I attached
On the RetCalc worksheet go to cell D9
Tools  GoalSeek
Set Formula Cell to $D$9 (should show that if you are on it)
For target Value set the amount shown in PV Required Balance 200000
variable Cell set to $B$6  (monthly draw)
Press OK
You will get "*Goal Seek not successful.
No exact value found.
Insert closest value (R 0)?*"
The same spreadsheet loaded into Excel gets an answer no problem (3584)

Trust that helps
Bruce Sobey


On Wed, May 15, 2013 at 6:49 PM, <bugzilla-daemon@freedesktop.org> wrote:

>  ign_christian <ign_christian@yahoo.com> changed bug 64499<https://bugs.freedesktop.org/show_bug.cgi?id=64499>
>  What Removed Added  Status UNCONFIRMED NEEDINFO  Ever confirmed   1
>
>  *Comment # 1 <https://bugs.freedesktop.org/show_bug.cgi?id=64499#c1> on bug
> 64499 <https://bugs.freedesktop.org/show_bug.cgi?id=64499> from
> ign_christian <ign_christian@yahoo.com> *
>
> Please explain how to reproduce that problem, so others can easily test it.
>
>  ------------------------------
> You are receiving this mail because:
>
>    - You reported the bug.
>
>

I can confirm same result on LO 4.0.3.3 (Win7 Home Premium 32bit)

set status UNCONFIRMED -> need confirmation from MS Office user

Whiteboard: NeedsMicrosoftOffice

We need confirmation by someone with MS-Office.

Goalseeking is a bit of a black art; I'd be unsurprised if there were differences in the solutions we managed to find across suites & platforms :-)

Bruce, sorry for being an ubernoob. Could you post exact reproduce steps? Which file is opened in which format with which application.

This seems to be about excel but the test file is a ods file?

Hi Foss
I think I initially submitted an xls file (RetirementCalc.xls), but I may
have saved it as an ods file as well.  It makes no difference they both
give the same problem when goal seek is run in LibreOffice.  It is a file
to calculate how much one can draw (cell B6) to reach the target at the
number of years.  To do this one runs goal seek (or makes many successive
changes to cell B6).  Running goal see (Tools, Goal Seek)one would tell it
to set Formula cell D7, Target value = 0, Variable cell B6.  However
LibreOffice (like OpenOffice before it) does not find a solution. However
Excel does not have a problem withe this - In fact the excel file has a
macro to do this automatically, which I was intending to move to
LibreOffice.

I made a small test file doing something similar and it ran Goal Seek fine,
so there is some problem with the convergence algorithm in this case.

Hope this helps
Bruce


On Thu, Nov 14, 2013 at 9:09 PM, <bugzilla-daemon@freedesktop.org> wrote:

>  Foss <foss@openmailbox.org> changed bug 64499<https://bugs.freedesktop.org/show_bug.cgi?id=64499>
>  What Removed Added  Status UNCONFIRMED NEEDINFO  Ever confirmed   1
>
>  *Comment # 6 <https://bugs.freedesktop.org/show_bug.cgi?id=64499#c6> on
> bug 64499 <https://bugs.freedesktop.org/show_bug.cgi?id=64499> from Foss
> <foss@openmailbox.org> *
>
> Bruce, sorry for being an ubernoob. Could you post exact reproduce steps? Which
> file is opened in which format with which application.
>
> This seems to be about excel but the test file is a ods file?
>
>  ------------------------------
> You are receiving this mail because:
>
>    - You reported the bug.
>
>

Bruce - Thanks for the update.

[Changing status -> UNCONFIRMED]

Created attachment 89341 [details]
XLS and screenshots showing behaviour under Excel Mac 2011 and LOv4132.

I can confirm the behaviour described in comment #2. I tested the provided under Crunchbang 11 x86_64 running LO v4.1.3.2 Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a. I then saved this file as an XLS and XLSX for testing under MacOS 10.6.8 running Word Mac 2011. Screenshots of what I am seeing are attached along with a copy of the XLS. The XLSX cause a "File is damaged, would you like to repair?" error in the indicated version of Word and even though it appeared to repair / work it is probably no more use than the XLS.

(In reply to comment #5)
> Goalseeking is a bit of a black art;

I do not profess to being a financial or formula whizz, but this would seem disheartening if true. Is it simply because there are multiple methods? I notice in the code (e.g., http://opengrok.libreoffice.org/xref/core/sc/source/core/data/documen4.cxx#51) that there are comments like:

> This function is used internally for the goal seek operation.  It uses the
> Regula Falsi (aka false position) algorithm to find a root of f(x).  The
> start value and the target value are to be given by the user in the
> goal seek dialog.  The f(x) in this case is defined as the formula in the
> formula cell minus target value.

... and on the web that there is also a secant method as well as others for determining / solving this type of equation. Presumably this is what is being referred to?

I just want to clarify what the goal seek is attempting to do in this case. It takes the formula in cell D9 and examines the references: A13 (start year); B7-1 (end year, b/c start is year 1 rather than 0); 4 (closing balance column). The goal seek is then telling Calc to adjust the value in B6 (monthly draw / salary) to determine a value that will result in the requested value (200,000) appearing as a closing balance at the 35 year mark. Is that right?

(In reply to comment #9)
> I tested the provided

I tested the initially provided ODS ...

(In reply to comment #9)
> XLS and screenshots showing behaviour under Word Mac 2011 and LOv4132.
> ...testing under MacOS 10.6.8 running Word Mac 2011.
> ... error in the indicated version of Word

Owen, did you test using Word or Excel? I believe OP mentioned that he was testing in Excel, specifically.

(In reply to comment #11)
> (In reply to comment #9)
> > XLS and screenshots showing behaviour under Word Mac 2011 and LOv4132.
> > ...testing under MacOS 10.6.8 running Word Mac 2011.
> > ... error in the indicated version of Word
> 
> Owen, did you test using Word or Excel? I believe OP mentioned that he was
> testing in Excel, specifically.

Oh good grief! Excel. Sorry for the mis-information. The screenshots are from Excel Mac 2011 and Calc v4.1.3.2. Thanks for the correction. I must have Word trauma.

(In reply to comment #9)
> 
> I can confirm the behaviour described in comment #2. I tested the provided
> under Crunchbang 11 x86_64 running LO v4.1.3.2 Build ID:
> ...
> I just want to clarify what the goal seek is attempting to do in this case.
> It takes the formula in cell D9 and examines the references: A13 (start
> year); B7-1 (end year, b/c start is year 1 rather than 0); 4 (closing
> balance column). The goal seek is then telling Calc to adjust the value in
> B6 (monthly draw / salary) to determine a value that will result in the
> requested value (200,000) appearing as a closing balance at the 35 year
> mark. Is that right?

Owen - do we need more information from OP?

(In reply to comment #13)
> Owen - do we need more information from OP?

No, I don't think so. This appears to be bug 37341 back again (which is mentioned in the comments of the code I linked upthread). Attachment 46880 [details] in that bug is a very clear example, that is once again not working. In that attachment:

1. Select F100.
2. Tools > Goal Seek...
3. Plug in "Target value" of 0; Variable cell E7.
4. Click OK > Goal seek failed.

That was the reported (as then) working test case, so this may be a regression. Selecting cell F110 gives the same result.

Reading through the comments in that bug, it does indeed appear to be some nightmare, perhaps related to a limit in the iterations.

Oh, I forgot to mention I tested attchment 46880 under Debian 7.3.0 x86_64 using v4.1.4.2 and Crunchbang 11 x86_64 using v4.2.0.4 (both website builds).

Jay, I am adding you to the CC list as you seem to have access to MS Office 2013. Could you test the attachments (save ODS to XLS/XLSX or use the XLS I have provided in the ZIP)? I think the goal seek facility in LO may have a problem, but it would be good to get a comparison with Excel 2013. Thanks.

Note also that both the ODS attachment in this bug and the attachment I link in comment 14 still return "Goal seek failed" under GNU/Linux using:

- v4.1.6.2 Build ID: 40ff705089295be5be0aae9b15123f687c05b0a
- v4.2.6.3 Build ID: 3fd416d4c6db7d3204c17ce57a1d70f6e531ee21
- v4.3.1.2 Build ID: 958349dc3b25111dbca392fbc281a05559ef6848
- v4.4.0.0.alpha0+ Build ID: 652b807658a54cd2ccd04ebc6900d2cf1ce85015 TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2014-09-05_01:32:46

Created attachment 105834 [details]
screenshot from excel 2013

Thanks Jay. I think this bug can be confirmed. It has oscillated back and forth between UNCONFIRMED and NEEDINFO enough. At the very least Calc should offer parity with Excel, in terms of Goal Seek. Status set to NEW.

Platform set to All/All as a result of comment 9, comment 15, and comment 17.

In the case of the use of the solver, I do not reproduce with version 4.3.2.0.0+ build at home under Ubuntu 14.04 x86-64. It works perfectly well (and find the solution as MS-Excel) if you choose the right options for the computation:
- solver: use one of the two linear solvers
- in Calc options, Calculate tab, uncheck "Precision as shown"; if it is checked, the linear solver says that the problem is not linear.

If you have the non-linear solvers installed, I think they are not suitable to solve other problems than optimization ones (minimization or maximization).

So it would be useful to know how GoalSeek performs its calculation. Perhaps it could better if GoalSeek did the same computations as the default linear solver.

Best regards. JBF

(In reply to comment #21)
> In the case of the use of the solver, I do not reproduce with version
> 4.3.2.0.0+ build at home under Ubuntu 14.04 x86-64. It works perfectly well
> (and find the solution as MS-Excel) 
> ...
> So it would be useful to know how GoalSeek performs its calculation. Perhaps
> it could better if GoalSeek did the same computations as the default linear
> solver.

Good point. I can also reproduce an accurate result using the Solver (linear algorithm) under GNU/Linux using:

- v4.1.6.2 Build ID: 40ff705089295be5be0aae9b15123f687c05b0a
- v4.2.6.3 Build ID: 3fd416d4c6db7d3204c17ce57a1d70f6e531ee21
- v4.3.1.2 Build ID: 958349dc3b25111dbca392fbc281a05559ef6848

... so the difference has evidently been there for a while. I don't trust NLPSolver results in v4.4 at present due to bug 82288.

Bug is confirmed and status is NEW -> Removing needAdvice tag.

Still reproducible with version 5.0.4.0+:
- Goalseek fails to find the solution
- both linear solvers work perfectly.

Best regards. JBF

I was here to file a bug report of my own, but saw this possible duplicate.

Yes, I can say with 100% certainty that this bug is still in the code!

Created attachment 120536 [details]
Failed Goal Seek - v5.0.2.2

This is on LibreOffice version 5.0.2.2.

Nothing to change to my comment #25 after testing with LO 5.3.1.0.0+ and current master.

Best regards. JBF

Created attachment 136168 [details]
Sample with another cell using INDEX instead OFFSET

I think issue comes from using volatile functions In the formula cell

On the first sample file, added a second cell for the formula using INDEX instead OFFSET:
=OFFSET($A$13;$B$7-1;4)            doesn't work
=INDEX($A$13:$E$500;$B$7;5)        works

I have found it with a question on the Spanish Ask using TODAY in a financial function in the cell with the formula, changing for a reference makes it works.

Goal seek returns the message "Goal Seek Fails" when I attempt to find a value.

Rechecked on 5 Oct 2019 on Windows version 6.3.1.2 and problem still persists. I tried using Solver.  All the supplied Solver engines except the experimental non-linear one get the correct result.  It has something to do with the Goalseek engine.

Rechecked again with the latest build 6.4.5.2  The situation is unchanged.  Solver manages to get a solution, but goalseek does not. The problem is that I want to use a macro, but there is an issue setting up solver macros to solve to a value in a macro.  Also a file that works in Excel should work in Calc to be compatible.

Solver works as it should if the solver engine is set in the options to LibreOffice Linear Solver.
Version:7.3.4.2 (x64)
Build: 728fec16bd5f605073805c3c9e7c4212a0120dc5
Environment: CPU threads 8; Windows 10.0 Build 19043
Use Interface: UI Render; Skia/Raster; VCL: win
Locale: en-GB
Misc: Calc: threaded