+ Reply to Thread
Results 1 to 12 of 12

replacing #N/A values with zero values

  1. #1
    Ted
    Guest

    replacing #N/A values with zero values

    replacing #N/A values with zero values

    I have a cell that is looking up values in another sheet using HLOOKUP....
    when it doesn't find a value it returns a #N/A value....
    I have tried using an IF statement with an imbedded ISNA or ISERROR or
    ERROR.TYPE function to have it return a zero in the event of #N/A and the
    actual value if it is able to find what it is looking up....
    I need to convert the #N/As to zeros so I can use the sum function by merely
    highlighting a range with my mouse...

    Thanks.

  2. #2
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094
    Ted,

    You are correct that you should combine the IF, ISERROR (or ISNA) with your lookup. This worked for me. Maybe the syntax was off in your attempts.

    =IF(ISERROR(HLOOKUP(6,Sheet1!A1:D2,2,FALSE)),0,HLOOKUP(6,Sheet1!A1:D2,2,FALSE))

    Does that help?

    Steve

  3. #3
    Elkar
    Guest

    RE: replacing #N/A values with zero values

    It sounds like you're on the right track. An IF() function with an ISERROR()
    function should work:

    =IF(ISERROR(HLOOKUP(...))=TRUE,0,HLOOKUP(...))

    If it still isn't working, try posting your formula, maybe there is a simple
    syntax problem.

    HTH,
    Elkar

    "Ted" wrote:

    > replacing #N/A values with zero values
    >
    > I have a cell that is looking up values in another sheet using HLOOKUP....
    > when it doesn't find a value it returns a #N/A value....
    > I have tried using an IF statement with an imbedded ISNA or ISERROR or
    > ERROR.TYPE function to have it return a zero in the event of #N/A and the
    > actual value if it is able to find what it is looking up....
    > I need to convert the #N/As to zeros so I can use the sum function by merely
    > highlighting a range with my mouse...
    >
    > Thanks.


  4. #4
    Ted
    Guest

    RE: replacing #N/A values with zero values

    Here is the formula I used:
    =IF(ISERROR(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant
    Financing'!E$4,'Calgen
    NC'!$D$5:$D$10,0),FALSE))="TRUE",0,(HLOOKUP($A6,'Calgen
    NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)))

    "Elkar" wrote:

    > It sounds like you're on the right track. An IF() function with an ISERROR()
    > function should work:
    >
    > =IF(ISERROR(HLOOKUP(...))=TRUE,0,HLOOKUP(...))
    >
    > If it still isn't working, try posting your formula, maybe there is a simple
    > syntax problem.
    >
    > HTH,
    > Elkar
    >
    > "Ted" wrote:
    >
    > > replacing #N/A values with zero values
    > >
    > > I have a cell that is looking up values in another sheet using HLOOKUP....
    > > when it doesn't find a value it returns a #N/A value....
    > > I have tried using an IF statement with an imbedded ISNA or ISERROR or
    > > ERROR.TYPE function to have it return a zero in the event of #N/A and the
    > > actual value if it is able to find what it is looking up....
    > > I need to convert the #N/As to zeros so I can use the sum function by merely
    > > highlighting a range with my mouse...
    > >
    > > Thanks.


  5. #5
    Ted
    Guest

    Re: replacing #N/A values with zero values

    Here is the formula I tried using: =IF(ISERROR(HLOOKUP($A6,'Calgen
    NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen
    NC'!$D$5:$D$10,0),FALSE))="TRUE",0,(HLOOKUP($A6,'Calgen
    NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)))

    "SteveG" wrote:

    >
    > Ted,
    >
    > You are correct that you should combine the IF, ISERROR (or ISNA) with
    > your lookup. This worked for me. Maybe the syntax was off in your
    > attempts.
    >
    > =IF(ISERROR(HLOOKUP(6,Sheet1!A1:D2,2,FALSE)),0,HLOOKUP(6,Sheet1!A1:D2,2,FALSE))
    >
    > Does that help?
    >
    > Steve
    >
    >
    > --
    > SteveG
    > ------------------------------------------------------------------------
    > SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
    > View this thread: http://www.excelforum.com/showthread...hreadid=513374
    >
    >


  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    You don't need quotes around TRUE, in fact you don't need ="TRUE" at all. Also if you're only experiencing #N/A errors it's better to use ISNA rather than ISERROR otherwise you risk masking errors that you might want to know about such as a misspelt function in your formula

    Try

    =IF(ISNA(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),0)),0,HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),0))

  7. #7
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094
    Ted,

    Try it like this,

    =IF(ISERROR(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)),0,HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE))

    You did not need the ="TRUE" because the ISERROR provides that in the formula. The start of your If False formula had a ( which it did not need.

    Steve

  8. #8
    Kevin Vaughn
    Guest

    Re: replacing #N/A values with zero values

    I would say that you don't need the ="TRUE" part and if you insist on using
    it, try getting rid of the quotes around it.
    --
    Kevin Vaughn


    "Ted" wrote:

    > Here is the formula I tried using: =IF(ISERROR(HLOOKUP($A6,'Calgen
    > NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen
    > NC'!$D$5:$D$10,0),FALSE))="TRUE",0,(HLOOKUP($A6,'Calgen
    > NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)))
    >
    > "SteveG" wrote:
    >
    > >
    > > Ted,
    > >
    > > You are correct that you should combine the IF, ISERROR (or ISNA) with
    > > your lookup. This worked for me. Maybe the syntax was off in your
    > > attempts.
    > >
    > > =IF(ISERROR(HLOOKUP(6,Sheet1!A1:D2,2,FALSE)),0,HLOOKUP(6,Sheet1!A1:D2,2,FALSE))
    > >
    > > Does that help?
    > >
    > > Steve
    > >
    > >
    > > --
    > > SteveG
    > > ------------------------------------------------------------------------
    > > SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
    > > View this thread: http://www.excelforum.com/showthread...hreadid=513374
    > >
    > >


  9. #9
    Dave Peterson
    Guest

    Re: replacing #N/A values with zero values

    Drop this portion completely:
    ="TRUE"
    or change it to:
    =TRUE

    (remove the quotes)


    Ted wrote:
    >
    > Here is the formula I used:
    > =IF(ISERROR(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant
    > Financing'!E$4,'Calgen
    > NC'!$D$5:$D$10,0),FALSE))="TRUE",0,(HLOOKUP($A6,'Calgen
    > NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)))
    >
    > "Elkar" wrote:
    >
    > > It sounds like you're on the right track. An IF() function with an ISERROR()
    > > function should work:
    > >
    > > =IF(ISERROR(HLOOKUP(...))=TRUE,0,HLOOKUP(...))
    > >
    > > If it still isn't working, try posting your formula, maybe there is a simple
    > > syntax problem.
    > >
    > > HTH,
    > > Elkar
    > >
    > > "Ted" wrote:
    > >
    > > > replacing #N/A values with zero values
    > > >
    > > > I have a cell that is looking up values in another sheet using HLOOKUP....
    > > > when it doesn't find a value it returns a #N/A value....
    > > > I have tried using an IF statement with an imbedded ISNA or ISERROR or
    > > > ERROR.TYPE function to have it return a zero in the event of #N/A and the
    > > > actual value if it is able to find what it is looking up....
    > > > I need to convert the #N/As to zeros so I can use the sum function by merely
    > > > highlighting a range with my mouse...
    > > >
    > > > Thanks.


    --

    Dave Peterson

  10. #10
    Pete_UK
    Guest

    Re: replacing #N/A values with zero values

    Remove the quotes from around the TRUE in the middle of the formula. In
    fact you don't need that little part, so your formula can become:

    =IF(ISERROR(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,
    MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)),0,
    (HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,
    MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)))

    Hope this helps.

    Pete


  11. #11
    Harlan Grove
    Guest

    Re: replacing #N/A values with zero values

    Ted wrote...
    >Here is the formula I used:
    >=IF(ISERROR(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,
    >MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE))="TRUE",0,
    >(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,
    >'Calgen NC'!$D$5:$D$10,0),FALSE)))

    ....

    Others have pointed out the ="TRUE" issue, but I'll note that you could
    compress this a bit.

    =IF(COUNTIF('Calgen NC'!$D$5:$T$5,$A6)
    *COUNTIF('Calgen NC'!$D$5:$D$10,'Plant Financing'!E$4),
    HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,
    'Calgen NC'!$D$5:$D$10,0),0),0)


  12. #12
    Ted
    Guest

    RE: replacing #N/A values with zero values

    Thanks everyone...
    problem solved...you were right about the syntax..
    Short answer is this;

    =if(iserror(Hllookup(Detail)),0,

    in other words I should have used a comma instead of an equal sign...

    "Ted" wrote:

    > replacing #N/A values with zero values
    >
    > I have a cell that is looking up values in another sheet using HLOOKUP....
    > when it doesn't find a value it returns a #N/A value....
    > I have tried using an IF statement with an imbedded ISNA or ISERROR or
    > ERROR.TYPE function to have it return a zero in the event of #N/A and the
    > actual value if it is able to find what it is looking up....
    > I need to convert the #N/As to zeros so I can use the sum function by merely
    > highlighting a range with my mouse...
    >
    > Thanks.


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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