Worksheet Calculation Issue

[derouyag]

I will try to be thorough but brief. When calculating a formula within a cell in memory, the results returned are in a floating point value.

---

Cells C6:C10 on 'some worksheet' are formatted as 'Number(2)' Cells D2:D10 on 'some other worksheet' are formatted as 'Number(3)'

e.g. =if(sum('some worksheet'!C6:C10)=sum('some other worksheet'!D2:D10), "Okay", "Error")

sum('some worksheet'!C6:C10) = floating point number = fp1 sum('some other worksheet'!D2:D10) = floating point number = fp2

fp1 <> fp2

If the cell is formatted as 'General' then Epplus uses a floating point type or double to determine the two sums. Because it is floating point, the result will always be "Error" (Think about this in code. worksheet.calculate(), what is the result of the cell? if (cell.value = "Error")... Don't think about Excel.)

To the best of my knowledge, if I format the e.g. formula cell to Number(3) and have "set precision as displayed' ON, then the formula will show "Okay" or "Error"

If you do the same thing in an Excel spreadsheet (Excel not Epplus) the result will correctly display "Okay" or "Error" because it probably uses a Decimal type instead. Or maybe it knows the precision used in both SUMs.

[swmal]

All numbers are doubles in the context of EPPlus formula calculation, but we use double.Epsilon to avoid floating point issues when comparing doubles internally. How the cell is formatted does not affect how the numeric values are stored.

I assume that you are saying that two different ranges of integer values that sums up to the same integer value still are evaluated as different results?

Will try to recreate your problem with the formula you have supplied.

[swmal]

The following test is passing when I try, maybe I have misunderstood. Can you change it so it fails according to your description?

        [TestMethod]
        public void Issue379()
        {
            using (var pck = new ExcelPackage())
            {
                var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
                sheet1.Cells["A1"].Value = 1;
                sheet1.Cells["A2"].Value = 2;
                sheet1.Cells["A3"].Value = 3;
                var sheet2 = pck.Workbook.Worksheets.Add("Sheet2");
                sheet2.Cells["A1"].Value = 1;
                sheet2.Cells["A2"].Value = 2;
                sheet2.Cells["A3"].Value = 3;
                var sheet3 = pck.Workbook.Worksheets.Add("Sheet3");
                sheet3.Cells["A1"].Formula = "IF(SUM(Sheet1!A1:A3) = SUM(Sheet2!A1:A3), \"Equals\", \"Not Equals\")";
                sheet3.Calculate();

                Assert.AreEqual("Equals", sheet3.Cells["A1"].Value);
            }
        }

[derouyag]

Hello Mats,

I don’t get back to work till January 2nd. But, from what I see the test would pass and makes sense in the way it is written with the data used. The essence of the issue was around decimal places. I won’t be able to test till I get back to work, but try the edited version of the test with the setting described here.

Sheet1 cells A1 to A3 should be of type ‘General’

Sheet2 cells A1 to A4 should be of type ‘General’

Sheet3 cells A1 should be of type ‘General’

Workbook – set precision to displayed = off

Workbook – Auto calculate = on

If that does not fail, try one more:

Sheet1 cells A1 to A3 should be of type ‘Number’ decimal places 5

Sheet2 cells A1 to A4 should be of type ‘Number’ decimal places 5

Sheet3 cells A1 should be of type ‘General’

Workbook – set precision to displayed = off

Workbook – Auto calculate = on

Cell sum = 7.71467

What we would see is something like: 7.7146700000000001 or 7.71466999999999998

Gordon

From: Mats Alm notifications@github.com Sent: Friday, December 28, 2018 1:51 AM To: JanKallman/EPPlus EPPlus@noreply.github.com Cc: derouyag derouyag@shaw.ca; Author author@noreply.github.com Subject: Re: [JanKallman/EPPlus] Worksheet Calculation Issue (#379)

The following test is passing when I try, maybe I have misunderstood. Can you change it so it fails according to your description?

    [TestMethod]
    public void Issue379()
    {
        using (var pck = new ExcelPackage())
        {
            var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
            sheet1.Cells["A1"].Value = 1.113;
            sheet1.Cells["A2"].Value = 2.756;
            sheet1.Cells["A3"].Value = 3.84567;
            var sheet2 = pck.Workbook.Worksheets.Add("Sheet2");
            sheet2.Cells["A1"].Value = 1.113;
            sheet2.Cells["A2"].Value = 2.756;
            sheet2.Cells["A3"].Value = 1.92283;
            sheet2.Cells["A4"].Value = 1.92284;
            var sheet3 = pck.Workbook.Worksheets.Add("Sheet3");
            sheet3.Cells["A1"].Formula = "IF(SUM(Sheet1!A1:A3) = SUM(Sheet2!A1:A4), \"Equals\", \"Not Equals\")";
            sheet3.Calculate();

            Assert.AreEqual("Equals", sheet3.Cells["A1"].Value);
        }
    }

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/JanKallman/EPPlus/issues/379#issuecomment-450319978 , or mute the thread https://github.com/notifications/unsubscribe-auth/ArMkDuz-yuaE82BLZjbqTZ6kRKFCMF-5ks5u9dt8gaJpZM4ZfmbD . https://github.com/notifications/beacon/ArMkDvkkilesjnL0GiaTh4jrziL8pWy5ks5u9dt8gaJpZM4ZfmbD.gif

[swmal]

Ok, thanks. Just for clarity - EPPlus does not change any values in the cells or the way they are calculated due to the parameters you are mentioning, they are just forwarded to the spreadsheet program (i.e. Excel) to use. EPPlus just stores the numeric values as they are either set via its interface or read from the open office xml. So these parameters will not change the result of the calculation.

Will retry the test with your edited version of the test, but the logic in EPPlus is quite simple. If the difference between two decimal numbers is less than double.Epsilon they will be treated as equal, otherwise not.

[derouyag]

Hello Mats,

Happy New Year. I am back at work and have created a UnitTest for you.

Here is a realistic test of what we are doing. The test fails.

1. Original1 and Original2 are to resemble a file to be imported. The sum values of each of these worksheets are to be equal. Original1.Sum() = Original2.Sum()
2. When we import the file, the values are copied over to a master file. The master file ensures that the structure of the import file is valid and has not been changed. (The master file is created by a user.) We cannot change any of the formulas.
3. Each worksheet in the master file is recalculated.

I matched the exact design of the import file we have here; cell for cell, formula for formula, even value for value. I double checked the math. Floating Point is an issue which forced the test to fail.

Our user is asking the question... If "A" was entered, then why are we getting "B"? We tried explaining "floating point issues" but the user is non-technical. For instance A is input as 123.65, why would B be 123.649999995. Of course in simple terms this would not be the issue, there needs to be some calculations and a lot more numbers.

Hope this helps.

Gordon.

Snippet using System . Xml; using Microsoft . VisualStudio . TestTools . UnitTesting; using OfficeOpenXml; namespace UnitTestEpplus {     [ TestClass ] public class EpplusUnitTesting {         [ TestMethod ] public void FloatingPointTest()         { using ( var fpTestExcelPackage = new ExcelPackage ())             { // false or true should have the same results SetFullPrecisionAttribute(fpTestExcelPackage, true ); var original1 = fpTestExcelPackage . Workbook . Worksheets . Add( "Original1" );                 LoadOriginal1(original1); var original2 = fpTestExcelPackage . Workbook . Worksheets . Add( "Original2" );                 LoadOriginal2(original2); var new1 = fpTestExcelPackage . Workbook . Worksheets . Add( "New1" );                 CopyToNew1(original1, new1); var new2 = fpTestExcelPackage . Workbook . Worksheets . Add( "New2" );                 CopyToNew2(original2, new2); // add debug here Assert . AreEqual(new1 . Cells[ "O9" ] . Value, new2 . Cells[ "N39" ] . Value);             }         }

        [ TestMethod ] public void FullPrecisionIsOnTest()         { using ( var fpTestExcelPackage = new ExcelPackage ())             {                 SetFullPrecisionAttribute(fpTestExcelPackage, true ); var fullPrecisionIs = GetFullPrecisionSetting(fpTestExcelPackage); Assert . AreEqual(fullPrecisionIs, true );             }         }

        [ TestMethod ] public void FullPrecisionIsOffTest()         { using ( var fpTestExcelPackage = new ExcelPackage ())             {                 SetFullPrecisionAttribute(fpTestExcelPackage, false ); var fullPrecisionIs = GetFullPrecisionSetting(fpTestExcelPackage); Assert . AreEqual(fullPrecisionIs, false );             }         } private void LoadOriginal1( ExcelWorksheet ws)         { // Total = 353,364,037.80 //ws.Cells["C6:O9"].Style.Numberformat.Format = "(* #,##0);(* (#,##0);( \" - \"??);(@)"; ws . Cells[ "C6" ] . Value = 187947800.43 ;             ws . Cells[ "E7" ] . Value = 180000 ;             ws . Cells[ "G6" ] . Value = 2405751 ;             ws . Cells[ "G7" ] . Value = 3154409 ;             ws . Cells[ "I6" ] . Value = 106975302.7 ;             ws . Cells[ "L6" ] . Value = 12227493 ;             ws . Cells[ "M6" ] . Value = 39156589.67 ;             ws . Cells[ "M7" ] . Value = 991692 ;             ws . Cells[ "N6" ] . Value = 325000 ;         } private void LoadOriginal2( ExcelWorksheet ws)         { // Total = 353,364,037.80 //ws.Cells["C6:N39"].Style.Numberformat.Format = "( #,##0);( (#,##0);_( \" - \"??);(@_)"; ws . Cells[ "C6" ] . Value = 145989516 ;             ws . Cells[ "C9" ] . Value = 22368738 ;             ws . Cells[ "G9" ] . Value = 1245552 ;             ws . Cells[ "C12" ] . Value = 500000 ;             ws . Cells[ "E14" ] . Value = 180000 ;             ws . Cells[ "G15" ] . Value = 3154409 ;             ws . Cells[ "C16" ] . Value = 1500795.43 ;             ws . Cells[ "I19" ] . Value = 52406055.97 ;             ws . Cells[ "I20" ] . Value = 6529960.53 ;             ws . Cells[ "I25" ] . Value = 21627748.89 ;             ws . Cells[ "I26" ] . Value = 3308325.01 ;             ws . Cells[ "I28" ] . Value = 23103212.3 ;             ws . Cells[ "L30" ] . Value = 23817860 ;             ws . Cells[ "L31" ] . Value = 12317624.84 ;             ws . Cells[ "L32" ] . Value = 2934333.83 ;             ws . Cells[ "M32" ] . Value = 325000 ;             ws . Cells[ "K33" ] . Value = 5224172 ;             ws . Cells[ "L33" ] . Value = 991692 ;             ws . Cells[ "C38" ] . Value = 17588751 ;             ws . Cells[ "G38" ] . Value = 1160199 ;             ws . Cells[ "K38" ] . Value = 7003321 ;             ws . Cells[ "L38" ] . Value = 86771 ;         } private void CopyToNew1( ExcelWorksheet original, ExcelWorksheet newCopy)         {             newCopy . Cells[ "C6" ] . Value = original . Cells[ "C6" ] . Value;             newCopy . Cells[ "E7" ] . Value = original . Cells[ "E7" ] . Value;             newCopy . Cells[ "G6" ] . Value = original . Cells[ "G6" ] . Value;             newCopy . Cells[ "G7" ] . Value = original . Cells[ "G7" ] . Value;             newCopy . Cells[ "I6" ] . Value = original . Cells[ "I6" ] . Value;             newCopy . Cells[ "L6" ] . Value = original . Cells[ "L6" ] . Value;             newCopy . Cells[ "M6" ] . Value = original . Cells[ "M6" ] . Value;             newCopy . Cells[ "M7" ] . Value = original . Cells[ "M7" ] . Value;             newCopy . Cells[ "N6" ] . Value = original . Cells[ "N6" ] . Value; // Add formulas newCopy . Cells[ "O6" ] . Formula = "SUM(C6:N6)" ;             newCopy . Cells[ "O7" ] . Formula = "SUM(C7:N7)" ;             newCopy . Cells[ "O8" ] . Formula = "SUM(C8:N8)" ;

            newCopy . Cells[ "O9" ] . Formula = "SUM(O6:O8)" ;

            newCopy . Calculate();         } private void CopyToNew2( ExcelWorksheet original, ExcelWorksheet newCopy)         {             newCopy . Cells[ "C6" ] . Value = original . Cells[ "C6" ] . Value;             newCopy . Cells[ "C9" ] . Value = original . Cells[ "C9" ] . Value;             newCopy . Cells[ "G9" ] . Value = original . Cells[ "G9" ] . Value;             newCopy . Cells[ "C12" ] . Value = original . Cells[ "C12" ] . Value;             newCopy . Cells[ "E14" ] . Value = original . Cells[ "E14" ] . Value;             newCopy . Cells[ "G15" ] . Value = original . Cells[ "G15" ] . Value;             newCopy . Cells[ "C16" ] . Value = original . Cells[ "C16" ] . Value;             newCopy . Cells[ "I19" ] . Value = original . Cells[ "I19" ] . Value;             newCopy . Cells[ "I20" ] . Value = original . Cells[ "I20" ] . Value;             newCopy . Cells[ "I25" ] . Value = original . Cells[ "I25" ] . Value;             newCopy . Cells[ "I26" ] . Value = original . Cells[ "I26" ] . Value;             newCopy . Cells[ "I28" ] . Value = original . Cells[ "I28" ] . Value;             newCopy . Cells[ "L30" ] . Value = original . Cells[ "L30" ] . Value;             newCopy . Cells[ "L31" ] . Value = original . Cells[ "L31" ] . Value;             newCopy . Cells[ "L32" ] . Value = original . Cells[ "L32" ] . Value;             newCopy . Cells[ "M32" ] . Value = original . Cells[ "M32" ] . Value;             newCopy . Cells[ "K33" ] . Value = original . Cells[ "K33" ] . Value;             newCopy . Cells[ "L33" ] . Value = original . Cells[ "L33" ] . Value;             newCopy . Cells[ "C38" ] . Value = original . Cells[ "C38" ] . Value;             newCopy . Cells[ "G38" ] . Value = original . Cells[ "G38" ] . Value;             newCopy . Cells[ "K38" ] . Value = original . Cells[ "K38" ] . Value;             newCopy . Cells[ "L38" ] . Value = original . Cells[ "L38" ] . Value; // Add formulas newCopy . Cells[ "N6" ] . Formula = "SUM(C6:M6)" ;             newCopy . Cells[ "N7" ] . Formula = "SUM(C7:M7)" ;             newCopy . Cells[ "N9" ] . Formula = "SUM(C9:M9)" ;             newCopy . Cells[ "N10" ] . Formula = "SUM(C10:M10)" ;             newCopy . Cells[ "N11" ] . Formula = "SUM(C11:M11)" ;             newCopy . Cells[ "N12" ] . Formula = "SUM(C12:M12)" ;             newCopy . Cells[ "N13" ] . Formula = "SUM(C13:M13)" ;             newCopy . Cells[ "N14" ] . Formula = "SUM(C14:M14)" ;             newCopy . Cells[ "N15" ] . Formula = "SUM(C15:M15)" ;             newCopy . Cells[ "N16" ] . Formula = "SUM(C16:M16)" ;             newCopy . Cells[ "N17" ] . Formula = "SUM(C17:M17)" ;             newCopy . Cells[ "N19" ] . Formula = "SUM(C19:M19)" ;             newCopy . Cells[ "N20" ] . Formula = "SUM(C20:M20)" ;             newCopy . Cells[ "N21" ] . Formula = "SUM(C21:M21)" ;             newCopy . Cells[ "N22" ] . Formula = "SUM(C22:M22)" ;             newCopy . Cells[ "N24" ] . Formula = "SUM(C24:M24)" ;             newCopy . Cells[ "N25" ] . Formula = "SUM(C25:M25)" ;             newCopy . Cells[ "N26" ] . Formula = "SUM(C26:M26)" ;             newCopy . Cells[ "N27" ] . Formula = "SUM(C27:M27)" ;             newCopy . Cells[ "N28" ] . Formula = "SUM(C28:M28)" ;             newCopy . Cells[ "N30" ] . Formula = "SUM(C30:M30)" ;             newCopy . Cells[ "N31" ] . Formula = "SUM(C31:M31)" ;             newCopy . Cells[ "N32" ] . Formula = "SUM(C32:M32)" ;             newCopy . Cells[ "N33" ] . Formula = "SUM(C33:M33)" ;             newCopy . Cells[ "N35" ] . Formula = "SUM(C35:M35)" ;             newCopy . Cells[ "N36" ] . Formula = "SUM(C36:M36)" ;             newCopy . Cells[ "N37" ] . Formula = "SUM(C37:M37)" ;             newCopy . Cells[ "N38" ] . Formula = "SUM(C38:M38)" ;

            newCopy . Cells[ "N39" ] . Formula = "SUM(N6:N38)" ; //newCopy.Cells["N39:N39"].Style.Numberformat.Format = "0.00"; newCopy . Calculate();         } private const string CalculationPropertiesNodeName = "calcPr" ; private const string FullPrecisionAttributeName = "fullPrecision" ; private const string FullPrecisionOffValue = "0" ; private bool GetFullPrecisionSetting( ExcelPackage ep)         { var fullPrecisionAttr = GetAttribute(ep, FullPrecisionAttributeName); // if the tag does not exist, which is likely... then fullPrecision is expected // only when fullPrecision = "0", we know that it is turned off return (fullPrecisionAttr == null || fullPrecisionAttr . Value != FullPrecisionOffValue);         } private void SetFullPrecisionAttribute( ExcelPackage ep, bool fullPrecision)         { var attributeCollection = GetCalculationPropertiesAttributeCollection(ep); // If we can't get the attribute collection, then we can't set the attribute if (attributeCollection == null ) return ; var fullPrecisionAttr = GetAttribute(ep, FullPrecisionAttributeName); if (fullPrecision)             { if (fullPrecisionAttr != null )                     attributeCollection . Remove(fullPrecisionAttr);             } else { if (fullPrecisionAttr == null )                 { // easiest way to set the value of the attribute var newAttr = ep . Workbook . WorkbookXml . CreateAttribute(FullPrecisionAttributeName);                     newAttr . Value = FullPrecisionOffValue;

                    attributeCollection . Append(newAttr);                 } else {                     fullPrecisionAttr . Value = FullPrecisionOffValue;                 }             }         } private XmlAttribute GetAttribute( ExcelPackage ep, string attributeName)         { var attributeCollection = GetCalculationPropertiesAttributeCollection(ep); // return null if fullPrecision does not exists return attributeCollection ? [attributeName];         } private XmlAttributeCollection GetCalculationPropertiesAttributeCollection( ExcelPackage ep)         { var xmlNode = GetCalculationPropertiesNode(ep); if (xmlNode == null || xmlNode . Attributes ?. Count == 0 ) return null ; return xmlNode . Attributes;         } private XmlNode GetCalculationPropertiesNode( ExcelPackage ep)         { // At the bottom of the Workbook XML, this tag should exist var xmlNodeList = ep . Workbook . WorkbookXml . GetElementsByTagName(CalculationPropertiesNodeName); if (xmlNodeList . Count == 0 ) return null ; return xmlNodeList[ 0 ];         }     } }

From: "Mats Alm" notifications@github.com To: "JanKallman/EPPlus" EPPlus@noreply.github.com Cc: "derouyag" derouyag@shaw.ca, "Author" author@noreply.github.com Sent: Friday, December 28, 2018 2:34:00 PM Subject: Re: [JanKallman/EPPlus] Worksheet Calculation Issue (#379)

Ok, thanks. Just for clarity - EPPlus does not change any values in the cells or the way they are calculated due to the parameters you are mentioning, they are just forwarded to the spreadsheet program (i.e. Excel) to use. EPPlus just stores the numeric values as they are either set via its interface or read from the open office xml. So these parameters will not change the result of the calculation.

Will retry the test with your edited version of the test, but the logic in EPPlus is quite simple. If the difference between two decimal numbers is less than double.Epsilon they will be treated as equal, otherwise not.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub , or mute the thread .

[derouyag]

We have created a simplified test which will fail.

1.11 + 1 will fail 1.22 + 1 will fail

Using large whole numbers with a single value having .67 will pass. Using large whole numbers with the .67 split into .33 and .34 on two of the numbers will fail.

Part of the forcing to fail is to have mixed types; integer mixed with decimal. The resulting sum() will evaluate to a double with additional precision

Snippet [ TestMethod ] public void SimpleFloatingPointFailTest() { using ( var fpTestExcelPackage = new ExcelPackage ())     { // false or true should have the same results //SetFullPrecisionAttribute(fpTestExcelPackage, true); var test1 = fpTestExcelPackage . Workbook . Worksheets . Add( "Test1" );

        test1 . Cells[ "A1" ] . Value = 1.11 ;         test1 . Cells[ "A2" ] . Value = 1 ;

        test1 . Cells[ "A4" ] . Formula = "SUM(A1:A3)" ;         test1 . Cells[ "A4" ] . Calculate(); // add debug here Assert . AreEqual(test1 . Cells[ "A4" ] . Value, 2.11 );     } }

Gordon.

From: "derouyag" derouyag@shaw.ca To: "JanKallman" reply@reply.github.com Cc: "JanKallman/EPPlus" EPPlus@noreply.github.com, "Author" author@noreply.github.com Sent: Wednesday, January 2, 2019 12:48:07 PM Subject: Re: [JanKallman/EPPlus] Worksheet Calculation Issue (#379)

Hello Mats,

Happy New Year. I am back at work and have created a UnitTest for you.

Here is a realistic test of what we are doing. The test fails.

1. Original1 and Original2 are to resemble a file to be imported. The sum values of each of these worksheets are to be equal. Original1.Sum() = Original2.Sum()
2. When we import the file, the values are copied over to a master file. The master file ensures that the structure of the import file is valid and has not been changed. (The master file is created by a user.) We cannot change any of the formulas.
3. Each worksheet in the master file is recalculated.

I matched the exact design of the import file we have here; cell for cell, formula for formula, even value for value. I double checked the math. Floating Point is an issue which forced the test to fail.

Our user is asking the question... If "A" was entered, then why are we getting "B"? We tried explaining "floating point issues" but the user is non-technical. For instance A is input as 123.65, why would B be 123.649999995. Of course in simple terms this would not be the issue, there needs to be some calculations and a lot more numbers.

Hope this helps.

Gordon.

Snippet using System . Xml; using Microsoft . VisualStudio . TestTools . UnitTesting; using OfficeOpenXml; namespace UnitTestEpplus {     [ TestClass ] public class EpplusUnitTesting {         [ TestMethod ] public void FloatingPointTest()         { using ( var fpTestExcelPackage = new ExcelPackage ())             { // false or true should have the same results SetFullPrecisionAttribute(fpTestExcelPackage, true ); var original1 = fpTestExcelPackage . Workbook . Worksheets . Add( "Original1" );                 LoadOriginal1(original1); var original2 = fpTestExcelPackage . Workbook . Worksheets . Add( "Original2" );                 LoadOriginal2(original2); var new1 = fpTestExcelPackage . Workbook . Worksheets . Add( "New1" );                 CopyToNew1(original1, new1); var new2 = fpTestExcelPackage . Workbook . Worksheets . Add( "New2" );                 CopyToNew2(original2, new2); // add debug here Assert . AreEqual(new1 . Cells[ "O9" ] . Value, new2 . Cells[ "N39" ] . Value);             }         }

        [ TestMethod ] public void FullPrecisionIsOnTest()         { using ( var fpTestExcelPackage = new ExcelPackage ())             {                 SetFullPrecisionAttribute(fpTestExcelPackage, true ); var fullPrecisionIs = GetFullPrecisionSetting(fpTestExcelPackage); Assert . AreEqual(fullPrecisionIs, true );             }         }

        [ TestMethod ] public void FullPrecisionIsOffTest()         { using ( var fpTestExcelPackage = new ExcelPackage ())             {                 SetFullPrecisionAttribute(fpTestExcelPackage, false ); var fullPrecisionIs = GetFullPrecisionSetting(fpTestExcelPackage); Assert . AreEqual(fullPrecisionIs, false );             }         } private void LoadOriginal1( ExcelWorksheet ws)         { // Total = 353,364,037.80 //ws.Cells["C6:O9"].Style.Numberformat.Format = "(* #,##0);(* (#,##0);( \" - \"??);(@)"; ws . Cells[ "C6" ] . Value = 187947800.43 ;             ws . Cells[ "E7" ] . Value = 180000 ;             ws . Cells[ "G6" ] . Value = 2405751 ;             ws . Cells[ "G7" ] . Value = 3154409 ;             ws . Cells[ "I6" ] . Value = 106975302.7 ;             ws . Cells[ "L6" ] . Value = 12227493 ;             ws . Cells[ "M6" ] . Value = 39156589.67 ;             ws . Cells[ "M7" ] . Value = 991692 ;             ws . Cells[ "N6" ] . Value = 325000 ;         } private void LoadOriginal2( ExcelWorksheet ws)         { // Total = 353,364,037.80 //ws.Cells["C6:N39"].Style.Numberformat.Format = "( #,##0);( (#,##0);_( \" - \"??);(@_)"; ws . Cells[ "C6" ] . Value = 145989516 ;             ws . Cells[ "C9" ] . Value = 22368738 ;             ws . Cells[ "G9" ] . Value = 1245552 ;             ws . Cells[ "C12" ] . Value = 500000 ;             ws . Cells[ "E14" ] . Value = 180000 ;             ws . Cells[ "G15" ] . Value = 3154409 ;             ws . Cells[ "C16" ] . Value = 1500795.43 ;             ws . Cells[ "I19" ] . Value = 52406055.97 ;             ws . Cells[ "I20" ] . Value = 6529960.53 ;             ws . Cells[ "I25" ] . Value = 21627748.89 ;             ws . Cells[ "I26" ] . Value = 3308325.01 ;             ws . Cells[ "I28" ] . Value = 23103212.3 ;             ws . Cells[ "L30" ] . Value = 23817860 ;             ws . Cells[ "L31" ] . Value = 12317624.84 ;             ws . Cells[ "L32" ] . Value = 2934333.83 ;             ws . Cells[ "M32" ] . Value = 325000 ;             ws . Cells[ "K33" ] . Value = 5224172 ;             ws . Cells[ "L33" ] . Value = 991692 ;             ws . Cells[ "C38" ] . Value = 17588751 ;             ws . Cells[ "G38" ] . Value = 1160199 ;             ws . Cells[ "K38" ] . Value = 7003321 ;             ws . Cells[ "L38" ] . Value = 86771 ;         } private void CopyToNew1( ExcelWorksheet original, ExcelWorksheet newCopy)         {             newCopy . Cells[ "C6" ] . Value = original . Cells[ "C6" ] . Value;             newCopy . Cells[ "E7" ] . Value = original . Cells[ "E7" ] . Value;             newCopy . Cells[ "G6" ] . Value = original . Cells[ "G6" ] . Value;             newCopy . Cells[ "G7" ] . Value = original . Cells[ "G7" ] . Value;             newCopy . Cells[ "I6" ] . Value = original . Cells[ "I6" ] . Value;             newCopy . Cells[ "L6" ] . Value = original . Cells[ "L6" ] . Value;             newCopy . Cells[ "M6" ] . Value = original . Cells[ "M6" ] . Value;             newCopy . Cells[ "M7" ] . Value = original . Cells[ "M7" ] . Value;             newCopy . Cells[ "N6" ] . Value = original . Cells[ "N6" ] . Value; // Add formulas newCopy . Cells[ "O6" ] . Formula = "SUM(C6:N6)" ;             newCopy . Cells[ "O7" ] . Formula = "SUM(C7:N7)" ;             newCopy . Cells[ "O8" ] . Formula = "SUM(C8:N8)" ;

            newCopy . Cells[ "O9" ] . Formula = "SUM(O6:O8)" ;

            newCopy . Calculate();         } private void CopyToNew2( ExcelWorksheet original, ExcelWorksheet newCopy)         {             newCopy . Cells[ "C6" ] . Value = original . Cells[ "C6" ] . Value;             newCopy . Cells[ "C9" ] . Value = original . Cells[ "C9" ] . Value;             newCopy . Cells[ "G9" ] . Value = original . Cells[ "G9" ] . Value;             newCopy . Cells[ "C12" ] . Value = original . Cells[ "C12" ] . Value;             newCopy . Cells[ "E14" ] . Value = original . Cells[ "E14" ] . Value;             newCopy . Cells[ "G15" ] . Value = original . Cells[ "G15" ] . Value;             newCopy . Cells[ "C16" ] . Value = original . Cells[ "C16" ] . Value;             newCopy . Cells[ "I19" ] . Value = original . Cells[ "I19" ] . Value;             newCopy . Cells[ "I20" ] . Value = original . Cells[ "I20" ] . Value;             newCopy . Cells[ "I25" ] . Value = original . Cells[ "I25" ] . Value;             newCopy . Cells[ "I26" ] . Value = original . Cells[ "I26" ] . Value;             newCopy . Cells[ "I28" ] . Value = original . Cells[ "I28" ] . Value;             newCopy . Cells[ "L30" ] . Value = original . Cells[ "L30" ] . Value;             newCopy . Cells[ "L31" ] . Value = original . Cells[ "L31" ] . Value;             newCopy . Cells[ "L32" ] . Value = original . Cells[ "L32" ] . Value;             newCopy . Cells[ "M32" ] . Value = original . Cells[ "M32" ] . Value;             newCopy . Cells[ "K33" ] . Value = original . Cells[ "K33" ] . Value;             newCopy . Cells[ "L33" ] . Value = original . Cells[ "L33" ] . Value;             newCopy . Cells[ "C38" ] . Value = original . Cells[ "C38" ] . Value;             newCopy . Cells[ "G38" ] . Value = original . Cells[ "G38" ] . Value;             newCopy . Cells[ "K38" ] . Value = original . Cells[ "K38" ] . Value;             newCopy . Cells[ "L38" ] . Value = original . Cells[ "L38" ] . Value; // Add formulas newCopy . Cells[ "N6" ] . Formula = "SUM(C6:M6)" ;             newCopy . Cells[ "N7" ] . Formula = "SUM(C7:M7)" ;             newCopy . Cells[ "N9" ] . Formula = "SUM(C9:M9)" ;             newCopy . Cells[ "N10" ] . Formula = "SUM(C10:M10)" ;             newCopy . Cells[ "N11" ] . Formula = "SUM(C11:M11)" ;             newCopy . Cells[ "N12" ] . Formula = "SUM(C12:M12)" ;             newCopy . Cells[ "N13" ] . Formula = "SUM(C13:M13)" ;             newCopy . Cells[ "N14" ] . Formula = "SUM(C14:M14)" ;             newCopy . Cells[ "N15" ] . Formula = "SUM(C15:M15)" ;             newCopy . Cells[ "N16" ] . Formula = "SUM(C16:M16)" ;             newCopy . Cells[ "N17" ] . Formula = "SUM(C17:M17)" ;             newCopy . Cells[ "N19" ] . Formula = "SUM(C19:M19)" ;             newCopy . Cells[ "N20" ] . Formula = "SUM(C20:M20)" ;             newCopy . Cells[ "N21" ] . Formula = "SUM(C21:M21)" ;             newCopy . Cells[ "N22" ] . Formula = "SUM(C22:M22)" ;             newCopy . Cells[ "N24" ] . Formula = "SUM(C24:M24)" ;             newCopy . Cells[ "N25" ] . Formula = "SUM(C25:M25)" ;             newCopy . Cells[ "N26" ] . Formula = "SUM(C26:M26)" ;             newCopy . Cells[ "N27" ] . Formula = "SUM(C27:M27)" ;             newCopy . Cells[ "N28" ] . Formula = "SUM(C28:M28)" ;             newCopy . Cells[ "N30" ] . Formula = "SUM(C30:M30)" ;             newCopy . Cells[ "N31" ] . Formula = "SUM(C31:M31)" ;             newCopy . Cells[ "N32" ] . Formula = "SUM(C32:M32)" ;             newCopy . Cells[ "N33" ] . Formula = "SUM(C33:M33)" ;             newCopy . Cells[ "N35" ] . Formula = "SUM(C35:M35)" ;             newCopy . Cells[ "N36" ] . Formula = "SUM(C36:M36)" ;             newCopy . Cells[ "N37" ] . Formula = "SUM(C37:M37)" ;             newCopy . Cells[ "N38" ] . Formula = "SUM(C38:M38)" ;

            newCopy . Cells[ "N39" ] . Formula = "SUM(N6:N38)" ; //newCopy.Cells["N39:N39"].Style.Numberformat.Format = "0.00"; newCopy . Calculate();         } private const string CalculationPropertiesNodeName = "calcPr" ; private const string FullPrecisionAttributeName = "fullPrecision" ; private const string FullPrecisionOffValue = "0" ; private bool GetFullPrecisionSetting( ExcelPackage ep)         { var fullPrecisionAttr = GetAttribute(ep, FullPrecisionAttributeName); // if the tag does not exist, which is likely... then fullPrecision is expected // only when fullPrecision = "0", we know that it is turned off return (fullPrecisionAttr == null || fullPrecisionAttr . Value != FullPrecisionOffValue);         } private void SetFullPrecisionAttribute( ExcelPackage ep, bool fullPrecision)         { var attributeCollection = GetCalculationPropertiesAttributeCollection(ep); // If we can't get the attribute collection, then we can't set the attribute if (attributeCollection == null ) return ; var fullPrecisionAttr = GetAttribute(ep, FullPrecisionAttributeName); if (fullPrecision)             { if (fullPrecisionAttr != null )                     attributeCollection . Remove(fullPrecisionAttr);             } else { if (fullPrecisionAttr == null )                 { // easiest way to set the value of the attribute var newAttr = ep . Workbook . WorkbookXml . CreateAttribute(FullPrecisionAttributeName);                     newAttr . Value = FullPrecisionOffValue;

                    attributeCollection . Append(newAttr);                 } else {                     fullPrecisionAttr . Value = FullPrecisionOffValue;                 }             }         } private XmlAttribute GetAttribute( ExcelPackage ep, string attributeName)         { var attributeCollection = GetCalculationPropertiesAttributeCollection(ep); // return null if fullPrecision does not exists return attributeCollection ? [attributeName];         } private XmlAttributeCollection GetCalculationPropertiesAttributeCollection( ExcelPackage ep)         { var xmlNode = GetCalculationPropertiesNode(ep); if (xmlNode == null || xmlNode . Attributes ?. Count == 0 ) return null ; return xmlNode . Attributes;         } private XmlNode GetCalculationPropertiesNode( ExcelPackage ep)         { // At the bottom of the Workbook XML, this tag should exist var xmlNodeList = ep . Workbook . WorkbookXml . GetElementsByTagName(CalculationPropertiesNodeName); if (xmlNodeList . Count == 0 ) return null ; return xmlNodeList[ 0 ];         }     } }

From: "Mats Alm" notifications@github.com To: "JanKallman/EPPlus" EPPlus@noreply.github.com Cc: "derouyag" derouyag@shaw.ca, "Author" author@noreply.github.com Sent: Friday, December 28, 2018 2:34:00 PM Subject: Re: [JanKallman/EPPlus] Worksheet Calculation Issue (#379)

Ok, thanks. Just for clarity - EPPlus does not change any values in the cells or the way they are calculated due to the parameters you are mentioning, they are just forwarded to the spreadsheet program (i.e. Excel) to use. EPPlus just stores the numeric values as they are either set via its interface or read from the open office xml. So these parameters will not change the result of the calculation.

Will retry the test with your edited version of the test, but the logic in EPPlus is quite simple. If the difference between two decimal numbers is less than double.Epsilon they will be treated as equal, otherwise not.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub , or mute the thread .

[swmal]

Hi Gordon!

The last test you provided was useful and the problem lies in that we cast all numbers to double in EPPlus. I could easily make your test pass by casting doubles to decimals in the SUM function.

We need some time to discuss how to solve this permanently in a generic way.

If you are in a hurry to have this resolved you can clone EPPlus and change Sum.cs in the namespace OfficeOpenXml.FormulaParsing.Excel.Function to the code below.

All the best!

Mats

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml.FormulaParsing.ExpressionGraph;
using OfficeOpenXml.Utils;
using OfficeOpenXml.FormulaParsing.Exceptions;

namespace OfficeOpenXml.FormulaParsing.Excel.Functions.Math
{
    public class Sum : HiddenValuesHandlingFunction
    {
        public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
        {
            var retVal = 0m;
            if (arguments != null)
            {
                foreach (var arg in arguments)
                {
                    retVal += Calculate(arg, context);                    
                }
            }
            return CreateResult(Convert.ToDouble(retVal), DataType.Decimal);
        }

        private decimal Calculate(FunctionArgument arg, ParsingContext context)
        {
            var retVal = 0m;
            if (ShouldIgnore(arg))
            {
                return retVal;
            }
            if (arg.Value is IEnumerable<FunctionArgument>)
            {
                foreach (var item in (IEnumerable<FunctionArgument>)arg.Value)
                {
                    retVal += Calculate(item, context);
                }
            }
            else if (arg.Value is ExcelDataProvider.IRangeInfo)
            {
                foreach (var c in (ExcelDataProvider.IRangeInfo)arg.Value)
                {
                    if (ShouldIgnore(c, context) == false)
                    {
                        CheckForAndHandleExcelError(c);
                        retVal += Convert.ToDecimal(c.ValueDouble);
                    }
                }
            }
            else
            {
                CheckForAndHandleExcelError(arg);
                retVal += Convert.ToDecimal(ConvertUtil.GetValueDouble(arg.Value, true));
            }
            return retVal;
        }
    }
}

[derouyag]

This could have additional affects in other areas of Epplus. Aspose Cells seems to have worked around this issue some how. Not like I want to switch though.

https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel

Gordon.

From: "Mats Alm" notifications@github.com To: "JanKallman/EPPlus" EPPlus@noreply.github.com Cc: "derouyag" derouyag@shaw.ca, "Author" author@noreply.github.com Sent: Thursday, January 3, 2019 11:20:00 AM Subject: Re: [JanKallman/EPPlus] Worksheet Calculation Issue (#379)

Hi Gordon!

The last test you provided was useful and the problem lies in that we cast all numbers to double in EPPlus. I could easily make your test pass by casting doubles to decimals in the SUM function.

We need some time to discuss how to solve this permanently in a generic way.

If you are in a hurry to have this resolved you can clone EPPlus and change Sum.cs in the namespace OfficeOpenXml.FormulaParsing.Excel.Function to the code below.

All the best!

Mats using System ; using System . Collections . Generic ; using System . Linq ; using System . Text ; using OfficeOpenXml . FormulaParsing . ExpressionGraph ; using OfficeOpenXml . Utils ; using OfficeOpenXml . FormulaParsing . Exceptions ; namespace OfficeOpenXml . FormulaParsing . Excel . Functions . Math { public class Sum : HiddenValuesHandlingFunction { public override CompileResult Execute ( IEnumerable < FunctionArgument > arguments , ParsingContext context ) { var retVal = 0m ; if ( arguments != null ) { foreach ( var arg in arguments ) { retVal += Calculate ( arg , context );
} } return CreateResult ( Convert . ToDouble ( retVal ), DataType . Decimal ); } private decimal Calculate ( FunctionArgument arg , ParsingContext context ) { var retVal = 0m ; if ( ShouldIgnore ( arg )) { return retVal ; } if ( arg . Value is IEnumerable < FunctionArgument >) { foreach ( var item in ( IEnumerable < FunctionArgument >) arg . Value ) { retVal += Calculate ( item , context ); } } else if ( arg . Value is ExcelDataProvider . IRangeInfo ) { foreach ( var c in ( ExcelDataProvider . IRangeInfo ) arg . Value ) { if ( ShouldIgnore ( c , context ) == false ) { CheckForAndHandleExcelError ( c ); retVal += Convert . ToDecimal ( c . ValueDouble ); } } } else { CheckForAndHandleExcelError ( arg ); retVal += Convert . ToDecimal ( ConvertUtil . GetValueDouble ( arg . Value , true )); } return retVal ; } } }

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub , or mute the thread .

[swmal]

We haven’t looked much into the floating point challenge yet. We are handling it on operators, but haven’t had much issues on calculations so far. From the besinning we did the formula engine mostly for fun and it has not been clear to us how much it is used.

Most likely an interesting challenge, but as you write we need to look at how others (including Excel!) have solved it and find a generic solution for all functions.

[kjkrum]

I was shocked and dismayed to find that my ExcelRange.Value contained a double, and found this issue while looking for a way to fix that. How feasible would it be to make the library read all numeric values as decimal?

[swmal]

@kjkrum That is how EPPlus is built from the beginning - i.e. all numeric values are doubles all the way down to the cell store. Excel itself stores numeric values in the IEEE 754 binary 64-bit floating point format and the .NET double type is same size and standard.

We could of course change double to decimal, but that would consume twice the memory for storing them.

[kjkrum]

@swmal Interesting. Apparently Excel uses some tricks to hide some (but not all) floating point errors, and its calculations are almost-but-not-quite IEEE 754. Accountants know and just live with it.