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,HLO OKUP(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

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,'C algen
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,'C algen
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,HLO OKUP(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))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513374

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513374

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,'C algen
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,HLO OKUP(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,'C algen
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.