ISNA() generating #N/A ?? 4

[Loomah]

Hi all
This isn't really a life or death situation. My original question was going to be:-
why does this work
=IF(ISNA(AS2),AM2*(1+Uplift2),IF(AS2=0,AM2*(1+Uplift2),AS2))

and this doesn't (generates #N/A error)
=IF(OR(AS2=0,ISNA(AS2)),AM2*(1+Uplift2),AS2)

But just before posting I checked the elements of this formula and found that =OR(AS2=0,ISNA(AS2)) generates #N/A error if ISNA(AS2) is true. And only is isna() is true.

=ISNA(AS2) used in isolation works just fine!

Am I missing something (again) or is this another 'feature' of Excel? BTW I'm using xl97

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

OR can be funny in some instances

something about how it returns a true value if one of the elements meets the criteria



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

and this doesn't (generates #N/A error)
=IF(OR(AS2=0,ISNA(AS2)),AM2*(1+Uplift2),AS2)

that's because the OR(AS2=0 will generate the #N/A error. Your other IF function will execute the tests from left to right ( ish ), whereas the OR will return results based on what's happening in every test.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TonyJollans]

All parts of an OR are evaluated in determining the result. So if AS2 is #N/A the check for zero returns #N/A, which 'overrides' the results of other conditions. If you want to get the result you are probably expecting you need to use nested IFs, something like ...

[blue][tt]=IF(ISNA(AS2),TRUE,IF(AS2=0,TRUE,FALSE))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[xlbo]

Thanks Glenn & Tony for explaining that - could never satisfactorily explain it - the bit about it evaluating ALL results rather than just the results until a TRUE answer is found is the key.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Loomah]

Cheers Gents

I was aware that OR evaluates all argumants but missed out on the fact that A1=0 returns #N/A if A1 is #N/A. I just thought it would return FALSE if A1's value wasn't 0.

Ho-hum!

As I said in my original post this wasn't a matter of life and death and I already had my formula. It was just a case of why doesn't something that seems perfectly logical work in the way I think it should!!

Thanks again and have a purple pointy pip! I'm off to try and work out why a file has doubled in size this morning, why Dr Watson always visits me at busiest times and to prey for the proposed upgrade to xl2k (yes, upgrade to xl2k!!)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​