Hello Mr Excel world, help needed with IF, AND & ISNA combo

[Trevken]

Hi fellow Excel lovers,

I need help to fix this seemingly simple formula but so far it's eluding me;

=IF(AND (ISNA(R2),ISNA(U2),ISNA(X2)),"Needs Subbing", "Subbed")

I've tried it with =TRUE after each reference and at the end of them and rearranging the brackets structure.

I have a feeling I'm being dumb and it's something small but I can't see it.

Any suggestions?

Thanks,

Trevor

 

[ianraff]

Re: Hello Mr Excel world, help needed with combination IF, AND & ISNA combo

What are the values in "R2", "U2" and "X2"? ISNA checks whether a value is #N/A and returns true or false. What are you trying to accomplish?

 

[Trevken]

Re: Hello Mr Excel world, help needed with combination IF, AND & ISNA combo

Hi, wow that's fast!

Basically if all those cells contain an N/A then I want it to say Needs Subbing.

Thanks

 

[ianraff]

Re: Hello Mr Excel world, help needed with combination IF, AND & ISNA combo

Do they all have to contain #N/A at the same time? If you're testing to see if only 1 or 2 are #N/A try using =IF(OR(ISNA(R2),ISNA(U2),ISNA(X2)),"Needs Subbing", "Subbed")

What exactly is happening when you enter that formula? Are you getting an error or is it saying false?

 

[Teeroy]

Re: Hello Mr Excel world, help needed with combination IF, AND & ISNA combo

Firstly the space after "AND" will cause the formula to fail. Secondly are the values in "R2", "U2" and "X2" the "#N/A" error or the text "N/A".

 

[Trevken]

Re: Hello Mr Excel world, help needed with combination IF, AND & ISNA combo

Hi, Thanks for that, I will probably use that also at some point so that's really useful.

But for now I want it to say "Needs Subbing" if ALL of the calls contain the error #N/A.

 

[Trevken]

Re: Hello Mr Excel world, help needed with combination IF, AND & ISNA combo

Firstly the space after "AND" will cause the formula to fail. Secondly are the values in "R2", "U2" and "X2" the "#N/A" error or the text "N/A".

I've not found having random space makes it fail in this version but it does fail when I remove the space anyway, try it.

And yes I mean the error #N/A

 

[ianraff]

Re: Hello Mr Excel world, help needed with combination IF, AND & ISNA combo

=IF(AND( should be the correct formula to use... What is the result when you enter that formula?

Teeroy is corrrect, but I would imagine it would give you the prompt to autocorrect the spaces if there were any? Any more details?

 

[Trevken]

Re: Hello Mr Excel world, help needed with combination IF, AND & ISNA combo

It just says "The formula you have types contains an error" message

 

[Trevken]

Re: Hello Mr Excel world, help needed with combination IF, AND & ISNA combo

Do they all have to contain #N/A at the same time? If you're testing to see if only 1 or 2 are #N/A try using =IF(OR(ISNA(R2),ISNA(U2),ISNA(X2)),"Needs Subbing", "Subbed")

What exactly is happening when you enter that formula? Are you getting an error or is it saying false?

I really don't know what I typed wrong, other than the extra space, but when I used your formula instead of mine but swapped the OR for an AND and it worked!

Strange, it looks just the same!!

It's =IF(AND(ISNA(R2),ISNA(U2),ISNA(X2)),"Needs Subbing", "Subbed")

Works fine now, thanks! Must have been some syntax error like spaces as Teeroy suggested.

Thanks both!