#N/A Getting Rid Of (Easy One)

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77
Hello All,

Cant seem to work this one out and wondered if you guys can help.

I have the following formula in Cell C1

=IF(AND(A1="TRUE",B1="TRUE"),"Yes","No")

Some of my results show as #N/A, and i would like to add to the formula so my results will not show as the #N/A when the references cells have no data in.

I have tryed ISNA, NOT but cant seem to get it lol

Please help.

Cheers All.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mbb4331

Board Regular
Joined
Dec 22, 2005
Messages
152
Something like this:

=IF(OR(ISNA(A1),ISNA(B1)),"",IF(AND(A1="TRUE",B1="TRUE"),"Yes","No"))
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

are A1 and B1 formulas? If so, what are they?

You could try

=IF(ISERROR(OR(A1,B1)),"",IF(AND(A1=TRUE,B1=TRUE),"Yes","No"))
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
try: =IF(ISNA(OR(A1="TRUE",B1="TRUE")),"No data",IF(AND(A1="TRUE",B1="TRUE"),"Yes","No")) not tested but I think the brackets are right
 

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77

ADVERTISEMENT

A1:A20 has simply "TRUE" or "FALSE" in
B1:B20 has simply "TRUE" or "FALSE" in

So in C1:C20 i did the formula above to check see of both Matched "TRUE", If so Display a "Yes" or If not Display a "No".

Some however return #N/A values as come cells in A1:A20 and B1:B20 have no data in. I would like to get rid of them :)
 

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77
A1:A20 has simply "TRUE" or "FALSE" in
B1:B20 has simply "TRUE" or "FALSE" in

So in C1:C20 i did the formula above to check see of both Matched "TRUE", If so Display a "Yes" or If not Display a "No".

Some however return #N/A values as come cells in A1:A20 and B1:B20 have no data in. I would like to get rid of them :)
 

mbb4331

Board Regular
Joined
Dec 22, 2005
Messages
152

ADVERTISEMENT

So columns A and B can contain "True", "False" or blank.

Is this correct?

If A is blank and B is True, what should C be?
 

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77
So columns A and B can contain "True", "False" or blank.

Is this correct?

If A is blank and B is True, what should C be?

Yes you are correct, A and B contain "True", "False", or are simply Blank statements.

Column C i would like to return "Yes" if A1 and B1 Match "True", return a "No" if they do not Match "True", and if there is no data in A1 or B1 for example then rather than the formula returning a #N/A it would simply just return a BLANK.

I think i can work it out from your posts :) ill give it a whirl. Thanks
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
How are columns A & B formatted, as text or general. Be careful when you use the words TRUE or FALSE as these have special meanings. What happens when you just type true, just like that, in lower case, into column A or B does it come out as TRUE?
 

SamBo1234

Board Regular
Joined
Aug 21, 2006
Messages
77
Thanks Guys,

Just tryed

=IF(OR(ISNA(A1),ISNA(B1)),"",IF(AND(A1="TRUE",B1="TRUE"),"Yes","No"))

Works like i want TY allz.
 

Forum statistics

Threads
1,136,272
Messages
5,674,755
Members
419,525
Latest member
helensesc

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
Top