Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    San Bernardino, CA USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using #DIV/0 in formulae (2002)

    Is there a way to use error messages like #DIV/0! in a formula? I'd like to do something like "=if(AVERAGE(D17:H17)=#DIV/0!,"NULL",average(d17:h17))", but can't figure out how to deal with the error message. When I put that particular formula in a spreadsheet it doesn't complain about syntax errors, but it doesn't give the right result, either (it gives #DIV/0! rather than NULL)

    Thanks...Bob

    Thanks...Bob

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: using #DIV/0 in formulae (2002)

    You might try using the ERROR.TYPE function. If the Error.TYPE function encounters a #DIV/0 error it returns a value of 2.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using #DIV/0 in formulae (2002)

    In addition to Catharine's response, how about =if(iserr(myfunction),null,myfunction)

    there are three of these:

    iserr: all errors but #n/a
    iserror: all errors
    isna: only #n/a

    HTH

    Brooke

  4. #4
    Lounger
    Join Date
    Mar 2001
    Location
    San Bernardino, CA USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using #DIV/0 in formulae (2002)

    That's the one I was looking for!

    Thanks...Bob

Posting Permissions

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