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

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

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

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

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

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

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

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

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

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

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