Help with TYPE function

[DMDave]

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]

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]

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?

 

[DMDave]

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

 

[Gord Dibben]

Try this formula

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


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

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?

Gord Dibben MS Excel MVP

 

[Dave Peterson]

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