View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jg jg is offline
external usenet poster
 
Posts: 42
Default NZ function equivalent in Excel

Bummer news, but I appreciate the response. Thanks Duke!

"Duke Carey" wrote:

Unless you're using Excel 2007 you're likely out of luck.

In 2007 there is a new IFERROR(value1,value2) function that returns value1
if it is not an error, otherwise returns value2


"jg" wrote:

Does Excel have an equivalent to the Access function NZ? I want to avoid
having to perform a double VLOOKUP (one to determine if the value is
non-numeric/non-existant, the second to pull the actual value in the event it
is numeric).
E.g:
=IF(ISERROR(VLOOKUP(A1,M1:N30,2,FALSE)),0,VLOOKUP( A1,M1:N30,2,FALSE))

I've written a custom function to address it, but would rather not have
users with high security levels blocked from using the function.

For reference, the function I built is here (yes, it's not an exact
match...but it does what I need for now):

<snip
Public Function NZ(CellVal As Variant) As Variant

Select Case VarType(CellVal)
Case vbError
NZ = 0
Case vbInteger, vbLong, vbDouble, vbSingle, vbCurrency, vbDecimal, vbByte
NZ = CellVal
Case Else
NZ = "#WRONG_TYPE"
End Select

End Function
</snip

...such that the equivalent to the above is:
=NZ(VLOOKUP(A1,M1:N30,2,FALSE))

Thanks!