+ Reply to Thread
Results 1 to 8 of 8

Is there a workaround for no IFERROR function in 2003?

  1. #1
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Question Is there a workaround for no IFERROR function in 2003?

    Hi,

    I have a query with regard to a workbook that I am trying to piece together to get it to work in 2003 (someone on here kindly posted the workbook up regarding a process that I would like to understand more but didn't indicate that it was likely to be 2007 or newer only). In short most of the sheet looks like it has formula in it that is 2003 compatible (e.g. everything seems to be turning up numbers) - however I have a number of formulas with IFERROR's in them that are turning up blanks and was wondering if there is a workaround for this to substitute for that there is no IFERROR in 2003?

    The following are cells/formulas that are causing issues

    =_xlfn.IFERROR(MAX(OFFSET($AJ$9,0,0,$J$10,1)-OFFSET($AK$9,0,0,$J$10,1)),"")
    =_xlfn.IFERROR(MAX(OFFSET($AK$9,0,0,$J$10,1)-OFFSET($AF$9,0,0,$J$10,1)),"") - this was an array so I'll add the brackets back in afterwards
    =_xlfn.IFERROR(ABS(MAX(J17:J18)),"")
    =_xlfn.IFERROR(J19*SQRT($J$10),"")
    =_xlfn.IFERROR(HLOOKUP(J19,KSDTableH,2),"")
    =IFERROR(IF(AG9<>"",NORMDIST(AH9,$J$11,$J$12,1),""),"")
    =_xlfn.IFERROR(IF(AG13<>"",NORMDIST(AH13,$J$11,$J$12,1),""),"")

    Not knowing specifically what an IFERROR is or what an xlfn.IFERROR is, I was wondering if a workaround is possible or feasible?

    Thanks in advance for any assistance
    Last edited by mrvp; 07-22-2012 at 11:52 AM. Reason: solved

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Is there a workaround for no IFERROR function in 2003?

    use iserror instead with if
    if(iserror(your formula),"",your formula)
    eg
    =if(iserror(max(offset($aj$9,0,0,$j$10,1)-offset($ak$9,0,0,$j$10,1))),"",max(offset($aj$9,0,0,$j$10,1)-offset($ak$9,0,0,$j$10,1)))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Is there a workaround for no IFERROR function in 2003?

    Thanks I think I've got this - I've already flipped two of the formulas over and am just working through the others. I think I'll just need to watch my brackets as it is occasionally creating issues with the "" (and returning it as an error in the formula) e.g: with reference to the third formula above:

    =IF(ISERROR(ABS(MAX(J17:J18)),"",ABS(MAX(J17:J18)))

    Not having created the workbook I don't know if this was expecting some kind of value at this point but I'll do a little bit of digging.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Is there a workaround for no IFERROR function in 2003?

    all those iferrors return ""
    the syntax is iferror(your formula,do this) if there is no error the formula is executed

  5. #5
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Is there a workaround for no IFERROR function in 2003?

    Sorry I didn't mean it like that what I meant is that Excel didn't accept the formula;

    e.g.: =IF(ISERROR(ABS(MAX(J17:J18)),"",ABS(MAX(J17:J18))) returns "the formula you typed contains an error" so I'm figuring there is something I haven't spotted e.g. I was figuring as the IFERROR formula already contains a "", I didn't need to add another one (and even if I did it still returns an error).

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Is there a workaround for no IFERROR function in 2003?

    As you both know, IfError() didn't come out until Excel 2007,
    http://www.excel-examples.com/iserror.htm

    The site above tells how to get around it.

    My suggestion is to upgrade to the latest version of Excel.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Is there a workaround for no IFERROR function in 2003?

    what!?? is someone stating the obvious,and please send op $100 or so for office 2010
    i was explaining how iferror worked,so the results as posted were all "" if there was an error
    anyway
    =IF(ISERROR(ABS(MAX(J17:J18))),"",ABS(MAX(J17:J18))) is correct don't forget to close off the iserror
    but there should be no reason ABS(MAX(J17:J18)) should be an error unless there is already an error in J17:J18,so perhaps you should look at whats in those cells and stop any possible error occurring
    Last edited by martindwilson; 07-22-2012 at 11:52 AM.

  8. #8
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Is there a workaround for no IFERROR function in 2003?

    Thanks to the pair of you for the hints (I'll probably upgrade to 2007 when I switch laptop which can't be too far off as I'm using a last of the PPC's Mac which are slowly becoming extinct). I'm going to mark this solved I think as the =IF(ISERROR(..... is clearly the workaround, and I think the problem I may be having above is possibly due to me not interpreting the formula, with respect to the formulas in the original worksheet correctly if that makes sense (which is definitely the case with respect to Martin's post - I'd missed a bracket - which he was writing at the same time I was writing mine - hence the edit).
    Last edited by mrvp; 07-22-2012 at 11:49 AM. Reason: post happening at the same time as my post

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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