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.

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.

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)

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

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

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

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

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.