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. 


Thread Tools  Display Modes 
#1




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 nonnumeric/nonexistant, 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




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 > nonnumeric/nonexistant, 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




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 > nonnumeric/nonexistant, 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




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 > > nonnumeric/nonexistant, 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




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 > nonnumeric/nonexistant, 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




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 > > nonnumeric/nonexistant, 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




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 >> > nonnumeric/nonexistant, 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  


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 