The result of a formula in my WS is #N/A. I would like to replace that
with 0. The formula I tried is:
=IF(TYPE(C144)=(#N/A),0,(VLOOKUP(NetAdj!C144*7,'Labor
Guideline'!$A$3:$C$75,2)+((NetAdj!C144*7-VLOOKUP(NetAdj!C144*7,'Labor
Guideline'!$A$3:$C$75,1))*0.016))/7)

I have also tried replacing the (#N/A) in the above formula with the
number 7 (The number which Excel says is returned when the TYPE is
#N/A.
I continue to get the #N/A result.
I also tried the ERROR.TYPE function with no luck.

Anyone know what I may be doing wrong?

Thank You,
Dave

You could use:

=if(isna(c144),0,....


If you decide that you don't care what type of error it is, you could use:

=if(iserror(c144),0,...

And remember that vlookup()'s can return #n/a's, too. You may want to make sure
c144 is a nice number and see what just the =vlookup() evaluates to.

DMDave wrote:

The result of a formula in my WS is #N/A. I would like to replace that
with 0. The formula I tried is:
=IF(TYPE(C144)=(#N/A),0,(VLOOKUP(NetAdj!C144*7,'Labor
Guideline'!$A$3:$C$75,2)+((NetAdj!C144*7-VLOOKUP(NetAdj!C144*7,'Labor
Guideline'!$A$3:$C$75,1))*0.016))/7)

I have also tried replacing the (#N/A) in the above formula with the
number 7 (The number which Excel says is returned when the TYPE is
#N/A.
I continue to get the #N/A result.
I also tried the ERROR.TYPE function with no luck.

Anyone know what I may be doing wrong?

Thank You,
Dave

--

Dave Peterson

Dave
I have used both of the options you provided but its still returning
#N/A. I know that those formulas should work.
As for the VLOOKUP portion, That portion works fine if there is data
available for the lookup. Since there is no data shouldn't it just
return 0?
Is there maybe an Excel setting that I'm missing?

Dave Peterson wrote:
You could use:

=if(isna(c144),0,....


If you decide that you don't care what type of error it is, you could use:

=if(iserror(c144),0,...

And remember that vlookup()'s can return #n/a's, too. You may want to make sure
c144 is a nice number and see what just the =vlookup() evaluates to.

DMDave wrote:

The result of a formula in my WS is #N/A. I would like to replace that
with 0. The formula I tried is:
=IF(TYPE(C144)=(#N/A),0,(VLOOKUP(NetAdj!C144*7,'Labor
Guideline'!$A$3:$C$75,2)+((NetAdj!C144*7-VLOOKUP(NetAdj!C144*7,'Labor
Guideline'!$A$3:$C$75,1))*0.016))/7)

I have also tried replacing the (#N/A) in the above formula with the
number 7 (The number which Excel says is returned when the TYPE is
#N/A.
I continue to get the #N/A result.
I also tried the ERROR.TYPE function with no luck.

Anyone know what I may be doing wrong?

Thank You,
Dave

--

Dave Peterson

Dave,
Nevermind, I missed a simple thing like calculate now.
Does Gray hair turn of certain memory cells in your brain? (g)

Try this formula

=IF(ISNA(VLOOKUP..),0,VLOOKUP(...))


IF(ISNA(VLOOKUP..),0,VLOOKUP(...))

On 7 May 2006 09:47:13 -0700, "DMDave" wrote:

Dave
I have used both of the options you provided but its still returning
#N/A. I know that those formulas should work.
As for the VLOOKUP portion, That portion works fine if there is data
available for the lookup. Since there is no data shouldn't it just
return 0?
Is there maybe an Excel setting that I'm missing?

Dave Peterson wrote:
You could use:

=if(isna(c144),0,....


If you decide that you don't care what type of error it is, you could use:

=if(iserror(c144),0,...

And remember that vlookup()'s can return #n/a's, too. You may want to make sure
c144 is a nice number and see what just the =vlookup() evaluates to.

DMDave wrote:

The result of a formula in my WS is #N/A. I would like to replace that
with 0. The formula I tried is:
=IF(TYPE(C144)=(#N/A),0,(VLOOKUP(NetAdj!C144*7,'Labor
Guideline'!$A$3:$C$75,2)+((NetAdj!C144*7-VLOOKUP(NetAdj!C144*7,'Labor
Guideline'!$A$3:$C$75,1))*0.016))/7)

I have also tried replacing the (#N/A) in the above formula with the
number 7 (The number which Excel says is returned when the TYPE is
#N/A.
I continue to get the #N/A result.
I also tried the ERROR.TYPE function with no luck.

Anyone know what I may be doing wrong?

Thank You,
Dave

--

Dave Peterson

Gord Dibben MS Excel MVP

I think gray is an indicator of loss of brain cells--not a cause. <vbg

DMDave wrote:

Dave,
Nevermind, I missed a simple thing like calculate now.
Does Gray hair turn of certain memory cells in your brain? (g)

--

Dave Peterson