IFs, ANDs, and BUTs

[Karlisa]

Need help finding a formula for the following conditions:

If AV14="Yes" AND AV76 is a number, then enter the value of AV76, BUT if AV14="No" then enter "N/A".

Any help would be appreciated. Thank you!

 

[Joe4]

Re: Need Help with IFs, ANDs, and BUTs

What if AV14 is "Yes", but AV76 is NOT a number?

If that is to be "N/A" also, then try:

=IF(AND(AV14="Yes",ISNUMBER(AV76)),AV76,"N/A")

 

[Karlisa]

Re: Need Help with IFs, ANDs, and BUTs

Thanks for that, Joe4. If the other conditions from my first post are met but if AV76 is NOT a number then the cell could say "Hold". If AV14="No", then it doesn't matter what's in AV76. The cell with the formula needs to say "N/A".

 

[Special-K99]

Re: Need Help with IFs, ANDs, and BUTs

This should work

=IF(AV14="Yes",IF(ISNUMBER(AV76),AV76,"Hold"),"N/A")

 

[Joe4]

Re: Need Help with IFs, ANDs, and BUTs

Special-K99s modification should do what you want.

In the future, be sure to include all the conditions in your questions, or else the answers you get might not work for you!

 

[Karlisa]

Re: Need Help with IFs, ANDs, and BUTs

Thank you for your response and advice, Special-K99. Unfortunately, your solution did not work, either. AV14="Yes", AV76 is not a number, but the response populated with "N/A". If AV14="Yes", the cell should not contain "N/A" regardless of what is in AV76.

 

[Special-K99]

Re: Need Help with IFs, ANDs, and BUTs

Thank you for your response and advice, Special-K99. Unfortunately, your solution did not work, either. AV14="Yes", AV76 is not a number, but the response populated with "N/A". If AV14="Yes", the cell should not contain "N/A" regardless of what is in AV76.

Nope, that's impossible.
If you look at the second IF condition, the ISNUMBER() condition, the only possible results for TRUE and FALSE are AV76 or "Hold".
The only way for the result to be N/A is if AV14 is NOT "Yes"

In a blank cell put =(AV14="Yes").
It should say TRUE.
If it says FALSE then AV14 is NOT "Yes" (no matter what it looks like).
(You probably have a space or CHAR(160) after "Yes" - CHAR(160) is common when data has been copied from the web.

If you're still having trouble upload the file to an online storage site and post a link to it so we can examine the data.

 

[tyija1995]

Re: Need Help with IFs, ANDs, and BUTs

To see if there's any trailing spaces you can try =LEN(AV14) - LEN(TRIM(AV14)), this should return 0 if the cell is clean, i.e. ="yes" exactly, otherwise if it returns 1 or greater then you will have "yes " etc!

 

[Karlisa]

Re: Need Help with IFs, ANDs, and BUTs

Nope, that's impossible.
If you look at the second IF condition, the ISNUMBER() condition, the only possible results for TRUE and FALSE are AV76 or "Hold".
The only way for the result to be N/A is if AV14 is NOT "Yes"

In a blank cell put =(AV14="Yes").
It should say TRUE.
If it says FALSE then AV14 is NOT "Yes" (no matter what it looks like).
(You probably have a space or CHAR(160) after "Yes" - CHAR(160) is common when data has been copied from the web.

If you're still having trouble upload the file to an online storage site and post a link to it so we can examine the data.

Thank you again for your assistance. With trial and error based on your input, I believe I have figured it out with the following formula:

=IF(AND(ISNUMBER(C76),C14="Yes"),C76,IF(C14<>"Yes","N/A","Hold"))