IF(ISNA formula problem

[Guest]

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(A1,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...

 

[Harald Staff]

Hi

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

HTH. Best wishes Harald

 

[Guest]

is there no way around that?

Hi

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

HTH. Best wishes Harald

 

[JulieD]

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

 

[Guest]

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]

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

 

[Guest]

JulieD you are an unbelievable help. Now, if you could just get me a code,
macro, whatever so that my rows automatically sort in descending order for a
certain range based on column D.

 

[JulieD]

Hi Brad

what do you mean "for a certain range" based on column D?
if it is basically a normal data / sort type thing (click in a cell in your
data range, choose data / sort - choose column D - click OK) type sort then
record a macro doing just this - to make it automaticly happen you need to
decided on an event that will fire this .. do you want it to happen when a
change is made anywhere in the sheet, or when the sheet is selected or ??? -
a list of events you can use can be found by right mouse clicking on the
sheet, choose view code, and then choose worksheet from the lefthand drop
down box, and then have a look at the right, these are the events you can
code against. Once you've chosen your event, copy & paste the code from
your recorded macro in here and give it a go.

alternatively answer the questions i've asked above, let me know what event
you want to use to trigger the sort and i'll throw some code together for
you.

Cheers
JulieD