| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to put 0 instead of #n/a
I am putting Lookup function.suppose if value is not there in table array.
it is giving #N/A. I want 0 instead of #N/A. How ? Pleae help me. Regd. Vas |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to put 0 instead of #n/a
=IF(ISNA(your formula),0,your formula
"Vas" wrote: I am putting Lookup function.suppose if value is not there in table array. it is giving #N/A. I want 0 instead of #N/A. How ? Pleae help me. Regd. Vas |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to put 0 instead of #n/a
isna() function
something like this =if(isna(lookup....),0,lookup....) Steve On Sun, 28 Jan 2007 13:44:00 -0000, Vas wrote: I am putting Lookup function.suppose if value is not there in table array. it is giving #N/A. I want 0 instead of #N/A. How ? Pleae help me. Regd. Vas |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to put 0 instead of #n/a
"Vas" wrote in message
... I am putting Lookup function.suppose if value is not there in table array. it is giving #N/A. I want 0 instead of #N/A. How ? Pleae help me. Regd. Vas You need to post more info like a formula and an error check can then be built into it. You could use a formula to check for an error ISBLANK(value) ISERR(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) or use a more complex formula starting with =IF(ISERROR{or ISwhatever}(your formula,0,your formula)) you could develope an error sheet with If error_val is ERROR.TYPE returns A 1 #NULL! B 1 2 #DIV/0! 2 3 #VALUE! 3 4 #REF! 4 5 #NAME? 5 6 #NUM! 6 1 #N/A 7 and this formula within your formula =IF(ERROR.TYPE(A3)<3,CHOOSE(ERROR.TYPE(A3),"Ranges do not intersect","The divisor is zero")) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to put 0 instead of #n/a
Another solution, which might be even better than a 0, would be to go to Page
Setup, select the Sheet tab, and for "Cell errors as:" select <blank. This will give you an empty cell when you print your page for any cells which show on the screen as #N/A. "Vas" wrote: I am putting Lookup function.suppose if value is not there in table array. it is giving #N/A. I want 0 instead of #N/A. How ? Pleae help me. Regd. Vas |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to put 0 instead of #n/a
Hi,
Please check my formula. still i am not able to put this formula. it shows error. =IF(ISERROR(LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000),0,LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000)) "Philippe L. Balmanno" wrote: "Vas" wrote in message ... I am putting Lookup function.suppose if value is not there in table array. it is giving #N/A. I want 0 instead of #N/A. How ? Pleae help me. Regd. Vas You need to post more info like a formula and an error check can then be built into it. You could use a formula to check for an error ISBLANK(value) ISERR(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) or use a more complex formula starting with =IF(ISERROR{or ISwhatever}(your formula,0,your formula)) you could develope an error sheet with If error_val is ERROR.TYPE returns A 1 #NULL! B 1 2 #DIV/0! 2 3 #VALUE! 3 4 #REF! 4 5 #NAME? 5 6 #NUM! 6 1 #N/A 7 and this formula within your formula =IF(ERROR.TYPE(A3)<3,CHOOSE(ERROR.TYPE(A3),"Ranges do not intersect","The divisor is zero")) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to put 0 instead of #n/a
Hi
i am puting this formula but still it showing error. =IF(ISNA(LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000),0,LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000)) "SteveW" wrote: isna() function something like this =if(isna(lookup....),0,lookup....) Steve On Sun, 28 Jan 2007 13:44:00 -0000, Vas wrote: I am putting Lookup function.suppose if value is not there in table array. it is giving #N/A. I want 0 instead of #N/A. How ? Pleae help me. Regd. Vas |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to put 0 instead of #n/a
Use ISNA()
It's not an Error when it returns #N/A Steve On Sun, 28 Jan 2007 17:11:00 -0000, Vas wrote: Hi, Please check my formula. still i am not able to put this formula. it shows error. =IF(ISERROR(LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000),0,LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000)) "Philippe L. Balmanno" wrote: "Vas" wrote in message ... I am putting Lookup function.suppose if value is not there in table array. it is giving #N/A. I want 0 instead of #N/A. How ? Pleae help me. Regd. Vas You need to post more info like a formula and an error check can then be built into it. You could use a formula to check for an error ISBLANK(value) ISERR(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) or use a more complex formula starting with =IF(ISERROR{or ISwhatever}(your formula,0,your formula)) you could develope an error sheet with If error_val is ERROR.TYPE returns A 1 #NULL! B 1 2 #DIV/0! 2 3 #VALUE! 3 4 #REF! 4 5 #NAME? 5 6 #NUM! 6 1 #N/A 7 and this formula within your formula =IF(ERROR.TYPE(A3)<3,CHOOSE(ERROR.TYPE(A3),"Ranges do not intersect","The divisor is zero")) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to put 0 instead of #n/a
=ISERROR(NA())
Returns TRUE. SteveW wrote: Use ISNA() It's not an Error when it returns #N/A Steve On Sun, 28 Jan 2007 17:11:00 -0000, Vas wrote: Hi, Please check my formula. still i am not able to put this formula. it shows error. =IF(ISERROR(LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000),0,LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000)) "Philippe L. Balmanno" wrote: "Vas" wrote in message ... I am putting Lookup function.suppose if value is not there in table array. it is giving #N/A. I want 0 instead of #N/A. How ? Pleae help me. Regd. Vas You need to post more info like a formula and an error check can then be built into it. You could use a formula to check for an error ISBLANK(value) ISERR(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) or use a more complex formula starting with =IF(ISERROR{or ISwhatever}(your formula,0,your formula)) you could develope an error sheet with If error_val is ERROR.TYPE returns A 1 #NULL! B 1 2 #DIV/0! 2 3 #VALUE! 3 4 #REF! 4 5 #NAME? 5 6 #NUM! 6 1 #N/A 7 and this formula within your formula =IF(ERROR.TYPE(A3)<3,CHOOSE(ERROR.TYPE(A3),"Ranges do not intersect","The divisor is zero")) -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to put 0 instead of #n/a
Maybe you were thinking of =iserr()?????
SteveW wrote: Use ISNA() It's not an Error when it returns #N/A Steve On Sun, 28 Jan 2007 17:11:00 -0000, Vas wrote: Hi, Please check my formula. still i am not able to put this formula. it shows error. =IF(ISERROR(LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000),0,LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000)) "Philippe L. Balmanno" wrote: "Vas" wrote in message ... I am putting Lookup function.suppose if value is not there in table array. it is giving #N/A. I want 0 instead of #N/A. How ? Pleae help me. Regd. Vas You need to post more info like a formula and an error check can then be built into it. You could use a formula to check for an error ISBLANK(value) ISERR(value) ISERROR(value) ISLOGICAL(value) ISNA(value) ISNONTEXT(value) ISNUMBER(value) ISREF(value) ISTEXT(value) or use a more complex formula starting with =IF(ISERROR{or ISwhatever}(your formula,0,your formula)) you could develope an error sheet with If error_val is ERROR.TYPE returns A 1 #NULL! B 1 2 #DIV/0! 2 3 #VALUE! 3 4 #REF! 4 5 #NAME? 5 6 #NUM! 6 1 #N/A 7 and this formula within your formula =IF(ERROR.TYPE(A3)<3,CHOOSE(ERROR.TYPE(A3),"Ranges do not intersect","The divisor is zero")) -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how to put 0 instead of #n/a
On Sun, 28 Jan 2007 17:12:00 -0000, Vas
wrote: Hi i am puting this formula but still it showing error. =IF(ISNA( LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000) Is this right, lookup syntax is "LOOKUP(lookup_value,lookup_vector,result_vect or) Lookup_value is a value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. Lookup_vector is a range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values." so you are looking up 2 in 1/(.....) - looks wrong to me Steve ,0,LOOKUP(2,1/((B2='Data-hokair'!$A$1:$A$1000)*(Data!$O$1='Data-hokair'!$C$1:$C$1000)),'Data-hokair'!$D$1:$D$1000)) "SteveW" wrote: isna() function something like this =if(isna(lookup....),0,lookup....) Steve On Sun, 28 Jan 2007 13:44:00 -0000, Vas wrote: I am putting Lookup function.suppose if value is not there in table array. it is giving #N/A. I want 0 instead of #N/A. How ? Pleae help me. Regd. Vas |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
