A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

NZ function equivalent in Excel



 
 
Thread Tools Display Modes
  #1  
Old July 11th 07, 06:02 PM posted to microsoft.public.excel.worksheet.functions
jg
external usenet poster
 
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!
Ads
  #2  
Old July 11th 07, 06:16 PM posted to microsoft.public.excel.worksheet.functions
Duke Carey
external usenet poster
 
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  
Old July 11th 07, 06:17 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
external usenet poster
 
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  
Old July 11th 07, 07:10 PM posted to microsoft.public.excel.worksheet.functions
jg
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!

  #5  
Old July 11th 07, 07:46 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
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  
Old July 11th 07, 09:04 PM posted to microsoft.public.excel.worksheet.functions
jg
external usenet poster
 
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  
Old July 11th 07, 11:04 PM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
external usenet poster
 
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!



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 03:01 AM
Looking for the equivalent of a Maxif function PerplexedinKY Excel Discussion (Misc queries) 6 January 20th 07 04: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 05:22 PM.


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