viewpoint-particle

Author Topic: Loss of Precision  (Read 889 times)

mark_in_atx

  • Newbie
  • *
  • Posts: 12
Loss of Precision
« on: January 02, 2018, 06:33:20 am »
I'm not sure if this is an artifact of Excel OLE or Winbatch. 

I have a number that requires a high degree of precision.  I apply a decimal format to Excel:
objWB.Worksheets('Worksheet 1').Range(objWB.Worksheets('Worksheet 1').Cells(1,1),objWB.Worksheets('Worksheet 1').Cells(1,1)).NumberFormat = "0.00000000000000000000"

In Excel, you can see the required precision I need.  However, when I output the value it loses precision down to 8 decimals:

Message('',objWB.Worksheets('Worksheet 1').Range('A1:A1').Value)

How can I get this to output all 20 decimals specified in the format?




td

  • Tech Support
  • *****
  • Posts: 2568
    • WinBatch
Re: Loss of Precision
« Reply #1 on: January 02, 2018, 06:54:19 am »
Please see the Decimals function in the Consolidated WIL Help file.
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


mark_in_atx

  • Newbie
  • *
  • Posts: 12
Re: Loss of Precision
« Reply #2 on: January 02, 2018, 07:14:59 am »
That doesn't fix it.  The input number has 20 signification digits, yet both code variants below still only show 8 digits.  If I multiply the number *1000000 you can see more precision, but still only 8 decimals.  I suspect this may be an OLE-based .Value issue...

Decimals(-1)
OutputValues = oApp.WorksheetFunction.Transpose(objWB.Worksheets('Adjust Data - Round 1').Range('P10:S%LastRow%').Value)
Message('',objWB.Worksheets('Adjust Data - Round 1').Range('S13:S13').Value)

Decimals(20)
OutputValues = oApp.WorksheetFunction.Transpose(objWB.Worksheets('Adjust Data - Round 1').Range('P10:S%LastRow%').Value)
Message('',objWB.Worksheets('Adjust Data - Round 1').Range('S13:S13').Value)

td

  • Tech Support
  • *****
  • Posts: 2568
    • WinBatch
Re: Loss of Precision
« Reply #3 on: January 02, 2018, 10:11:14 am »
There are three factors to consider.   The first is the WinBatch will only use 10 digits of precision when converting from a floating point number to a string.  This does not affect the value of the floating point number contained in a floating point variable.  I should have read your original post a little more carefully and mentioned this.

The second factor is floating point precision.  Double precision floating point numbers only have 52 bit for the mantissa which limits the precision and 11 bits for the exponent which limit magnitude.  This arrangement is specified by the IEEE-754 standard.  A 20 significant digit number cannot be represented at full precision.

The third factor is how Excel and COM represent floating point numbers.   I am not an Excel expert but Excel certainly takes advantage of multiple internal representations of numbers.  Also COM has 3 ways to represent floating point numbers - R4, R8, and DECIMAL.  Consider the following:

Code: Winbatch
Num    = 1234567890.0123456789
fltNum = ObjectType('R4', Num )
dblNum = ObjectType('R8', Num )
decNum = ObjectType('decimal', Num)
strText = 'R4: ':fltNum:@CRLF:'R8: ':dblNum:@CRLF:'DECIMAL: ':decNum:@CRLF

fltNum = ObjectType('R4', '1234567890.0123456789')
dblNum = ObjectType('R8', '1234567890.0123456789')
decNum = ObjectType('decimal', '1234567890.0123456789')
strText := 'R4: ':fltNum:@CRLF:'R8: ':dblNum:@CRLF:'DECIMAL: ':decNum

Message('Variant Numbers ':Num, strText)
 

Best advice is to figure out how Excel is storing your floating point and maybe getting it to return floats as DECIMAL variants. 

Perhaps someone with more Excel experience can suggest a better solution.
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


stanl

  • Pundit
  • *****
  • Posts: 783
Re: Loss of Precision
« Reply #4 on: January 02, 2018, 11:59:38 am »
I think Excel is stuck with 15 decimal precision. Can be extended with the product below which they say is free to use.


http://precisioncalc.com/

td

  • Tech Support
  • *****
  • Posts: 2568
    • WinBatch
Re: Loss of Precision
« Reply #5 on: January 02, 2018, 04:25:47 pm »
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates