Excel evaluates “FALSE” and blank cell as equal

2
votes

I have a spreadsheet that uses VBA to retrieve data from a database and copy it into a worksheet. I need to compare two columns to see if they match. The possible values are "TRUE", "FALSE", or a blank cell.

I have a simple formula to flag the row if the values are different: =IF(I2=J2,"Same","Difference")

This formula works fine if the values match, or if they are TRUE/Blank or TRUE/FALSE, but it does not evaluate as expected when there's a FALSE and a blank cell! Screenshot of formula not evaluating as expected

If I type in the word "FALSE" the formula evaluates properly (like in the image below). The formula only seems to be tripped up when the data is pasted from the database by VBA.

screenshot of formula evaluating correctly when I type in the word "False"

I suspect this has something to do with Excel treating FALSE and blank cells as both equivalent to "0", but I cannot figure out how to easily workaround this "feature." A couple things I tried:

  • I confirmed the cell types are set to "Text" for all cells and I tried using VBA to force the format to be text by using Range("I:J").NumberFormat = "@". Still evaluates to "Same."
  • I tried manually recalculating the formula by using the "Calculate Now" option in the Formulas ribbon. Still evaluates to "Same."
  • I can write an ugly formula in the IF statement to check if the cells match: =IF(OR(AND(I2="FALSE",J2="FALSE"), AND(I2="TRUE",J2="TRUE"), AND(I2="",J2="")),"Same","Difference") Is there a way to simplify this formula or force the I2=J2 formula to calculate correctly in case we ever have to handle data other than true/false/blank?
2
excelvbaexcel-formula
Possible workaround, though it may run into its own set of problems: =IF(AND(A1=B1,LEN(A1)=LEN(B1)),"same","different") - cybernetic.nomad
You might even only need LEN(A1)=LEN(B1) since TRUE and FALSE don't contain the same amount of letters. - riskypenguin
What format are your cells? - Zac
TEXT(H11,"@")=TEXT(I11,"@") - Nathan_Sav

2 Answers

4
votes

You can use EXACT:

=IF(EXACT(A1,B1),"Same","Different")

enter image description here

2
votes

The reason is that it is defined as FALSE = 0 and TRUE = 1 (in formulas) or TRUE = -1 (in VBA).

If you compare A1=B1 you compare the values of these cells. And the value of FALSE is 0 exactly as the value of a blank cell is 0. So their value is equal and therefore they are considered the same.

In addition to only check if the values are the same A1=B1 also check if the data type is the same TYPE(A1)=TYPE(B1) using the TYPE function:

=IF(AND(A1=B1,TYPE(A1)=TYPE(B1)),"same","different")

enter image description here

This prevents implicit data type conversion (for example from a boolean FALSE to a integer 0 and vice versa).

TYPE() can distinguish between the following data types:

  • 1 = Number
  • 2 = Text
  • 4 = Logical value (Boolean)
  • 16 = Error value
  • 64 = Array (Matrix)
  • 128 = Compound data