To be precise, we’re mistaken

“Bother!  We were mistaken!”
“To be precise: we’re a mistake.”
- Thomson and Thompson, Cigars of the Pharaoh

This post belongs to that category of dusty old bits of knowledge that lay deep in your head — stuff you never really thought would be useful, but you learned it anyway and it’s there, waiting for its turn in the sun.

So a friend of mine comes up to me and says, “What do you know about Excel?”.  I say, “a little”.  And she asked me to come look at a problem she’s having.  She’s got a spreadsheet that she’s using to build a data entry form on (I know), and she’s got a cell where someone can put in an Account Number. 

“Watch”, she says.  And she types in 123456789123456789 and hits enter.

Excel immediately changes the cell contents to 123456789123456000, replacing the final 3 digits (789) with 3 zeros.

“Why is it doing that?’, she asks.

I make one change to the cell, and ask her to try it again.  This time is works without replacing the final 3 digits.

I give you a 4 question quiz:

  1. What did I do?
  2. Why did I do it?
  3. What about Excel was causing it to do what it did?
  4. Why would this knowledge be useful to me at all when dealing with databases?
Posted in Skills. 2 Comments »

2 Responses to “To be precise, we’re mistaken”

  1. Chen Shapira Says:

    I know! I know!

    1) Changed the “Number” category from General to Text
    2) Because “Text” will not be messed around by the know-it-all Excel
    3) Excel’s default number format is 1.2345E+17, which causes the last 3 digits to be rounded off when the number has 20.
    4) Something to do with to_number and to_char?

  2. Sidhu Says:


    You hit before me & stole my choklate from Dominic [:D]

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder