+ Reply to Thread
Results 1 to 2 of 2

#DIV/0 and #VALUE - do error messages slow down your spreadsheet?

  1. #1
    Registered User
    Join Date
    10-03-2017
    Location
    Australia
    MS-Off Ver
    365
    Posts
    1

    #DIV/0 and #VALUE - do error messages slow down your spreadsheet?

    Hi there,

    I was wondering whether #DIV/0 etc in spreadsheets cause them to slow down. Someone recommended that I should get rid of them and replace with say ISERROR or IFERROR. But then that adds extra calculation I would have thought. Does anyone have some experience/thoughts on this?

    Many thanks
    Y

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,827

    Re: #DIV/0 and #VALUE - do error messages slow down your spreadsheet?

    I don't recall seeing any performance penalties due to errors like this.

    I put together a quick test:
    1) Make a lookup table -- since linear lookups are good and slow calculations that potentially make good test functions for this.
    1a) Fill column A with =ROW() to get a column of numbers
    1b) Fill column B with =RANDBETWEEN(0,10). Copy and paste as values so they don't change.
    2) Run some lookups that will sometimes result in errors and see if computation time is effected.
    2a) One formula =VLOOKUP(RANDBETWEEN(1,1e6),$A:$B,2,FALSE)/VLOOKUP(RANDBETWEEN(1,1e6),$A:$B,2,FALSE). Make enough copies so that you can measure calculation time (My machine is slow, but 40 copies took about 7 seconds).
    2b) Two formula. Wrap 2a in IFERROR() =IFERROR(VLOOKUP(RANDBETWEEN(1,1e6),$A:$B,2,FALSE)/VLOOKUP(RANDBETWEEN(1,1e6),$A:$B,2,FALSE),-1). Again, took about 7 seconds.
    3) Repeatedly press F9 to measure computation time.

    In my test, I could not see a difference in computation time, though there were several #Div0 among the results. My guess is that the use of IFERROR() is not about faster computation times.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Error Handling: Creating code to display error messages
    By Student1990 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-01-2013, 02:21 PM
  2. [SOLVED] lookup - if(is error(vlookup and error messages meanings
    By grphillips in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-18-2013, 07:03 PM
  3. add-ins/functions error messages when copying spreadsheet to other computers
    By adidrying in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-23-2012, 03:27 PM
  4. Replies: 5
    Last Post: 11-10-2009, 07:06 AM
  5. [SOLVED] error messages-Is there a way to show error messages
    By hans in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-02-2006, 12:45 PM
  6. Creating multiple messages from spreadsheet
    By tip2tail in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-21-2005, 01:35 PM
  7. [SOLVED] Help Messages in Spreadsheet
    By Neil Greenough in forum Excel General
    Replies: 1
    Last Post: 11-29-2005, 02:20 PM
  8. Help Messages in Spreadsheet
    By Neil Greenough in forum Excel General
    Replies: 2
    Last Post: 11-27-2005, 03:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1