Bug 64499 - Goalseek does not get answer although Excel has no problem on same sheet saved in xls format
Summary: Goalseek does not get answer although Excel has no problem on same sheet save...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.2.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: GoalSeek
  Show dependency treegraph
 
Reported: 2013-05-12 16:26 UTC by Bruce Sobey
Modified: 2018-10-16 02:50 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Worksheet that Goalseek does not work on. (40.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-05-12 16:26 UTC, Bruce Sobey
Details
XLS and screenshots showing behaviour under Excel Mac 2011 and LOv4132. (512.56 KB, application/zip)
2013-11-17 00:32 UTC, Owen Genat (retired)
Details
screenshot from excel 2013 (166.49 KB, image/png)
2014-09-06 11:21 UTC, Yousuf Philips (jay) (retired)
Details
Failed Goal Seek - v5.0.2.2 (209.55 KB, image/png)
2015-11-14 10:36 UTC, AK
Details
Sample with another cell using INDEX instead OFFSET (40.51 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-09-11 11:21 UTC, m.a.riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Bruce Sobey 2013-05-12 16:26:10 UTC
Created attachment 79200 [details]
Worksheet that Goalseek does not work on.

In a spreadsheet goalseek and solver does not work although the same sheet works in Excel.  I firstly removed all unused sheets - no effect. Then saved it back in xls format and Excel still found the answer without a problem.  Sheet attached. Note these are excel macros and I have not sorted out for LibreOffice so was first doing manually to get working. Goalseek cell D9 to 200 000 by changing cell B6
Comment 1 ign_christian 2013-05-15 16:49:40 UTC
Please explain how to reproduce that problem, so others can easily test it.
Comment 2 Bruce Sobey 2013-05-16 15:56:35 UTC
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.
>
>
Comment 3 ign_christian 2013-05-17 02:16:17 UTC
I can confirm same result on LO 4.0.3.3 (Win7 Home Premium 32bit)

set status UNCONFIRMED -> need confirmation from MS Office user
Comment 4 Robinson Tryon (qubit) 2013-11-14 06:21:23 UTC
Whiteboard: NeedsMicrosoftOffice

We need confirmation by someone with MS-Office.
Comment 5 Michael Meeks 2013-11-14 15:53:41 UTC
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 :-)
Comment 6 retired 2013-11-14 19:09:30 UTC
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?
Comment 7 Bruce Sobey 2013-11-15 16:46:50 UTC
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.
>
>
Comment 8 Robinson Tryon (qubit) 2013-11-15 16:59:35 UTC
Bruce - Thanks for the update.

[Changing status -> UNCONFIRMED]
Comment 9 Owen Genat (retired) 2013-11-17 00:32:18 UTC
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?
Comment 10 Owen Genat (retired) 2013-11-17 00:35:20 UTC
(In reply to comment #9)
> I tested the provided

I tested the initially provided ODS ...
Comment 11 Robinson Tryon (qubit) 2013-11-17 01:04:58 UTC
(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.
Comment 12 Owen Genat (retired) 2013-11-17 05:51:18 UTC
(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.
Comment 13 Robinson Tryon (qubit) 2014-02-03 15:25:13 UTC
(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?
Comment 14 Owen Genat (retired) 2014-02-07 14:51:04 UTC
(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.
Comment 15 Owen Genat (retired) 2014-02-07 14:54:03 UTC
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).
Comment 16 QA Administrators 2014-09-03 21:32:50 UTC Comment hidden (obsolete)
Comment 17 Owen Genat (retired) 2014-09-06 08:00:39 UTC
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
Comment 18 Yousuf Philips (jay) (retired) 2014-09-06 11:21:56 UTC
Created attachment 105834 [details]
screenshot from excel 2013
Comment 19 Owen Genat (retired) 2014-09-07 07:27:43 UTC
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.
Comment 20 Owen Genat (retired) 2014-09-07 07:30:11 UTC
Platform set to All/All as a result of comment 9, comment 15, and comment 17.
Comment 21 Jean-Baptiste Faure 2014-09-07 11:23:08 UTC
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
Comment 22 Owen Genat (retired) 2014-09-08 09:54:30 UTC
(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.
Comment 23 Robinson Tryon (qubit) 2014-09-25 21:46:37 UTC
Bug is confirmed and status is NEW -> Removing needAdvice tag.
Comment 24 QA Administrators 2015-10-14 19:56:53 UTC Comment hidden (obsolete)
Comment 25 Jean-Baptiste Faure 2015-10-18 15:54:09 UTC
Still reproducible with version 5.0.4.0+:
- Goalseek fails to find the solution
- both linear solvers work perfectly.

Best regards. JBF
Comment 26 AK 2015-11-14 10:33:17 UTC
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!
Comment 27 AK 2015-11-14 10:36:00 UTC
Created attachment 120536 [details]
Failed Goal Seek - v5.0.2.2

This is on LibreOffice version 5.0.2.2.
Comment 28 QA Administrators 2017-01-03 19:35:56 UTC Comment hidden (obsolete)
Comment 29 Jean-Baptiste Faure 2017-02-04 11:48:53 UTC
Nothing to change to my comment #25 after testing with LO 5.3.1.0.0+ and current master.

Best regards. JBF
Comment 30 m.a.riosv 2017-09-11 11:21:55 UTC
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.
Comment 31 kcooper 2017-10-15 21:02:52 UTC
Goal seek returns the message "Goal Seek Fails" when I attempt to find a value.
Comment 32 QA Administrators 2018-10-16 02:50:39 UTC
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug