Bug 106210

Summary:	=EFFECT(0,4) returns error
Product:	LibreOffice	Reporter:	raal <raal>
Component:	Calc	Assignee:	Winfried Donkers <winfrieddonkers>
Status:	VERIFIED FIXED
Severity:	normal	CC:	raal, winfrieddonkers
Priority:	medium
Version:	unspecified
Hardware:	All
OS:	All
Whiteboard:	target:5.4.0
Crash report or crash signature:		Regression By:

Description raal 2017-02-27 08:53:05 UTC

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

Comment 1 raal 2017-02-27 08:54:38 UTC

Winfried confirmed

Comment 2 Commit Notification 2017-03-06 15:52:52 UTC

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.

Comment 3 Luke 2017-03-06 19:29:35 UTC

In Excel, 
=EFFECT(0,4)

Results in
#NUM!

Your change will break Excel interoperability unless it's only applied to ODF documents.

Comment 4 Luke 2017-03-06 19:48:40 UTC

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.

Comment 5 Winfried Donkers 2017-03-07 11:22:38 UTC

(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 :-)

Comment 6 Winfried Donkers 2017-03-07 11:24:38 UTC

(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.

Comment 7 raal 2017-03-09 17:19:11 UTC

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