Excel 2007 : If and Or Statement

[Jerseynjphillypa]

I am trying to get my if and or function to work but I am having problem

If Col1 and Col2 are #N/A I want a 0 to show up

If (Col1>=Col2,Col1,Col2)

But this is the part that I can't get working

If Col1 has a value but Col2 is #N/A I get a value of 0 with my function but I need the Col 1 value

Also if Col2 has a value but Col1 is #N/A I need the Col2 value

here is the function I am using

=IF(OR(ISNA(A2),ISNA(B2)),0,IF(A2>=B2,A2,B2))


Thanks for your help

[Whizbang]

If Col1 and Col2 are #N/A I want a 0 to show up

AND(ISNA(A2),ISNA(B2))

If Col1 has a value but Col2 is #N/A I get a value of 0 with my function but I need the Col 1 value

Also if Col2 has a value but Col1 is #N/A I need the Col2 value

=IF(AND(ISNA(A2),ISNA(B2)),0,IF(ISNA(A2),B2,A2))

What happens if they are both not NA? Do you want the max?

Try this:

=MAX(NOT(ISNA(A2))*A2,NOT(ISNA(B2))*B2)

[JosephP]

how about
=MAX(IFERROR(A2,0),IFERROR(B2,0))
it just treats error values like 0

[Whizbang]

Oops. I hadn't tested my formula.

JosephP beat me to it:

=MAX(IFERROR(A2,0),IFERROR(B2,0))


IFERROR() is different than ISNA, though. Is there a specific reason you are testing for #N/A, or do you want to catch any error?

[jraj1106]

Hello..

Try this one and check:

=IF(AND(ISNA(A2),ISNA(B3)),0,IF(AND(ISNUMBER(A2),ISNA(B2)),A2,IF(AND(ISNA(A2),ISNUMBER(B2)),B2,IF(AND(ISNUMBER(A2),ISNUMBER(B2)),MAX(A2,B2),""))))

Regards...

[Jerseynjphillypa]

AND(ISNA(A2),ISNA(B2))




=IF(AND(ISNA(A2),ISNA(B2)),0,IF(ISNA(A2),B2,A2))

What happens if they are both not NA? Do you want the max?

Try this:

=MAX(NOT(ISNA(A2))*A2,NOT(ISNA(B2))*B2)

Yes I want the Max value if both are not NA

Also if I tried the function =MAX(NOT(ISNA(A2))*A2,NOT(ISNA(B2))*B2), I don't get the value as 0 anymore like the first function you provided.

Thanks

[Jerseynjphillypa]

Oops. I hadn't tested my formula.

JosephP beat me to it:

=MAX(IFERROR(A2,0),IFERROR(B2,0))


IFERROR() is different than ISNA, though. Is there a specific reason you are testing for #N/A, or do you want to catch any error?

The reason I have #N/A is because when i do a vlookup some of the values are not being found from the reference table

Thanks for your help

[Jerseynjphillypa]

Hello..

Try this one and check:



Regards...

I notice your function is calling B3, the values should stay within the row only.

Thanks

[Jerseynjphillypa]

how about
=MAX(IFERROR(A2,0),IFERROR(B2,0))
it just treats error values like 0

Thanks for your help

[jraj1106]

Sorry..That was a mistake... its B2 and not B3