3 ms·

> The generated code uses floating point, rather than fully precise arithmetic, so results can differ slightly Use with caution.

teraflop 11y ago
Excel already uses floating point internally, so if you're worried about compatibility, this is less risky than switching to arbitrary precision math.
- jscottmiller 11y ago
  Wow, didn't realize that about excel (though I have observed it in google sheets). I wonder how many hedge funds rely on sheets with catastrophic cancellation issues.
  - AceJohnny2 11y ago
    I expect all financial math is fixed point.
    - chillingeffect 11y ago
      I'm not sure why ppl downvoted you. But from what I've heard - never implemented myself - what you're saying is common... At least as far as using separate fixed point values to represent the left and right sides of the decimal place. Now I'm sure for things like computing interest by raising things to a power or even computing percentages, etc, floating point is used (and eventually reduced to fixed point values), so maybe someone found it too absolute to say "all" financial math is fixed point? Anyway, if someone with experience can confirm or deny, it would be neat...
      - JupiterMoon 11y ago
        Because he is wrong? Excel uses 64 bit floats.
    - timrobinson 11y ago
      It's absolutely not. Everything I've seen has been 64-bit double. If you need to, you round either intermediate or final calcs to a set precision. I once worked on replicating a particular index, and the engine used fixed point internally. In order to get the values to match with the ones published officially, we had to emulate Excel floating point: our fixed point values wouldn't line up with theirs.
  - ma2rten 11y ago
    I tried 0.1 + 0.2 in both google docs and excel. I got 0.3000000000000000000, not 0.30000000000000004, like you normally would using floating point.
    - teraflop 11y ago
      I think there's internal rounding that hides it. Try A1=1e50, B1=A1+1, and C1=B1-A1. Excel definitely used floating point in the past (because you used to be able to use it to test for the Pentium FDIV bug) and according to the documentation, it still does: https://support.microsoft.com/en-us/kb/78113 https://support.microsoft.com/en-us/kb/78113
      - timrobinson 11y ago
        Rounding happens when the value is displayed. To see the raw value, try: =(0.1+0.2)
- Someone 11y ago
  Floating point tweaked to make it look like it uses decimal arithmetic. See http://www.eecs.berkeley.edu/~wkahan/Mindless.pdf http://www.eecs.berkeley.edu/~wkahan/Mindless.pdf, starting at page 3.

teraflop 11y ago

Excel already uses floating point internally, so if you're worried about compatibility, this is less risky than switching to arbitrary precision math.

jscottmiller 11y ago
Wow, didn't realize that about excel (though I have observed it in google sheets). I wonder how many hedge funds rely on sheets with catastrophic cancellation issues.
- AceJohnny2 11y ago
  I expect all financial math is fixed point.
  - chillingeffect 11y ago
    I'm not sure why ppl downvoted you. But from what I've heard - never implemented myself - what you're saying is common... At least as far as using separate fixed point values to represent the left and right sides of the decimal place. Now I'm sure for things like computing interest by raising things to a power or even computing percentages, etc, floating point is used (and eventually reduced to fixed point values), so maybe someone found it too absolute to say "all" financial math is fixed point? Anyway, if someone with experience can confirm or deny, it would be neat...
    - JupiterMoon 11y ago
      Because he is wrong? Excel uses 64 bit floats.
  - timrobinson 11y ago
    It's absolutely not. Everything I've seen has been 64-bit double. If you need to, you round either intermediate or final calcs to a set precision. I once worked on replicating a particular index, and the engine used fixed point internally. In order to get the values to match with the ones published officially, we had to emulate Excel floating point: our fixed point values wouldn't line up with theirs.
- ma2rten 11y ago
  I tried 0.1 + 0.2 in both google docs and excel. I got 0.3000000000000000000, not 0.30000000000000004, like you normally would using floating point.
  - teraflop 11y ago
    I think there's internal rounding that hides it. Try A1=1e50, B1=A1+1, and C1=B1-A1. Excel definitely used floating point in the past (because you used to be able to use it to test for the Pentium FDIV bug) and according to the documentation, it still does: https://support.microsoft.com/en-us/kb/78113 https://support.microsoft.com/en-us/kb/78113
    - timrobinson 11y ago
      Rounding happens when the value is displayed. To see the raw value, try: =(0.1+0.2)
Someone 11y ago
Floating point tweaked to make it look like it uses decimal arithmetic. See http://www.eecs.berkeley.edu/~wkahan/Mindless.pdf http://www.eecs.berkeley.edu/~wkahan/Mindless.pdf, starting at page 3.