#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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak 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
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
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,171,588
Messages
5,876,320
Members
433,193
Latest member
BruxoTrader

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