Rounding problems and NumberToTextConverter

[RickyS <rs...@gmail.com>]

Hello,

I've long tried to find a solution to the problem of different results
calculated by POI and Excel for the same formula.
My test case is as follows:

A1 = 1.0028 (constant, which I write dinamically to the sheet)
A2 = 0.9973 (constant, which I write dinamically to the sheet)
A3 = 1 + A1 - A2 (this evaluates to 1.0055 in Excel)
B1 = 1.0055 (constant)

the troubled formula is:
B2 = IF($A$3=B1;1;0)

so this compares the constant 1.0055 with the A3 results and then gives 1 or
0. 
In Excel, you get 1. In POI 3.2, you get 0. You see here the well-known
rounding anomalies strike very hard, since the function IF() itself is
discontinuous, so a small perturbation of A3's value leads to a totally
different result. 
The only way I've found to fix this is to modify A3's formula to ROUND(1 +
A1 - A2; n) with n = 4..15, 
but the problem is I don't want to modify the Excel sheet which is provided
by our customer, even if B2's formula is not very numerically stable by
definition (equality between doubles, one of which calculated, the other
constant), Excel does it that way, so we need POI to do it the same way.

So, with POI I investiganted the values using both getNumericCellValue() and
evaluate() for A3 and I see the value is indeed 1.0054999999999996 so here
we go, rounding anomalies between java doubles and Excel.

Later, I learned of commit r722284 which solves bug 46156, claiming that the
new class NumberToTextConverter is "now used for all number to text
conversion used during formula rendering and formula evaluation". 
So I switched to POI 3.5-beta6 and gave it a spin. And indeed,
NumberToTextConverter.toText(cellValue.getNumberValue()) now gives 1.0055 so
it apparently behaves exactly like Excel. 
However, for some reason I still get 0 as a final result for B2 instead of
1.

But why? Shouldn't POI now round the intermediate value (A3) with
NumberToTextConverter before feeding it to the final formula (B2)? Maybe I'm
not getting what the commit message is saying.
A guy in that bugzilla entry proposed to do something along those lines:

String resultText = NumberToTextConverter.render(messyResult);
double roundedResult = Double.parseDouble(resultText);

is that not what POI 3.5 does? 
If it's not, is there a way to modify POI to achieve that? Maybe
NumberEval.getNumberValue is the method I should try to hack, but will that
work? I have no knowledge of POI's internals and no time allotted to
actually study it. I just need POI to mimic Excel as close as possible.

If someone can shed some light on this, I'd really appreciate that.


Thanks,
RickyS


-- 
View this message in context: http://www.nabble.com/Rounding-problems-and-NumberToTextConverter-tp24388983p24388983.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Rounding problems and NumberToTextConverter

[Dave Fisher <da...@jmlafferty.com>]

Quite an accomplishment....

Dave

On Jul 28, 2009, at 9:29 PM, Josh Micich wrote:

> This is now fixed:
>
> https://issues.apache.org/bugzilla/show_bug.cgi?id=47598
>
> POI should now reproduce Excel behaviour for all comparison operators.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Rounding problems and NumberToTextConverter

[Josh Micich <jo...@gmail.com>]

This is now fixed:

https://issues.apache.org/bugzilla/show_bug.cgi?id=47598

POI should now reproduce Excel behaviour for all comparison operators.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org