Bug 68200 - Rate function error
Summary: Rate function error
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 5.2.3-FINAL
Hardware: PC All
: P3 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-11-23 10:37 UTC by TongHuang
Modified: 2023-11-24 13:46 UTC (History)
1 user (show)


Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description TongHuang 2023-11-23 10:37:01 UTC 
Hi Team,

Here some cases, `calculateRate` will return `Double.NaN` -> [#NUM!],
(https://github.com/apache/poi/blob/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Rate.java#L99)

But it can get the right result in Numbers

case1:

RATE(22,30000,20000,-82257625,0,0.1)

Result: Double.NaN

whereas in Numbers I get: 0.35397960290713076

--

case2:

RATE(22,10000,10000,-313562750,0,0.1)

Result: Double.NaN

whereas in Numbers I get: 0.35397960290713076
Comment 1 Axel Howind 2023-11-23 11:37:51 UTC 
I can confirm the result in Numbers, but I can also confirm that I get an error in Excel. In general we try to follow what Excel is doing.

Since it works the same as in Excel, I reduce the importance to P3 / normal.

How should we proceed here?
Comment 2 PJ Fanning 2023-11-23 20:19:00 UTC 
POI should try to match Excel results even if the Excel result is not ideal.
Comment 3 Axel Howind 2023-11-24 13:46:48 UTC 
As POI strives to reproduce Excel results, and Excel also returns an error for the example provided, we will not change the RATE function to match the results of another application's implementation.