Hi,
I have submitted a patch for review:
https://gerrit.libreoffice.org/4327
To pull it, you can do:
git pull ssh://gerrit.libreoffice.org:29418/core refs/changes/27/4327/1
resolved fdo#65082 RATE function should not find roots <= -1
Limit RATE to roots > -1.0, which is what also Excel and now Gnumeric
do. If the Newton goal seek fails for the default guess value 0.1 try
other values.
This now also calculates the few remaining error cases of i#15090
attachment https://issues.apache.org/ooo/attachment.cgi?id=6528
correctly.
Change-Id: Ic62cc807626b4715035c3076f58f303b9020db5a
(cherry picked from commit 9ee7be4efb494351c4be096ffa04cdbd85cdc3d4)
---
M sc/source/core/tool/interpr2.cxx
1 file changed, 46 insertions(+), 13 deletions(-)
diff --git a/sc/source/core/tool/interpr2.cxx b/sc/source/core/tool/interpr2.cxx
index a25826c..713966f 100644
--- a/sc/source/core/tool/interpr2.cxx
+++ b/sc/source/core/tool/interpr2.cxx
@@ -1419,16 +1419,19 @@
fXnew = fX + 1.1 * SCdEpsilon; // move away from zero slope
else
fXnew = fX - fTerm / fTermDerivation;
- nCount++;
- // more accuracy not possible in oscillating cases
- bFound = (fabs(fXnew - fX) < SCdEpsilon);
- fX = fXnew;
+ nCount++;
+ // more accuracy not possible in oscillating cases
+ bFound = (fabs(fXnew - fX) < SCdEpsilon);
+ fX = fXnew;
}
}
// Gnumeric returns roots < -1, Excel gives an error in that cases,
// ODFF says nothing about it. Enable the statement, if you want Excel's
- // behavior
+ // behavior.
//bValid =(fX >=-1.0);
+ // Update 2013-06-17: Gnumeric (v1.12.2) doesn't return roots <= -1
+ // anymore.
+ bValid = (fX > -1.0);
}
else
{ // Nper is not an integer value.
@@ -1455,11 +1458,11 @@
fXnew = fX + 1.1 * SCdEpsilon; // move away from zero slope
else
fXnew = fX - fTerm / fTermDerivation;
- nCount++;
- // more accuracy not possible in oscillating cases
- bFound = (fabs(fXnew - fX) < SCdEpsilon);
- fX = fXnew;
- bValid = (fX >= -1.0); // otherwise pow(1.0+fX,fNper) will fail
+ nCount++;
+ // more accuracy not possible in oscillating cases
+ bFound = (fabs(fXnew - fX) < SCdEpsilon);
+ fX = fXnew;
+ bValid = (fX >= -1.0); // otherwise pow(1.0+fX,fNper) will fail
}
}
}
@@ -1473,14 +1476,18 @@
RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScZins" );
double fPv, fPayment, fNper;
// defaults for missing arguments, see ODFF spec
- double fFv = 0, fPayType = 0, fGuess = 0.1;
+ double fFv = 0, fPayType = 0, fGuess = 0.1, fOrigGuess = 0.1;
bool bValid = true;
+ bool bDefaultGuess = true;
nFuncFmtType = NUMBERFORMAT_PERCENT;
sal_uInt8 nParamCount = GetByte();
if ( !MustHaveParamCount( nParamCount, 3, 6 ) )
return;
if (nParamCount == 6)
- fGuess = GetDouble();
+ {
+ fOrigGuess = fGuess = GetDouble();
+ bDefaultGuess = false;
+ }
if (nParamCount >= 5)
fPayType = GetDouble();
if (nParamCount >= 4)
@@ -1498,7 +1505,33 @@
//if (fPayType != 0.0) fPayType = 1.0;
bValid = RateIteration(fNper, fPayment, fPv, fFv, fPayType, fGuess);
if (!bValid)
- SetError(errNoConvergence);
+ {
+ /* TODO: try also for specified guess values, not only default? As is,
+ * a specified 0.1 guess may be error result but a default 0.1 guess
+ * may succeed. On the other hand, using a different guess value than
+ * the specified one may not be desired, even if that didn't match. */
+ if (bDefaultGuess)
+ {
+ /* TODO: this is rather ugly, instead of looping over different
+ * guess values and doing a Newton goal seek for each we could
+ * first insert the values into the RATE equation to obtain a set
+ * of y values and then do a bisecting goal seek, possibly using
+ * different algorithms. */
+ double fX = fOrigGuess;
+ for (int nStep = 2; nStep <= 10 && !bValid; ++nStep)
+ {
+ fGuess = fX * nStep;
+ bValid = RateIteration( fNper, fPayment, fPv, fFv, fPayType, fGuess);
+ if (!bValid)
+ {
+ fGuess = fX / nStep;
+ bValid = RateIteration( fNper, fPayment, fPv, fFv, fPayType, fGuess);
+ }
+ }
+ }
+ if (!bValid)
+ SetError(errNoConvergence);
+ }
PushDouble(fGuess);
}
--
To view, visit https://gerrit.libreoffice.org/4327
To unsubscribe, visit https://gerrit.libreoffice.org/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ic62cc807626b4715035c3076f58f303b9020db5a
Gerrit-PatchSet: 1
Gerrit-Project: core
Gerrit-Branch: libreoffice-4-0
Gerrit-Owner: Eike Rathke <erack@redhat.com>
Context
- [PATCH libreoffice-4-0] resolved fdo#65082 RATE function should not find roots <= -1 · Eike Rathke (via Code Review)
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.