• (disco)

    There's an option to trim the precision of values before using them at the workbook (or column, or cell) level?

  • (disco) in reply to dkf
    dkf:
    before

    I personally wouldn't have thought so, but now I'm curious where such a setting lies....

  • (disco)

    Woah, that's a pretty horrible setting to even exist in the first place...

  • (disco)

    Holy balls this is actually a thing that exists!!!! [image]

    Microsoft's explanation for this feature is "Preventing floating point rounding errors": https://support.office.com/en-NZ/article/Set-rounding-precision-e5d707e3-07a8-4df2-810c-218c531eb06a

  • (disco) in reply to Tsaukpaetra
    Tsaukpaetra:
    "Preventing floating point rounding errors"

    Round everything to prevent rounding errors. Um, yeah, sure.

  • (disco) in reply to Tsaukpaetra
    Tsaukpaetra:
    Microsoft's explanation for this feature is "Preventing floating point rounding errors"

    Holy shitballs, that's a dumb thing to have in there. Fully 20% of the idiotic questions on Stack Overflow are about preventing floating point rounding “errors” and every last one of them is because the questioner knows fuck all about what binary arithmetic means when applied to fractional math with finite precision. To crown that sort of stupidity by baking it into a workbook-level option…

    A friend of mine found a way to do infinite precision floats correctly. The maths involved is… well, it involves tensors as arithmetic operators and stereographic projection of values onto the unit circle and was a bit out of my meagre league. I did note that it seemed a lot more tractable than the more common approaches of using generator functions that yield simple sequences of digits or continued fraction coefficients, but it still has the usual problems with decidability because they're fundamental. Indeed, they were what Turing was working on when he invented computation as we now know it in order to solve some of the lemmas he was working with.

  • (disco) in reply to dkf
    dkf:
    tensors as arithmetic operators and stereographic projection of values onto the unit circle

    Well, these all appear to be sensible words, but I'm having trouble stitching them together into a meaningful interpretation...

  • (disco) in reply to dkf

    So why is all this so hard, such a big deal, and why are these people using Excel for this? Such a task sounds like it would call for something more...advanced(?).

  • (disco) in reply to dkf
    dkf:
    knows fuck all about what binary arithmetic means when applied to fractional math with finite precision

    I'm sure the world is full of experts about this.

  • (disco)

    Actually it is quite useful. Consider a tax calculation on a sale. It will be rounded to the nearest penny as paid. Now sum a bunch of these, you need the sum of the rounded numbers to actually represent the total amount collection.

    BOTH (pre-round, or not) approaches cause problems if they are not understood. This should be part of a required "license" before being authorized to do any math!

  • (disco) in reply to TheCPUWizard

    Yes, but it shouldn't be global to the entire workbook file!

  • (disco) in reply to Tsaukpaetra
    Tsaukpaetra:
    dkf:
    tensors as arithmetic operators and stereographic projection of values onto the unit circle

    Well, these all appear to be sensible words, but I'm having trouble stitching them together into a meaningful interpretation...

    I found the citation.

    Potts, Peter John. 1998. Exact real arithmetic using Mobius transformations. PhD diss., University of London. http://peterpotts.com/pdf%20files/phd.pdf

    (And yes, this stuff makes my head spin as well.)

  • (disco) in reply to dkf
    dkf:
    Potts, Peter John. 1998. Exact real arithmetic using Mobius transformations. PhD diss., University of London.http://peterpotts.com/pdf%20files/phd.pdf

    PJP, there's TRWTF, right there. Er, hang on ...

    But seriiously though, the moment someone comes on the phone driving his title before him like a Versace baby-buggy, you know he's got nothing left but his reputation, and most definitely is not as clever as he thinks he is. He might have been clever once, but senility sets in ...

    And of course under such circumstances, you say to him: "Have you checked the precision of the numbers? Be a doll and do that, then if you still have problems, give me another call."

  • (disco)

    So you claim there is a MS Excel support, which talks with end customers and spends TWO WEEKS, trying to solve their mysterious problems?

    I have a problem believing that

  • (disco) in reply to rc4

    You already have the answer, he's a professor

  • (disco) 
    “Man, I gotta become a developer,” he muttered. “I bet they don’t deal with crap like this.”

    Hahahahahahahahahahahahahahahahahahahahahahahahahahahahahahahaaaaahahahahaha

  • (disco) in reply to dkf

    It's always a good sign when the referenced link is a file titled merely "phd.pdf".....

  • (disco) in reply to Tsaukpaetra
    TheCPUWizard:
    Actually it is quite useful. Consider a tax calculation on a sale. It will be rounded to the nearest penny as paid. Now sum a bunch of these, you need the sum of the rounded numbers to actually represent the total amount collection.

    When you want rounding, there's a function for that. Blindly applying round to every single calculation in every single worksheet is just asking for trouble.

    Frankly, I'm surprised it wasn't just foisted off on a never-ending round of blame transfer. It's impressive that Ishai actually came up with an answer for a problem like this. Especially since the professor (not Mr. or sir) was such a snob.

  • (disco) in reply to Eldelshell

    Yeah, I once tried to help a stuffy professor. He wanted to write a serial port interrupt routine for the Macintosh. In LISP.

  • (disco) in reply to martin
    martin:
    So you claim there is a MS Excel support, which talks with end customers and spends TWO WEEKS, trying to solve their mysterious problems?

    Maybe he was only looking into it during his spare time between calls? If he stopped taking calls for two weeks to look into, then that's a major :wtf:, both because he stopped doing his main job for two weeks and because it took two weeks to debug this one problem.

  • (disco) in reply to martin

    That's why Microsoft support is paid support. You're paying for the rep/SDET/Dev/Raymond time your request consumes.

  • (disco) in reply to BBIsMe

    My reaction exactly

  • (disco) in reply to CoyneTheDup

    Um, why is this a reply to me?

  • (disco)

    TRWTF is that a Tier-3 Excel support person should have only one response to all callers: "Stop using Excel and get an app that actually works and is reliable."

    Friends Don't Let Friends Use Excel.

    Oh, and also this particular professor seems to be much more of a PHB than a PhD. Plus he's a dimbrained fool for using Excel.

  • (disco) in reply to cellocgw
    cellocgw:
    TRWTF is that a Tier-3 Excel support person should have only one response to all callers: "Stop using Excel and get an app that actually works and is reliable."

    Friends Don't Let Friends Use Excel.

    Proof that Tier-3 support is not your friend. According to them, every Microsoft product must be the best product for your intended purpose; expressing that sentiment is a job requirement.

  • (disco) in reply to cellocgw
    cellocgw:
    Oh, and also this particular professor seems to be much more of a PHB than a PhD. Plus he's a dimbrained fool for using Excel.

    I'm guessing that you haven't worked at any Universities before.

    Several years ago I found myself interviewing for an IT position at A Major University. I had a nice chat with some of the team members, did a kind of Unix Rorschach test with configuration files ("That's Sendmail... This one is BIND... I'm not sure what it does, but it's using awk... This is two bears high fiving...") and then took a quick tour before we discussed just what the job responsibilities would be.

    "It's great.", the team leader gushed. "We have a good budget, the department head is all about investing in new technology, and the benefits are pretty good too."

    "But...", and here he lowered his voice, as if worried that someone or something from outside of the room might overhear him, "A major part of this position is working directly with... professors."

    "Yes", I replied cheerfully, "That's what it said in the original posting. I don't have a problem with end user support."

    "I just want to be clear", he continued, "these people tend to be... well... a little bit special. They have a lot of ideas of their own and can... um... sometimes be ... a little difficult to work with. I want to be absolutely sure that you are aware of this before anything happens."

    The other two people at the table looked around nervously and nodded.

    I ended up taking another position with a small NGO that was offering a lot more cash and fewer interactions with tenured faculty, but that was when I learned that "So, why is this position open and what happened to the last person who was doing this job?" was a good question to ask before agreeing to anything.

  • (disco) in reply to CoyneTheDup
    CoyneTheDup:
    When you want rounding, there's a function for that. Blindly applying round to every single calculation in every single worksheet is just asking for trouble.

    It's obvious that you don't work in financial and/or accounting software. Individual calculations are always rounded. Wrapping every single one with a round function would be a PITA.

    Of course, the correct way to handle this is a decimal variable type, but Excel.

  • (disco) in reply to BBIsMe

    Blatant troll is blatant.

  • (disco) in reply to TheCPUWizard
    TheCPUWizard:
    Actually it is quite useful. Consider a tax calculation on a sale. It will be rounded to the nearest penny as paid. Now sum a bunch of these, you need the sum of the rounded numbers to actually represent the total amount collection.
    This, I’d probably do by rounding in the cell after the calculation, so that the value actually displayed is the one that gets used in other calculations. Not by applying a general setting to the whole spreadsheet, as I suspect that might just cause the exact opposite problem somewhere else you don’t expect it.

    Edit: :hanzo:

  • (disco) in reply to RevCurtisP
    RevCurtisP:
    It's obvious that you don't work in financial and/or accounting software. Individual calculations are always rounded. Wrapping every single one with a round function would be a PITA.

    Of course, the correct way to handle this is a decimal variable type, but Excel.

    Isn't that a case of thou shalt not store currency amounts in a binary floating-point variable?

  • (disco) in reply to martin
    martin:
    So you claim there is a MS Excel support, which talks with end customers and spends TWO WEEKS, trying to solve their mysterious problems?

    I have a problem believing that

    I'm not sure how it works now, but ten years ago, when you paid your $250 support fee you got your support, even if it meant writing a custom OS patch for you, especially if it looked like you were experiencing an actual bug.

    My boss at the time always made sure we asked for something that would require fixing multiple problems in between before we could even get to the original problem, and they were always willing, if not always as skilled as we'd like. One actual bug in Terminal Services got us in touch with an actual developer of the product, and that guy knew his stuff in and out and rolled out a patch to us (that eventually made it into a release).

  • (disco) in reply to PleegWat
    PleegWat:
    Isn't that a case of thou shalt not store currency amounts in a binary floating-point variable?

    We're not talking addition and subtration; Excel has far more than enough precision for that, at 64-bit double float. It's things like multiplication, division, interest calculations with powers, and so on that generate long strings of digits after the decimal that MUST be cut off immediately before any more calculations are made with that number, by most accounting rules.

  • (disco) in reply to Quite
    Quite:
    But seriiously though, the moment someone comes on the phone driving his title before him like a Versace baby-buggy, you know he's got nothing left but his reputation, and most definitely is not as clever as he thinks he is. He might have been clever once, but senility sets in ...

    And of course under such circumstances, you say to him: "Have you checked the precision of the numbers? Be a doll and do that, then if you still have problems, give me another call."

    Did you have much of a point there? Seriously, if you want to do exact real arithmetic then you need to delve into some rather complex mathematics. The algorithms produced tend to be slow; you usually use them just to check the other faster, imprecise algorithms that you use for the bulk work, or to generate the high-precision constants that those algorithms require.

    I'm very glad that I don't have to understand it. I know that I could read it up if needed, but I sure don't want to. OTOH, I have good libraries available if I need them because I know people who do grok this stuff.

    DCRoss:
    "A major part of this position is working directly with... professors."
    Welcome to my life.
  • (disco) in reply to foxyshadis
    foxyshadis:
    We're not talking addition and subtration; Excel has far more than enough precision for that, at 64-bit double float.

    Really? Because I frequently run into stuff like this...

    [image]
  • (disco)
    abarker:
    you're complaining about errors when doing arithmetic with floats?

    No, I'm surprised that someone thought Excel didn't have errors when doing arithmetic with floats.

  • (disco) in reply to anotherusername
    anotherusername:
    I'm surprised that someone thought Excel didn't have errors

    Why?

  • (disco) in reply to Fox

    Because it's Excel.

  • (disco) in reply to anotherusername
    foxyshadis:
    thou shalt not store currency amounts in a binary floating-point variable?

    As I mentioned above, thou shalt not store currency amounts in a binary floating-point variable.

  • (disco) in reply to BBIsMe
    BBIsMe:
    `“Man, I gotta become a developer,” he muttered. “I bet they don’t deal with crap like this.”``

    Hahahahahahahahahahahahahahahahahahahahahahahahahahahahahahahaaaaahahahahaha

    Actually, he's right. With a different kind of crap. In a different way.

  • (disco) in reply to anotherusername
    anotherusername:
    Because it's Excel.

    But consumers.

  • (disco)

    A CS professor, with a complex calculation, doesn't simply write his own program to do his calculations? He expects Excel to be sufficient, even after turning on a workbook-wide setting to round off all calculation results, that is off by default? Why do I get the feeling that his BigName university is only well known for its non-technical degrees?

  • (disco)
    1. Open a blank Excel workbook
    2. In cell A1 put "7051.94"
    3. In cell A2 put "7615.88"
    4. In cell B2 put formula "=A2-A1"
    5. In cell C2 put "563.94"

    Now the interesting part.

    1. In cell D2 put "=C2=B2". This should compare two cells that both have 563.94. There is only 2 decimal places in the entered numbers so there's no rounding or anything going on.

    You would expect the value of cell D2 is TRUE but it's not.

    So in Excel, 563.94 != 563.94.

  • (disco) in reply to prueg
    prueg:
    A CS professor,

    Did it say he's a CS professor? I don't recall that... *checks*

    ...holy shit, I didn't even notice that. *upgrades :wtf: level of this article by +3 :wtf:s*

  • (disco) in reply to BC_Caps

    And yet C2-B2 = 0, even when shown as scientific notation.

    Maybe it is doing referential equality, not value equality?

  • (disco) in reply to prueg

    However, =ABS(C2-B2) yields 4.54747E-13. So yes, just another oddity of floating point underflow.

  • (disco) in reply to Scarlet_Manuka

    Also, take a look at what happens if you do a copy-paste-value of B2.

  • (disco)

    This is an old problem. I heard of manual "computers" (humans) that worked on things in the Manhattan project. They were told to "compute" and their previous experience was with business numbers (dollars and cents). They automatically rounded things to two digits, and got "wrong" answers. After that fiasco, they were told to do "more digits" and things came out better.

    Live and learn.

  • (disco) in reply to BC_Caps
    RevCurtisP:
    It's obvious that you don't work in financial and/or accounting software. Individual calculations are always rounded. Wrapping every single one with a round function would be a PITA.

    Of course, the correct way to handle this is a decimal variable type, but Excel.

    Actually, I do financial software for a living right now, but I rarely do rounding. That's because rounding is not needed for add and subtract when Currency types are being used and precision is not changing; if you think it is, you don't understand precision or rounding. You can bet I do round when using multiply or divide.

    But what the professor was doing is not financial software; and it is a major WTF to round everything in, say, a science application. Moreover, the check that was pointed out by tier-3 is not a default setting, so the professor evidently selected it.

    BC_Caps:
    You would expect the value of cell D2 is TRUE but it's not.

    So in Excel, 563.94 != 563.94.

    That's because Excel uses floating point. If you increase the display precision you will see why the comparison fails:

    [image]

    Comparisons of this type need to be rounded to a lesser precision to ensure equality. See Comparison. Some languages do provide a means to do this automatically; Rexx NUMERIC FUZZ comes to mind.

  • (disco)

    Happily, he was free to roll his eyes without risk.

    Ah yes, because he didn't know about the secret camera. (I live in fear that the phone's microphone ever stays on for an additional five seconds.)

  • (disco) in reply to BBIsMe

    “Man, I gotta become a developer,” he muttered. “I bet they don’t deal with crap like this.”

    Yeah that statement is TRWTF the rest is just another day in paradise for user support.

Leave a comment on “The Excel Expert”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article