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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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:
Upvote 0
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".
 
Upvote 0
Re: Need Help with IFs, ANDs, and BUTs

This should work

=IF(AV14="Yes",IF(ISNUMBER(AV76),AV76,"Hold"),"N/A")
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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"))
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top