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.

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

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.

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.

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
>
>

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))

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

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
> >
> >

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

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

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)

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.