Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 11th 07, 06:02 PM posted to microsoft.public.excel.worksheet.functions
jg jg is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 42
Default NZ function equivalent in Excel

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!

  #2   Report Post  
Old July 11th 07, 06:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,081
Default NZ function equivalent in Excel

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!

  #3   Report Post  
Old July 11th 07, 06:17 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 3,268
Default NZ function equivalent in Excel

Excel 2007 has a new function called IFERROR but not earlier versions

IFERROR(VLOOKUP(),0)

So you are stuck with either a UDF or

=IF(ISNUMBER(MATCH(A1,M1:M30,0)),VLOOKUP(A1,M1:N30 ,2,FALSE),0)

or

=IF(ISNA(VLOOKUP(A1,M1:N30,2,FALSE)),0,VLOOKUP(A1, M1:N30,2,FALSE))


--
Regards,

Peo Sjoblom



"jg" wrote in message
...
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!



  #4   Report Post  
Old July 11th 07, 07:10 PM posted to microsoft.public.excel.worksheet.functions
jg jg is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
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!

  #5   Report Post  
Old July 11th 07, 07:46 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 3,718
Default NZ function equivalent in Excel

Try this:

=IF(COUNTIF(M1:M30,A1),VLOOKUP(A1,M1:N30,2,0),0)


"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!



  #6   Report Post  
Old July 11th 07, 09:04 PM posted to microsoft.public.excel.worksheet.functions
jg jg is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 42
Default NZ function equivalent in Excel

If I'm not mistaken, this is better than what I had started with, but still
requires both a table scan and a separate aggregation. However, given my
version limitation (Office 2003), may be the best solution short of keeping
the custom formula.

Thanks Mama!
jg

"Teethless mama" wrote:

Try this:

=IF(COUNTIF(M1:M30,A1),VLOOKUP(A1,M1:N30,2,0),0)


"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!

  #7   Report Post  
Old July 11th 07, 11:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 3,268
Default NZ function equivalent in Excel

Note that COUNTIF does not make any difference between text numbers and
"real" numbers whereas MATCH and VLOOKUP do, so you can still get the #N/A!
error after passing the countif test.


--
Regards,

Peo Sjoblom


"jg" wrote in message
...
If I'm not mistaken, this is better than what I had started with, but
still
requires both a table scan and a separate aggregation. However, given my
version limitation (Office 2003), may be the best solution short of
keeping
the custom formula.

Thanks Mama!
jg

"Teethless mama" wrote:

Try this:

=IF(COUNTIF(M1:M30,A1),VLOOKUP(A1,M1:N30,2,0),0)


"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!





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there an Excel 2003 equivalent to Word's "versions" function? Steve Excel Discussion (Misc queries) 0 March 4th 07 02:01 AM
Looking for the equivalent of a Maxif function PerplexedinKY Excel Discussion (Misc queries) 6 January 20th 07 03:57 AM
WORKDAY() Function Equivalent with SUMPRODUCT() George Ray Excel Worksheet Functions 4 October 9th 06 04:04 PM
"MAXIF" Equivalent function in Excel Vital Miranda Excel Worksheet Functions 5 September 27th 06 11:56 PM
What is the Excel equivalent of the CELL function? JP Excel Worksheet Functions 8 September 5th 06 12:49 AM


All times are GMT +1. The time now is 03:44 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017