Bugzilla 47598 - Improved formula evaluator number comparison

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@798771 13f79535-47bb-0310-9956-ffa450edef68

7 files changed, 715 insertions, 30 deletions

diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestEqualEval.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestEqualEval.java
index 19d1fa1fbc..3e71107b76 100644
--- a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestEqualEval.java
+++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestEqualEval.java
@@ -94,10 +94,10 @@ public final class TestEqualEval extends TestCase {
 
 	/**
 	 * Bug 47198 involved a formula "-A1=0" where cell A1 was 0.0.
-	 * Excel evaluates "-A1=0" to TRUE, not because it thinks -0.0==0.0 
+	 * Excel evaluates "-A1=0" to TRUE, not because it thinks -0.0==0.0
 	 * but because "-A1" evaluated to +0.0
 	 * <p/>
-	 * Note - the original diagnosis of bug 47198 was that 
+	 * Note - the original diagnosis of bug 47198 was that
 	 * "Excel considers -0.0 to be equal to 0.0" which is NQR
 	 * See {@link TestMinusZeroResult} for more specific tests regarding -0.0.
 	 */
@@ -114,4 +114,19 @@ public final class TestEqualEval extends TestCase {
 			throw new AssertionFailedError("Identified bug 47198: -0.0 != 0.0");
 		}
 	}
+
+	public void testRounding_bug47598() {
+		double x = 1+1.0028-0.9973; // should be 1.0055, but has IEEE rounding
+		assertFalse(x == 1.0055);
+
+		NumberEval a = new NumberEval(x);
+		NumberEval b = new NumberEval(1.0055);
+		assertEquals("1.0055", b.getStringValue());
+
+		Eval[] args = { a, b, };
+		BoolEval result = (BoolEval) EqualEval.instance.evaluate(args, 0, (short) 0);
+		if (!result.getBooleanValue()) {
+			throw new AssertionFailedError("Identified bug 47598: 1+1.0028-0.9973 != 1.0055");
+		}
+	}
 }
diff --git a/src/testcases/org/apache/poi/ss/util/AllSSUtilTests.java b/src/testcases/org/apache/poi/ss/util/AllSSUtilTests.java
index 9da4c2ca36..49bdfadfa5 100644
--- a/src/testcases/org/apache/poi/ss/util/AllSSUtilTests.java
+++ b/src/testcases/org/apache/poi/ss/util/AllSSUtilTests.java
@@ -21,13 +21,15 @@ import junit.framework.Test;
 import junit.framework.TestSuite;
 /**
  * Test suite for <tt>org.apache.poi.ss.util</tt>
- * 
+ *
  * @author Josh Micich
  */
 public final class AllSSUtilTests {
-    public static Test suite() {
+	public static Test suite() {
 		TestSuite result = new TestSuite(AllSSUtilTests.class.getName());
 		result.addTestSuite(TestCellReference.class);
+		result.addTestSuite(TestExpandedDouble.class);
+		result.addTestSuite(TestNumberComparer.class);
 		result.addTestSuite(TestNumberToTextConverter.class);
 		result.addTestSuite(TestRegion.class);
 		return result;
diff --git a/src/testcases/org/apache/poi/ss/util/NumberComparingSpreadsheetGenerator.java b/src/testcases/org/apache/poi/ss/util/NumberComparingSpreadsheetGenerator.java
new file mode 100644
index 0000000000..d04bf402ec
--- /dev/null
+++ b/src/testcases/org/apache/poi/ss/util/NumberComparingSpreadsheetGenerator.java
@@ -0,0 +1,155 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.util;
+
+import java.io.File;
+import java.io.FileOutputStream;
+import java.io.IOException;
+
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFCellStyle;
+import org.apache.poi.hssf.usermodel.HSSFFont;
+import org.apache.poi.hssf.usermodel.HSSFRichTextString;
+import org.apache.poi.hssf.usermodel.HSSFRow;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.util.NumberComparisonExamples.ComparisonExample;
+import org.apache.poi.util.HexDump;
+
+/**
+ * Creates a spreadsheet that checks Excel's comparison of various IEEE double values.
+ * The class {@link NumberComparisonExamples} contains specific comparison examples
+ * (along with their expected results) that get encoded into rows of the spreadsheet.
+ * Each example is checked with a formula (in column I) that displays either "OK" or
+ * "ERROR" depending on whether actual results match those expected.
+ *
+ * @author Josh Micich
+ */
+public class NumberComparingSpreadsheetGenerator {
+
+	private static final class SheetWriter {
+
+		private final HSSFSheet _sheet;
+		private int _rowIndex;
+
+		public SheetWriter(HSSFWorkbook wb) {
+			HSSFSheet sheet = wb.createSheet("Sheet1");
+
+			writeHeaderRow(wb, sheet);
+			_sheet = sheet;
+			_rowIndex = 1;
+		}
+
+		public void addTestRow(double a, double b, int expResult) {
+			writeDataRow(_sheet, _rowIndex++, a, b, expResult);
+		}
+	}
+
+	private static void writeHeaderCell(HSSFRow row, int i, String text, HSSFCellStyle style) {
+		HSSFCell cell = row.createCell(i);
+		cell.setCellValue(new HSSFRichTextString(text));
+		cell.setCellStyle(style);
+	}
+	static void writeHeaderRow(HSSFWorkbook wb, HSSFSheet sheet) {
+		sheet.setColumnWidth(0, 6000);
+		sheet.setColumnWidth(1, 6000);
+		sheet.setColumnWidth(2, 3600);
+		sheet.setColumnWidth(3, 3600);
+		sheet.setColumnWidth(4, 2400);
+		sheet.setColumnWidth(5, 2400);
+		sheet.setColumnWidth(6, 2400);
+		sheet.setColumnWidth(7, 2400);
+		sheet.setColumnWidth(8, 2400);
+		HSSFRow row = sheet.createRow(0);
+		HSSFCellStyle style = wb.createCellStyle();
+		HSSFFont font = wb.createFont();
+		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
+		style.setFont(font);
+		writeHeaderCell(row, 0, "Raw Long Bits A", style);
+		writeHeaderCell(row, 1, "Raw Long Bits B", style);
+		writeHeaderCell(row, 2, "Value A", style);
+		writeHeaderCell(row, 3, "Value B", style);
+		writeHeaderCell(row, 4, "Exp Cmp", style);
+		writeHeaderCell(row, 5, "LT", style);
+		writeHeaderCell(row, 6, "EQ", style);
+		writeHeaderCell(row, 7, "GT", style);
+		writeHeaderCell(row, 8, "Check", style);
+	}
+	/**
+	 * Fills a spreadsheet row with one comparison example. The two numeric values are written to
+	 * columns C and D. Columns (F, G and H) respectively get formulas ("v0<v1", "v0=v1", "v0>v1"),
+	 * which will be evaluated by Excel. Column D gets the expected comparison result. Column I
+	 * gets a formula to check that Excel's comparison results match that predicted in column D.
+	 *
+	 * @param v0 the first value to be compared
+	 * @param v1 the second value to be compared
+	 * @param expRes expected comparison result (-1, 0, or +1)
+	 */
+	static void writeDataRow(HSSFSheet sheet, int rowIx, double v0, double v1, int expRes) {
+		HSSFRow row = sheet.createRow(rowIx);
+
+		int rowNum = rowIx + 1;
+
+
+		row.createCell(0).setCellValue(formatDoubleAsHex(v0));
+		row.createCell(1).setCellValue(formatDoubleAsHex(v1));
+		row.createCell(2).setCellValue(v0);
+		row.createCell(3).setCellValue(v1);
+		row.createCell(4).setCellValue(expRes < 0 ? "LT" : expRes > 0 ? "GT" : "EQ");
+		row.createCell(5).setCellFormula("C" + rowNum + "<" + "D" + rowNum);
+		row.createCell(6).setCellFormula("C" + rowNum + "=" + "D" + rowNum);
+		row.createCell(7).setCellFormula("C" + rowNum + ">" + "D" + rowNum);
+		// TODO - bug elsewhere in POI - something wrong with encoding of NOT() function
+		String frm = "if(or(" +
+			"and(E#='LT', F#      , G#=FALSE, H#=FALSE)," +
+			"and(E#='EQ', F#=FALSE, G#      , H#=FALSE)," +
+			"and(E#='GT', F#=FALSE, G#=FALSE, H#      )" +
+			"), 'OK', 'error')"	;
+		row.createCell(8).setCellFormula(frm.replaceAll("#", String.valueOf(rowNum)).replace('\'', '"'));
+	}
+
+	private static String formatDoubleAsHex(double d) {
+		long l = Double.doubleToLongBits(d);
+		StringBuilder sb = new StringBuilder(20);
+		sb.append(HexDump.longToHex(l)).append('L');
+		return sb.toString();
+	}
+
+	public static void main(String[] args) {
+
+		HSSFWorkbook wb = new HSSFWorkbook();
+		SheetWriter sw = new SheetWriter(wb);
+		ComparisonExample[] ces = NumberComparisonExamples.getComparisonExamples();
+		for (int i = 0; i < ces.length; i++) {
+			ComparisonExample ce = ces[i];
+			sw.addTestRow(ce.getA(), ce.getB(), ce.getExpectedResult());
+		}
+
+
+		File outputFile = new File("ExcelNumberCompare.xls");
+
+		try {
+			FileOutputStream os = new FileOutputStream(outputFile);
+			wb.write(os);
+			os.close();
+		} catch (IOException e) {
+			throw new RuntimeException(e);
+		}
+		System.out.println("Finished writing '" + outputFile.getAbsolutePath() + "'");
+	}
+}
diff --git a/src/testcases/org/apache/poi/ss/util/NumberComparisonExamples.java b/src/testcases/org/apache/poi/ss/util/NumberComparisonExamples.java
new file mode 100644
index 0000000000..265e40d8c9
--- /dev/null
+++ b/src/testcases/org/apache/poi/ss/util/NumberComparisonExamples.java
@@ -0,0 +1,182 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.util;
+
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * Contains specific examples of <tt>double</tt> value pairs and their comparison result according to Excel.
+ *
+ * @author Josh Micich
+ */
+final class NumberComparisonExamples {
+
+	private NumberComparisonExamples() {
+		// no instances of this class
+	}
+
+	/**
+	 * represents one comparison test case
+	 */
+	public static final class ComparisonExample {
+		private final long _rawBitsA;
+		private final long _rawBitsB;
+		private final int _expectedResult;
+
+		public ComparisonExample(long rawBitsA, long rawBitsB, int expectedResult) {
+			_rawBitsA = rawBitsA;
+			_rawBitsB = rawBitsB;
+			_expectedResult = expectedResult;
+		}
+
+		public double getA() {
+			return Double.longBitsToDouble(_rawBitsA);
+		}
+		public double getB() {
+			return Double.longBitsToDouble(_rawBitsB);
+		}
+		public double getNegA() {
+			return -Double.longBitsToDouble(_rawBitsA);
+		}
+		public double getNegB() {
+			return -Double.longBitsToDouble(_rawBitsB);
+		}
+		public int getExpectedResult() {
+			return _expectedResult;
+		}
+	}
+
+	private static final ComparisonExample[] examples = initExamples();
+
+	private static ComparisonExample[] initExamples() {
+
+		List<ComparisonExample> temp = new ArrayList<ComparisonExample>();
+
+		addStepTransition(temp, 0x4010000000000005L);
+		addStepTransition(temp, 0x4010000000000010L);
+		addStepTransition(temp, 0x401000000000001CL);
+
+		addStepTransition(temp, 0x403CE0FFFFFFFFF1L);
+
+		addStepTransition(temp, 0x5010000000000006L);
+		addStepTransition(temp, 0x5010000000000010L);
+		addStepTransition(temp, 0x501000000000001AL);
+
+		addStepTransition(temp, 0x544CE6345CF32018L);
+		addStepTransition(temp, 0x544CE6345CF3205AL);
+		addStepTransition(temp, 0x544CE6345CF3209CL);
+		addStepTransition(temp, 0x544CE6345CF320DEL);
+
+		addStepTransition(temp, 0x54B250001000101DL);
+		addStepTransition(temp, 0x54B2500010001050L);
+		addStepTransition(temp, 0x54B2500010001083L);
+
+		addStepTransition(temp, 0x6230100010001000L);
+		addStepTransition(temp, 0x6230100010001005L);
+		addStepTransition(temp, 0x623010001000100AL);
+
+		addStepTransition(temp, 0x7F50300020001011L);
+		addStepTransition(temp, 0x7F5030002000102BL);
+		addStepTransition(temp, 0x7F50300020001044L);
+
+
+		addStepTransition(temp, 0x2B2BFFFF1000102AL);
+		addStepTransition(temp, 0x2B2BFFFF10001079L);
+		addStepTransition(temp, 0x2B2BFFFF100010C8L);
+
+		addStepTransition(temp, 0x2B2BFF001000102DL);
+		addStepTransition(temp, 0x2B2BFF0010001035L);
+		addStepTransition(temp, 0x2B2BFF001000103DL);
+
+		addStepTransition(temp, 0x2B61800040002024L);
+		addStepTransition(temp, 0x2B61800040002055L);
+		addStepTransition(temp, 0x2B61800040002086L);
+
+
+		addStepTransition(temp, 0x008000000000000BL);
+		// just outside 'subnormal' range
+		addStepTransition(temp, 0x0010000000000007L);
+		addStepTransition(temp, 0x001000000000001BL);
+		addStepTransition(temp, 0x001000000000002FL);
+
+		for(ComparisonExample ce : new ComparisonExample[] {
+				// negative, and exponents differ by more than 1
+				ce(0xBF30000000000000L, 0xBE60000000000000L, -1),
+
+				// negative zero *is* less than positive zero, but not easy to get out of calculations
+				ce(0x0000000000000000L, 0x8000000000000000L, +1),
+				// subnormal numbers compare without rounding for some reason
+				ce(0x0000000000000000L, 0x0000000000000001L, -1),
+				ce(0x0008000000000000L, 0x0008000000000001L, -1),
+				ce(0x000FFFFFFFFFFFFFL, 0x000FFFFFFFFFFFFEL, +1),
+				ce(0x000FFFFFFFFFFFFBL, 0x000FFFFFFFFFFFFCL, -1),
+				ce(0x000FFFFFFFFFFFFBL, 0x000FFFFFFFFFFFFEL, -1),
+
+				// across subnormal threshold (some mistakes when close)
+				ce(0x000FFFFFFFFFFFFFL, 0x0010000000000000L, +1),
+				ce(0x000FFFFFFFFFFFFBL, 0x0010000000000007L, +1),
+				ce(0x000FFFFFFFFFFFFAL, 0x0010000000000007L, 0),
+
+				// when a bit further apart - normal results
+				ce(0x000FFFFFFFFFFFF9L, 0x0010000000000007L, -1),
+				ce(0x000FFFFFFFFFFFFAL, 0x0010000000000008L, -1),
+				ce(0x000FFFFFFFFFFFFBL, 0x0010000000000008L, -1),
+		}) {
+			temp.add(ce);
+		}
+
+		ComparisonExample[] result = new ComparisonExample[temp.size()];
+		temp.toArray(result);
+		return result;
+	}
+
+	private static ComparisonExample ce(long rawBitsA, long rawBitsB, int expectedResult) {
+		return new ComparisonExample(rawBitsA, rawBitsB, expectedResult);
+	}
+
+	private static void addStepTransition(List<ComparisonExample> temp, long rawBits) {
+		for(ComparisonExample ce : new ComparisonExample[] {
+				ce(rawBits-1, rawBits+0, 0),
+				ce(rawBits+0, rawBits+1, -1),
+				ce(rawBits+1, rawBits+2, 0),
+		}) {
+			temp.add(ce);
+		}
+
+	}
+
+	public static ComparisonExample[] getComparisonExamples() {
+		return examples.clone();
+	}
+
+	public static ComparisonExample[] getComparisonExamples2() {
+		ComparisonExample[] result = examples.clone();
+
+		for (int i = 0; i < result.length; i++) {
+			int ha = ("a"+i).hashCode();
+			double a = ha * Math.pow(0.75, ha % 100);
+			int hb = ("b"+i).hashCode();
+			double b = hb * Math.pow(0.75, hb % 100);
+
+			result[i] = new ComparisonExample(Double.doubleToLongBits(a), Double.doubleToLongBits(b), Double.compare(a, b));
+		}
+
+		return result;
+	}
+}
diff --git a/src/testcases/org/apache/poi/ss/util/NumberToTextConversionExamples.java b/src/testcases/org/apache/poi/ss/util/NumberToTextConversionExamples.java
index fb80010c5a..91f9c41429 100644
--- a/src/testcases/org/apache/poi/ss/util/NumberToTextConversionExamples.java
+++ b/src/testcases/org/apache/poi/ss/util/NumberToTextConversionExamples.java
@@ -95,17 +95,17 @@ final class NumberToTextConversionExamples {
 		ec(0x4087A00000000000L, "756.0", "756"),
 		ec(0x401E3D70A3D70A3DL, "7.56", "7.56"),
 
-//		ec(0x405EDD3C07FB4C8CL, "123.4567890123455",  "123.456789012345"),
+		ec(0x405EDD3C07FB4C8CL, "123.4567890123455",  "123.456789012345"),
 		ec(0x405EDD3C07FB4C99L, "123.45678901234568", "123.456789012346"),
 		ec(0x405EDD3C07FB4CAEL, "123.45678901234598", "123.456789012346"),
 		ec(0x4132D687E3DF2180L, "1234567.8901234567", "1234567.89012346"),
 
-//		ec(0x3F543A272D9E0E49L, "0.001234567890123455",  "0.00123456789012345"),
+		ec(0x3F543A272D9E0E49L, "0.001234567890123455",  "0.00123456789012345"),
 		ec(0x3F543A272D9E0E4AL, "0.0012345678901234552", "0.00123456789012346"),
 		ec(0x3F543A272D9E0E55L, "0.0012345678901234576", "0.00123456789012346"),
 		ec(0x3F543A272D9E0E72L, "0.0012345678901234639", "0.00123456789012346"),
 		ec(0x3F543A272D9E0E76L, "0.0012345678901234647", "0.00123456789012346"),
-//		ec(0x3F543A272D9E0E77L, "0.001234567890123465",  "0.00123456789012346"),
+		ec(0x3F543A272D9E0E77L, "0.001234567890123465",  "0.00123456789012346"),
 
 		ec(0x3F543A272D9E0E78L, "0.0012345678901234652", "0.00123456789012347"),
 
@@ -121,11 +121,11 @@ final class NumberToTextConversionExamples {
 		ec(0x544CE6345CF32121L, "1.2345678901234751E98",  "1.23456789012348E+98"),
 
 
-//		ec(0x54820FE0BA17F5E9L, "1.23456789012355E99",    "1.2345678901236E+99"),
+		ec(0x54820FE0BA17F5E9L, "1.23456789012355E99",    "1.2345678901236E+99"),
 		ec(0x54820FE0BA17F5EAL, "1.2345678901235502E99",  "1.2345678901236E+99"),
-//		ec(0x54820FE0BA17F784L, "1.2345678901236498E99",  "1.2345678901237E+99"),
+		ec(0x54820FE0BA17F784L, "1.2345678901236498E99",  "1.2345678901237E+99"),
 		ec(0x54820FE0BA17F785L, "1.23456789012365E99",    "1.2345678901237E+99"),
-//		ec(0x54820FE0BA17F920L, "1.2345678901237498E99",  "1.2345678901238E+99"),
+		ec(0x54820FE0BA17F920L, "1.2345678901237498E99",  "1.2345678901238E+99"),
 		ec(0x54820FE0BA17F921L, "1.23456789012375E99",    "1.2345678901238E+99"),
 
 
@@ -137,52 +137,52 @@ final class NumberToTextConversionExamples {
 		ec(0x547D42AEA2879F2AL,"9.999999999999995E98",  "9.99999999999999E+98"),
 		ec(0x547D42AEA2879F2BL,"9.999999999999996E98",  "1E+99"),
 		ec(0x547D42AEA287A0A0L,"1.0000000000000449E99", "1E+99"),
-//		ec(0x547D42AEA287A0A1L,"1.000000000000045E99",  "1.0000000000001E+99"),
+		ec(0x547D42AEA287A0A1L,"1.000000000000045E99",  "1.0000000000001E+99"),
 		ec(0x547D42AEA287A3D8L,"1.0000000000001449E99", "1.0000000000001E+99"),
-//		ec(0x547D42AEA287A3D9L,"1.000000000000145E99",  "1.0000000000002E+99"),
+		ec(0x547D42AEA287A3D9L,"1.000000000000145E99",  "1.0000000000002E+99"),
 		ec(0x547D42AEA287A710L,"1.000000000000245E99",  "1.0000000000002E+99"),
-//		ec(0x547D42AEA287A711L,"1.0000000000002451E99", "1.0000000000003E+99"),
+		ec(0x547D42AEA287A711L,"1.0000000000002451E99", "1.0000000000003E+99"),
 
 
 		ec(0x54B249AD2594C2F9L,"9.999999999999744E99",  "9.9999999999997E+99"),
-//		ec(0x54B249AD2594C2FAL,"9.999999999999746E99",  "9.9999999999998E+99"),
+		ec(0x54B249AD2594C2FAL,"9.999999999999746E99",  "9.9999999999998E+99"),
 		ec(0x54B249AD2594C32DL,"9.999999999999845E99",  "9.9999999999998E+99"),
-//		ec(0x54B249AD2594C32EL,"9.999999999999847E99",  "9.9999999999999E+99"),
+		ec(0x54B249AD2594C32EL,"9.999999999999847E99",  "9.9999999999999E+99"),
 		ec(0x54B249AD2594C360L,"9.999999999999944E99",  "9.9999999999999E+99"),
-//		ec(0x54B249AD2594C361L,"9.999999999999946E99",  "1E+100"),
+		ec(0x54B249AD2594C361L,"9.999999999999946E99",  "1E+100"),
 		ec(0x54B249AD2594C464L,"1.0000000000000449E100","1E+100"),
-//		ec(0x54B249AD2594C465L,"1.000000000000045E100", "1.0000000000001E+100"),
+		ec(0x54B249AD2594C465L,"1.000000000000045E100", "1.0000000000001E+100"),
 		ec(0x54B249AD2594C667L,"1.000000000000145E100", "1.0000000000001E+100"),
-//		ec(0x54B249AD2594C668L,"1.0000000000001451E100","1.0000000000002E+100"),
+		ec(0x54B249AD2594C668L,"1.0000000000001451E100","1.0000000000002E+100"),
 		ec(0x54B249AD2594C86AL,"1.000000000000245E100", "1.0000000000002E+100"),
-//		ec(0x54B249AD2594C86BL,"1.0000000000002452E100","1.0000000000003E+100"),
+		ec(0x54B249AD2594C86BL,"1.0000000000002452E100","1.0000000000003E+100"),
 
 
 		ec(0x2B95DF5CA28EF4A8L,"1.0000000000000251E-98","1.00000000000003E-98"),
-//		ec(0x2B95DF5CA28EF4A7L,"1.000000000000025E-98", "1.00000000000002E-98"),
+		ec(0x2B95DF5CA28EF4A7L,"1.000000000000025E-98", "1.00000000000002E-98"),
 		ec(0x2B95DF5CA28EF46AL,"1.000000000000015E-98", "1.00000000000002E-98"),
 		ec(0x2B95DF5CA28EF469L,"1.0000000000000149E-98","1.00000000000001E-98"),
 		ec(0x2B95DF5CA28EF42DL,"1.0000000000000051E-98","1.00000000000001E-98"),
-//		ec(0x2B95DF5CA28EF42CL,"1.000000000000005E-98", "1E-98"),
-//		ec(0x2B95DF5CA28EF3ECL,"9.999999999999946E-99", "1E-98"),
+		ec(0x2B95DF5CA28EF42CL,"1.000000000000005E-98", "1E-98"),
+		ec(0x2B95DF5CA28EF3ECL,"9.999999999999946E-99", "1E-98"),
 		ec(0x2B95DF5CA28EF3EBL,"9.999999999999944E-99", "9.9999999999999E-99"),
-//		ec(0x2B95DF5CA28EF3AEL,"9.999999999999845E-99", "9.9999999999999E-99"),
+		ec(0x2B95DF5CA28EF3AEL,"9.999999999999845E-99", "9.9999999999999E-99"),
 		ec(0x2B95DF5CA28EF3ADL,"9.999999999999843E-99", "9.9999999999998E-99"),
-//		ec(0x2B95DF5CA28EF371L,"9.999999999999746E-99", "9.9999999999998E-99"),
+		ec(0x2B95DF5CA28EF371L,"9.999999999999746E-99", "9.9999999999998E-99"),
 		ec(0x2B95DF5CA28EF370L,"9.999999999999744E-99", "9.9999999999997E-99"),
 
 
-//		ec(0x2B617F7D4ED8C7F5L,"1.000000000000245E-99", "1.0000000000003E-99"),
+		ec(0x2B617F7D4ED8C7F5L,"1.000000000000245E-99", "1.0000000000003E-99"),
 		ec(0x2B617F7D4ED8C7F4L,"1.0000000000002449E-99","1.0000000000002E-99"),
-//		ec(0x2B617F7D4ED8C609L,"1.0000000000001452E-99","1.0000000000002E-99"),
+		ec(0x2B617F7D4ED8C609L,"1.0000000000001452E-99","1.0000000000002E-99"),
 		ec(0x2B617F7D4ED8C608L,"1.000000000000145E-99", "1.0000000000001E-99"),
-//		ec(0x2B617F7D4ED8C41CL,"1.000000000000045E-99", "1.0000000000001E-99"),
+		ec(0x2B617F7D4ED8C41CL,"1.000000000000045E-99", "1.0000000000001E-99"),
 		ec(0x2B617F7D4ED8C41BL,"1.0000000000000449E-99","1E-99"),
-//		ec(0x2B617F7D4ED8C323L,"9.999999999999945E-100","1E-99"),
+		ec(0x2B617F7D4ED8C323L,"9.999999999999945E-100","1E-99"),
 		ec(0x2B617F7D4ED8C322L,"9.999999999999943E-100","9.9999999999999E-100"),
-//		ec(0x2B617F7D4ED8C2F2L,"9.999999999999846E-100","9.9999999999999E-100"),
+		ec(0x2B617F7D4ED8C2F2L,"9.999999999999846E-100","9.9999999999999E-100"),
 		ec(0x2B617F7D4ED8C2F1L,"9.999999999999844E-100","9.9999999999998E-100"),
-//		ec(0x2B617F7D4ED8C2C1L,"9.999999999999746E-100","9.9999999999998E-100"),
+		ec(0x2B617F7D4ED8C2C1L,"9.999999999999746E-100","9.9999999999998E-100"),
 		ec(0x2B617F7D4ED8C2C0L,"9.999999999999744E-100","9.9999999999997E-100"),
 
 
diff --git a/src/testcases/org/apache/poi/ss/util/TestExpandedDouble.java b/src/testcases/org/apache/poi/ss/util/TestExpandedDouble.java
new file mode 100644
index 0000000000..dd524fdb86
--- /dev/null
+++ b/src/testcases/org/apache/poi/ss/util/TestExpandedDouble.java
@@ -0,0 +1,225 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.util;
+
+import java.math.BigDecimal;
+import java.math.BigInteger;
+
+import junit.framework.AssertionFailedError;
+import junit.framework.TestCase;
+
+import org.apache.poi.util.HexDump;
+/**
+ * Tests for {@link ExpandedDouble}
+ *
+ * @author Josh Micich
+ */
+public final class TestExpandedDouble extends TestCase {
+	private static final BigInteger BIG_POW_10 = BigInteger.valueOf(1000000000);
+
+	public void testNegative() {
+		ExpandedDouble hd = new ExpandedDouble(0xC010000000000000L);
+
+		if (hd.getBinaryExponent() == -2046) {
+			throw new AssertionFailedError("identified bug - sign bit not masked out of exponent");
+		}
+		assertEquals(2, hd.getBinaryExponent());
+		BigInteger frac = hd.getSignificand();
+		assertEquals(64, frac.bitLength());
+		assertEquals(1, frac.bitCount());
+	}
+
+	public void testSubnormal() {
+		ExpandedDouble hd = new ExpandedDouble(0x0000000000000001L);
+
+		if (hd.getBinaryExponent() == -1023) {
+			throw new AssertionFailedError("identified bug - subnormal numbers not decoded properly");
+		}
+		assertEquals(-1086, hd.getBinaryExponent());
+		BigInteger frac = hd.getSignificand();
+		assertEquals(64, frac.bitLength());
+		assertEquals(1, frac.bitCount());
+	}
+
+	/**
+	 * Tests specific values for conversion from {@link ExpandedDouble} to {@link NormalisedDecimal} and back
+	 */
+	public void testRoundTripShifting() {
+		long[] rawValues = {
+				0x4010000000000004L,
+				0x7010000000000004L,
+				0x1010000000000004L,
+				0x0010000000000001L, // near lowest normal number
+				0x0010000000000000L, // lowest normal number
+				0x000FFFFFFFFFFFFFL, // highest subnormal number
+				0x0008000000000000L, // subnormal number
+
+				0xC010000000000004L,
+				0xE230100010001004L,
+				0x403CE0FFFFFFFFF2L,
+				0x0000000000000001L, // smallest non-zero number (subnormal)
+				0x6230100010000FFEL,
+				0x6230100010000FFFL,
+				0x6230100010001000L,
+				0x403CE0FFFFFFFFF0L, // has single digit round trip error
+				0x2B2BFFFF10001079L,
+		};
+		boolean success = true;
+		for (int i = 0; i < rawValues.length; i++) {
+			success &= confirmRoundTrip(i, rawValues[i]);
+		}
+		if (!success) {
+			throw new AssertionFailedError("One or more test examples failed.  See stderr.");
+		}
+	}
+	public static boolean confirmRoundTrip(int i, long rawBitsA) {
+		double a = Double.longBitsToDouble(rawBitsA);
+		if (a == 0.0) {
+			// Can't represent 0.0 or -0.0 with NormalisedDecimal
+			return true;
+		}
+		ExpandedDouble ed1;
+		NormalisedDecimal nd2;
+		ExpandedDouble ed3;
+		try {
+			ed1 = new ExpandedDouble(rawBitsA);
+			nd2 = ed1.normaliseBaseTen();
+			checkNormaliseBaseTenResult(ed1, nd2);
+
+			ed3 = nd2.normaliseBaseTwo();
+		} catch (RuntimeException e) {
+			System.err.println("example[" + i + "] ("
+					+ formatDoubleAsHex(a) + ") exception:");
+			e.printStackTrace();
+			return false;
+		}
+		if (ed3.getBinaryExponent() != ed1.getBinaryExponent()) {
+			System.err.println("example[" + i + "] ("
+					+ formatDoubleAsHex(a) + ") bin exp mismatch");
+			return false;
+		}
+		BigInteger diff = ed3.getSignificand().subtract(ed1.getSignificand()).abs();
+		if (diff.signum() == 0) {
+			return true;
+		}
+		// original quantity only has 53 bits of precision
+		// these quantities may have errors in the 64th bit, which hopefully don't make any difference
+
+		if (diff.bitLength() < 2) {
+			// errors in the 64th bit happen from time to time
+			// this is well below the 53 bits of precision required
+			return true;
+		}
+
+		// but bigger errors are a concern
+		System.out.println("example[" + i + "] ("
+				+ formatDoubleAsHex(a) + ") frac mismatch: " + diff.toString());
+
+		for (int j=-2; j<3; j++) {
+			System.out.println((j<0?"":"+") + j + ": " + getNearby(ed1, j));
+		}
+		for (int j=-2; j<3; j++) {
+			System.out.println((j<0?"":"+") + j + ": " + getNearby(nd2, j));
+		}
+
+
+		return false;
+	}
+
+	public static String getBaseDecimal(ExpandedDouble hd) {
+		int gg = 64 - hd.getBinaryExponent() - 1;
+		BigDecimal bd = new BigDecimal(hd.getSignificand()).divide(new BigDecimal(BigInteger.ONE.shiftLeft(gg)));
+		int excessPrecision = bd.precision() - 23;
+		if (excessPrecision > 0) {
+			bd = bd.setScale(bd.scale() - excessPrecision, BigDecimal.ROUND_HALF_UP);
+		}
+		return bd.unscaledValue().toString();
+	}
+	public static BigInteger getNearby(NormalisedDecimal md, int offset) {
+		BigInteger frac = md.composeFrac();
+		int be = frac.bitLength() - 24 - 1;
+		int sc = frac.bitLength() - 64;
+		return getNearby(frac.shiftRight(sc), be, offset);
+	}
+
+	public static BigInteger getNearby(ExpandedDouble hd, int offset) {
+		return getNearby(hd.getSignificand(), hd.getBinaryExponent(), offset);
+	}
+
+	private static BigInteger getNearby(BigInteger significand, int binExp, int offset) {
+		int nExtraBits = 1;
+		int nDec = (int) Math.round(3.0 + (64+nExtraBits) * Math.log10(2.0));
+		BigInteger newFrac = significand.shiftLeft(nExtraBits).add(BigInteger.valueOf(offset));
+
+		int gg = 64 + nExtraBits - binExp - 1;
+
+		BigDecimal bd = new BigDecimal(newFrac);
+		if (gg > 0) {
+			bd = bd.divide(new BigDecimal(BigInteger.ONE.shiftLeft(gg)));
+		} else {
+			BigInteger frac = newFrac;
+			while (frac.bitLength() + binExp < 180) {
+				frac = frac.multiply(BigInteger.TEN);
+			}
+			int binaryExp = binExp - newFrac.bitLength() + frac.bitLength();
+
+			bd = new BigDecimal( frac.shiftRight(frac.bitLength()-binaryExp-1));
+		}
+		int excessPrecision = bd.precision() - nDec;
+		if (excessPrecision > 0) {
+			bd = bd.setScale(bd.scale() - excessPrecision, BigDecimal.ROUND_HALF_UP);
+		}
+		return bd.unscaledValue();
+	}
+
+	private static void checkNormaliseBaseTenResult(ExpandedDouble orig, NormalisedDecimal result) {
+		String sigDigs = result.getSignificantDecimalDigits();
+		BigInteger frac = orig.getSignificand();
+		while (frac.bitLength() + orig.getBinaryExponent() < 200) {
+			frac = frac.multiply(BIG_POW_10);
+		}
+		int binaryExp = orig.getBinaryExponent() - orig.getSignificand().bitLength();
+
+		String origDigs = frac.shiftLeft(binaryExp+1).toString(10);
+
+		if (!origDigs.startsWith(sigDigs)) {
+			throw new AssertionFailedError("Expected '" + origDigs + "' but got '" + sigDigs + "'.");
+		}
+
+		double dO = Double.parseDouble("0." + origDigs.substring(sigDigs.length()));
+		double d1 = Double.parseDouble(result.getFractionalPart().toPlainString());
+		BigInteger subDigsO = BigInteger.valueOf((int) (dO * 32768 + 0.5));
+		BigInteger subDigsB = BigInteger.valueOf((int) (d1 * 32768 + 0.5));
+
+		if (subDigsO.equals(subDigsB)) {
+			return;
+		}
+		BigInteger diff = subDigsB.subtract(subDigsO).abs();
+		if (diff.intValue() > 100) {
+			// 100/32768 ~= 0.003
+			throw new AssertionFailedError("minor mistake");
+		}
+	}
+
+	private static String formatDoubleAsHex(double d) {
+		long l = Double.doubleToLongBits(d);
+		StringBuilder sb = new StringBuilder(20);
+		sb.append(HexDump.longToHex(l)).append('L');
+		return sb.toString();
+	}
+}
diff --git a/src/testcases/org/apache/poi/ss/util/TestNumberComparer.java b/src/testcases/org/apache/poi/ss/util/TestNumberComparer.java
new file mode 100644
index 0000000000..7c3d87e99a
--- /dev/null
+++ b/src/testcases/org/apache/poi/ss/util/TestNumberComparer.java
@@ -0,0 +1,106 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.util;
+
+import junit.framework.AssertionFailedError;
+import junit.framework.TestCase;
+
+import org.apache.poi.ss.util.NumberComparisonExamples.ComparisonExample;
+import org.apache.poi.util.HexDump;
+/**
+ * Tests for {@link NumberComparer}
+ *
+ * @author Josh Micich
+ */
+public final class TestNumberComparer extends TestCase {
+
+	public void testAllComparisonExamples() {
+		ComparisonExample[] examples = NumberComparisonExamples.getComparisonExamples();
+		boolean success = true;
+
+		for(int i=0;i<examples.length; i++) {
+			ComparisonExample ce = examples[i];
+			success &= confirm(i, ce.getA(), ce.getB(), +ce.getExpectedResult());
+			success &= confirm(i, ce.getB(), ce.getA(), -ce.getExpectedResult());
+			success &= confirm(i, ce.getNegA(), ce.getNegB(), -ce.getExpectedResult());
+			success &= confirm(i, ce.getNegB(), ce.getNegA(), +ce.getExpectedResult());
+		}
+		if (!success) {
+			throw new AssertionFailedError("One or more cases failed.  See stderr");
+		}
+	}
+
+	public void testRoundTripOnComparisonExamples() {
+		ComparisonExample[] examples = NumberComparisonExamples.getComparisonExamples();
+		boolean success = true;
+		for(int i=0;i<examples.length; i++) {
+			ComparisonExample ce = examples[i];
+			success &= confirmRoundTrip(i, ce.getA());
+			success &= confirmRoundTrip(i, ce.getNegA());
+			success &= confirmRoundTrip(i, ce.getB());
+			success &= confirmRoundTrip(i, ce.getNegB());
+		}
+		if (!success) {
+			throw new AssertionFailedError("One or more cases failed.  See stderr");
+		}
+
+	}
+
+	private boolean confirmRoundTrip(int i, double a) {
+		return TestExpandedDouble.confirmRoundTrip(i, Double.doubleToLongBits(a));
+	}
+
+	/**
+	 * The actual example from bug 47598
+	 */
+	public void testSpecificExampleA() {
+		double a = 0.06-0.01;
+		double b = 0.05;
+		assertFalse(a == b);
+		assertEquals(0, NumberComparer.compare(a, b));
+	}
+
+	/**
+	 * The example from the nabble posting
+	 */
+	public void testSpecificExampleB() {
+		double a = 1+1.0028-0.9973;
+		double b = 1.0055;
+		assertFalse(a == b);
+		assertEquals(0, NumberComparer.compare(a, b));
+	}
+
+	private static boolean confirm(int i, double a, double b, int expRes) {
+		int actRes = NumberComparer.compare(a, b);
+
+		int sgnActRes = actRes < 0 ? -1 : actRes > 0 ? +1 : 0;
+		if (sgnActRes != expRes) {
+			System.err.println("Mismatch example[" + i + "] ("
+					+ formatDoubleAsHex(a) + ", " + formatDoubleAsHex(b) + ") expected "
+					+ expRes + " but got " + sgnActRes);
+			return false;
+		}
+		return true;
+	}
+	private static String formatDoubleAsHex(double d) {
+		long l = Double.doubleToLongBits(d);
+		StringBuilder sb = new StringBuilder(20);
+		sb.append(HexDump.longToHex(l)).append('L');
+		return sb.toString();
+	}
+}