IF Statement not working

[christinamfisher]

Hi! I am trying to convert 'TRUE/FALSE' values into binaries (0 and 1). I am using the IF statement =IF(B2="FALSE",0,1). The list is returning either all zeros or all ones. Ideas on how to fix this? I have tried converting to text and to general. I believe the syntax is correct as I have used it previously like this : =IF(E2="Under",1,0) in another workbook. I have also tried to copy and paste into another workbook. Any help would be GREATLY appreciated.

 

[Tetra201]

Try using just

=--B2

 

[christinamfisher]

@Tetra201 that did not work

 

[christinamfisher]

@Tetra201 thank you for the suggestion though!

 

[jtakw]

Hi,

A few things I'd like to point out, the below applies to Both TRUE/FALSE:

1. In your IF formula, you're checking B1 for the Text Word "FALSE", Not the Excel logical FALSE, they are Not the same.
2. If Tetra's suggestion did Not work for you, then what you have in B1 is Not the Excel logical FALSE.
3. What Exactly do you have in B1? How did it get there? If by formula, what's the formula?

See below samples:

Book1
	A	B	C	D	E
1	FALSE		FALSE	TRUE	0
2	FALSE		TRUE	FALSE	#VALUE!
Sheet89

Cell Formulas
Range	Formula
C1	=A1="FALSE"
C2	=A2="FALSE"
D1	=A1=FALSE
D2	=A2=FALSE
E1	=--A1
E2	=--A2
A2	="FALSE"

A1 is Excel logical FALSE
A2 is the Text Word "FALSE"

 

[christinamfisher]

@jtakw I am intending to check for the text word "false". I downloaded my data as a set of predictions from an AutoML tool (DataRobot). Thanks!

 

[jtakw]

If your data is downloaded, often times they contain "hidden" characters and/or leading and/or trailing spaces.

What does =LEN(B1) give you?

 

[christinamfisher]

@jtakw =len(b2) returns 5, one for each letter :/

 

[jtakw]

Worth a shot, try this:

=IF(CLEAN(B2)="FALSE",0,1)

or, even:

=IF(TRIM(CLEAN(B2))="FALSE",0,1)

 

[christinamfisher]

@jtakw that worked! Thanks I wonder why it worked if length was returning 5... hmmmm.