how to put 0 instead of #n/a

[Guest]

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

 

[Guest]

=IF(ISNA(your formula),0,your formula

 

[SteveW]

isna() function

something like this

=if(isna(lookup....),0,lookup....)
Steve

 

[Philippe L. Balmanno]

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

 

[Guest]

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

 

[Guest]

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))

 

[Guest]

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]

Use ISNA()
It's not an Error when it returns #N/A

Steve

 

[Dave Peterson]

=ISERROR(NA())
Returns TRUE.

Use ISNA()
It's not an Error when it returns #N/A

Steve

 

[Dave Peterson]

Maybe you were thinking of =iserr()?????

Use ISNA()
It's not an Error when it returns #N/A

Steve

 

[SteveW]

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_vector)

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))