Description: > SYNTAX: EFFECT( _Number_ rate ; _Integer_ payments ) > > CONSTRAINTS: rate >= 0; =EFFECT(0,4) returns error, so constraints > should be rate > 0 (without =) ? The same in excel : > https://support.office.com/en-US/article/EFFECT-function-910D4E4C-79E2-4009-95E6-507E04F11BC4 > If nominal_rate ≤ 0 or if npery < 1, EFFECT returns the #NUM! error > value. EFFECT is an ODFF1.2 function, the code currently does not apply the ODFF-constraints correctly. There is no reason why argument rate may not be 0. Actual Results: . Expected Results: . Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (Windows NT 6.1; rv:51.0) Gecko/20100101 Firefox/51.0
Winfried confirmed
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9a7b5d86aceb97fc9f3d36bee817d3d0fc976254 tdf#106210 Apply correct constraint for calc function EFFECTIVE. It will be available in 5.4.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
In Excel, =EFFECT(0,4) Results in #NUM! Your change will break Excel interoperability unless it's only applied to ODF documents.
Google Sheets, WPS Sheets, and Gnumeric all also produce an error with '=EFFECT(0,4)'. We should conform to the industry standard unless there is a good reason. The bug here is with the spec not out implementation.
(In reply to Luke from comment #3) > In Excel, > =EFFECT(0,4) > > Results in > #NUM! > > Your change will break Excel interoperability unless it's only applied to > ODF documents. It is a question of breaking ODFF compliance or Excel interoperability. In this case, adding an extra function EFFECT_XL that will produce an error for a mathematically and financially totally legitimate value for argument 'interest' will not make matters better. With current low interest rates, it is even a plus that Calc can calculate the nett annual interest for a nominal interest of 0 :-)
(In reply to Luke from comment #4) > Google Sheets, WPS Sheets, and Gnumeric all also produce an error with > '=EFFECT(0,4)'. We should conform to the industry standard unless there is a > good reason. > > The bug here is with the spec not out implementation. Then at least Gnumeric and Excel when saving to ods file format do not comply with ODFF. I don't see a bug in ODFF, although one could argue that negative interests should also be accepted.
verified in Version: 5.4.0.0.alpha0+ Build ID: a5a6694128728c48c1a8482450a21ad49025c40c CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk2; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-03-07_02:52:26