IFs, ANDs, and BUTs

Karlisa

New Member
Joined
Aug 7, 2019
Messages
7
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!
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,412
Office Version
365
Platform
Windows
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:
Code:
=IF(AND(AV14="Yes",ISNUMBER(AV76)),AV76,"N/A")
 
Last edited:

Karlisa

New Member
Joined
Aug 7, 2019
Messages
7
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
Re: Need Help with IFs, ANDs, and BUTs

This should work

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,412
Office Version
365
Platform
Windows
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

New Member
Joined
Aug 7, 2019
Messages
7
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,342
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.
 
Last edited:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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!
 
Last edited:

Karlisa

New Member
Joined
Aug 7, 2019
Messages
7
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"))
 

Watch MrExcel Video

Forum statistics

Threads
1,099,086
Messages
5,466,549
Members
406,488
Latest member
syamvg

This Week's Hot Topics

Top