Calculation error

Calculation error

Posted by: tosch on 7 December 2018, 9:28 am EST

• 

  tosch

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 7 December 2018, 9:28 am EST

  I’m having a problem with a calculation difference in Spread V11.

  Please load the attached xml file.

  In column B I have several values which I sum in row 6 and then take that value and round it to 5 cents in row 7.
  
  When I enter exactly the same number from row 6 in a different column and round it I get a different result.

  I have saved the sheet in Excel format. When I open it in Excel B7 and C7 both display 121.30.

  Please explain.

  Thomas

• 

  tosch

  • Post Options:
  • Link
    Copy

  Posted 7 December 2018, 9:30 am EST

  and here are the attachments

  Copy of Spread_CalculationError.zip

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 10 December 2018, 6:49 am EST

  Hi,

  This is due to double precision error. In your XML file, the value after sum is 121.27499999999998.
  
  121.27499999999998 * 20 = 2425.499999999998
  
  2425.499999999998 + 0.5 = 2425.999999999998
  
  INT(2425.999999999998) = 2425
  
  2425 / 20 = 121.25

  This has been fixed in Spread 12, so that it is consistent with Excel.

  In your case, you can replace INT with ROUND to avoid rounding down of the result of sum.

  Regards,
  
  Jitender