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: [email protected] For additional commands, e-mail: [email protected]
