Excel accuracy

The Excel team have a great post explaining Excels accuracy here.

Do you think its adequate for normal spreadsheet use?

Do you think it should be improved in todays 64 bit computing world?

Would you be willing to take a performance hit for greater accuracy?

My answers – yes, no, no.

In fairness I have tripped over accuracy issues in spreadsheets I’ve worked with, so if it didn’t impact performance (I’m not bothered about file size), and didn’t break a bunch of old stuff, then a few more significant digits would be welcome. What about you?

cheers

Simon

Advertisements

13 Responses to “Excel accuracy”

  1. Mike Staunton Says:

    I’m happy with 15-digit numerical precision in Excel – if I really needed more, I’d work in Matlab or Gauss – what I’m not happy with is the poor accuracy of key Excel functions such as NORMSINV that are accurate to only 7 decimal places or so and return occasional crazy results

  2. Harlan Grove Says:

    Much less than IEEE double precision was sufficient for NASA’s Apollo Program. If 64 bits was more than sufficient for competent rocket scientists, it should be more than sufficient for more mundane tasks.

    More digits will not solve rounding/truncation problem. Fractional values that can’t be represented exactly as sums of negative powers of 2 will still be affected. Arbitrary precision arithmetic might help, but it’s significantly slower than floating point.

    If the task at hand is essentially and exclusively accounting, then precision as displayed is sufficient to eliminate rounding/truncation error. But this can lead to other problems if any greater precision calculations are needed, such as compound interest calculations.

  3. alastair Says:

    this topic fascinates me, primarily because it is important but a typical excel user will be totally unaware of it.

    Personally i think that the way it works is fine, and I’d rather they spent time sorting (getting rid of) the ribbon!

  4. Marcus Says:

    Also Yes, No and No.

    The most granular I’ve had to care about is the 8th or 9th decimal place. As Mike suggested, other tools are probably more appropriate for tasks requiring accuracy up to (or beyond) the 15th.

  5. Simon Says:

    I think most people who want better than 15 digits are trying to store credit card numbers as numbers rather than text, from the newsgroup posts I’ve seen.

    Alastair I’m trying to lay off the ribbon as a few readers seem to be sick of the topic. I have a few post lined up though if anyone is still interested?

    Yeah I reckon others tools are appropriate if current Excel is not adequate.

  6. Marcus Says:

    “credit card numbers”
    I was thinking 15 digits to the right of the decimal, not a number 15 digits long.

  7. Martin Rushton Says:

    Simon you might be interested that world war iii looks like it might break out on the Word list I am a member of. With one exception everyone is saying Word 2007 is a giant leap back and a lot of people are re-installing 2003 or moving to open office. The one exception is currently writing a guide to ………..

    Word 2007 changes. She is accusing the rest of being reactionaries who will get left behind by change as they will be out of tune with the rest of the world. I don’t think she’s considered the possibility M$ have gone off key.

  8. Martin Rushton Says:

    Yes your 6 and 7 th decimal place will only be accurrate if your numbers were less than 1000 million and 100 million respectively. Try putting 16 1s a cell. Excel will immediately convert to 1.1 E+15. Try formatting it as general and the last 1 will become a 0 which is why the credit cards only work formatted as text (unless the last digit is 0).

    There was a similar discussion on the peachease Excel-L list last week and the consensus was wherever the decimal point how can something that is 10^-14 the value of the first digit be material.

  9. Simon Says:

    Martin – interesting on Word 2007, I thought Office 2007 had scraped through all the cack handed journo reviews because they are all just basic word users with no clue about the rest of the suite, or real world features.

    I recently got an integration project canned because Word 2007 failed to deliver.

    RE accuracy 10 ^14 – thats my point exactly, I have seen trillions reported to 2 (and more!) decimal places, but its just make the author look silly.

  10. Martin Rushton Says:

    There’s a couple of issues. Firstly the new terminology and secondly, involving your pet hate, unable to find things on the ribbon.

    The thread was started by a TechWriter who’s new PC came pre-installed witj 2007 and wondered if he could install 2003 as well because he might have to use 2007 on occassion. A lot of other’s came back and said they had gone as far as uninstalling 2007. There’s also an indication of a migration from M$ to other WP tools including OO so it isn’t just Excel that is haemorrhaging users.

  11. Harlan Grove Says:

    There are some applications that require huge INTEGERS. Most cryptographic algorithms involve very large prime numbers. There’s seldom if ever any particular need for more than 9 significant FRACTIONAL digits even for perversely complicated discounted cashflow analysis.

    To use computers to calculate effectively, you have to learn when and how to round your own calculations and when and how to handle machine rounding.

  12. Harlan Grove Says:

    Perhaps Excel will need more digits to represent its resource needs. Off-topic, but check out

    http://www.infoworld.com/article/08/04/14/16TC-winoffice-performance_1.html

  13. Mike Staunton Says:

    For my option pricing examples, I need at least 8-10 decimal places of accuracy for the most difficult to value and pretty much 15 dp for straightforward Black-Scholes options

    And the maximum real value equates to only 173 factorial so lots of times you need approximations or a work-around taking logs

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: