I'm using the above formula to return a blank cell or a 0 if my vlookup
returns an N/A. Where this is a problem is that I have a forumlua
=vlookup(A1,range,6,false)-I1. When I use
=IF(ISNA(vlookup(A1,range,6,false)),"0",(vlookup(A 1,range,6,false))-I9 I get
#VALUE! instead of 0. Why? Is there anyway around that? I'm trying to
automate my sheet as much as possible so that I don't have to spend time
deleting formulas etc...

Hi

"0" is text, 0 is a number. Since you use "0" in a calculation, it will err.

HTH. Best wishes Harald

"Brad" skrev i melding
...
I'm using the above formula to return a blank cell or a 0 if my vlookup
returns an N/A. Where this is a problem is that I have a forumlua
=vlookup(A1,range,6,false)-I1. When I use
=IF(ISNA(vlookup(A1,range,6,false)),"0",(vlookup(A 1,range,6,false))-I9 I
get
#VALUE! instead of 0. Why? Is there anyway around that? I'm trying to
automate my sheet as much as possible so that I don't have to spend time
deleting formulas etc...

is there no way around that?

"Harald Staff" wrote:

Hi

"0" is text, 0 is a number. Since you use "0" in a calculation, it will err.

HTH. Best wishes Harald

"Brad" skrev i melding
...
I'm using the above formula to return a blank cell or a 0 if my vlookup
returns an N/A. Where this is a problem is that I have a forumlua
=vlookup(A1,range,6,false)-I1. When I use
=IF(ISNA(vlookup(A1,range,6,false)),"0",(vlookup(A 1,range,6,false))-I9 I
get
#VALUE! instead of 0. Why? Is there anyway around that? I'm trying to
automate my sheet as much as possible so that I don't have to spend time
deleting formulas etc...

Hi Brad

rewrite your formula as
=IF(ISNA(vlookup(A1,range,6,false)),0,(vlookup(A1, range,6,false))-I9

you only need the " " when you are talking about text.

Cheers
JulieD

"Brad" wrote in message
...
is there no way around that?

"Harald Staff" wrote:

Hi

"0" is text, 0 is a number. Since you use "0" in a calculation, it will
err.

HTH. Best wishes Harald

"Brad" skrev i melding
...
I'm using the above formula to return a blank cell or a 0 if my vlookup
returns an N/A. Where this is a problem is that I have a forumlua
=vlookup(A1,range,6,false)-I1. When I use
=IF(ISNA(vlookup(A1,range,6,false)),"0",(vlookup(A 1,range,6,false))-I9
I
get
#VALUE! instead of 0. Why? Is there anyway around that? I'm trying
to
automate my sheet as much as possible so that I don't have to spend
time
deleting formulas etc...

excellent, that worked. Thanks. How about if a division now is returning
#DIV/O. Can I create a similar function in that cell so that it returns a 0

"JulieD" wrote:

Hi Brad

rewrite your formula as
=IF(ISNA(vlookup(A1,range,6,false)),0,(vlookup(A1, range,6,false))-I9

you only need the " " when you are talking about text.

Cheers
JulieD

"Brad" wrote in message
...
is there no way around that?

"Harald Staff" wrote:

Hi

"0" is text, 0 is a number. Since you use "0" in a calculation, it will
err.

HTH. Best wishes Harald

"Brad" skrev i melding
...
I'm using the above formula to return a blank cell or a 0 if my vlookup
returns an N/A. Where this is a problem is that I have a forumlua
=vlookup(A1,range,6,false)-I1. When I use
=IF(ISNA(vlookup(A1,range,6,false)),"0",(vlookup(A 1,range,6,false))-I9
I
get
#VALUE! instead of 0. Why? Is there anyway around that? I'm trying
to
automate my sheet as much as possible so that I don't have to spend
time
deleting formulas etc...

Hi Brad

you're welcom .. as to your new question, yes, you'll need to use the
ISERROR function (which is a pretty broad function, covers lots of errors -
N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL)

e.g.
=IF(ISERROR(A1/B1),0,A1/B1)

Cheers
JulieD


"Brad" wrote in message
...
excellent, that worked. Thanks. How about if a division now is returning
#DIV/O. Can I create a similar function in that cell so that it returns a
0

"JulieD" wrote:

Hi Brad

rewrite your formula as
=IF(ISNA(vlookup(A1,range,6,false)),0,(vlookup(A1, range,6,false))-I9

you only need the " " when you are talking about text.

Cheers
JulieD

"Brad" wrote in message
...
is there no way around that?

"Harald Staff" wrote:

Hi

"0" is text, 0 is a number. Since you use "0" in a calculation, it
will
err.

HTH. Best wishes Harald

"Brad" skrev i melding
...
I'm using the above formula to return a blank cell or a 0 if my
vlookup
returns an N/A. Where this is a problem is that I have a forumlua
=vlookup(A1,range,6,false)-I1. When I use
=IF(ISNA(vlookup(A1,range,6,false)),"0",(vlookup(A 1,range,6,false))-I9
I
get
#VALUE! instead of 0. Why? Is there anyway around that? I'm
trying
to
automate my sheet as much as possible so that I don't have to spend
time
deleting formulas etc...