You might encounter unexpected TRUE results in logical tests in Google Sheets due to changes in data type—like comparing text with a number. This can happen in formulas like IF, AND, or simple comparisons such as A1 > 1000, and it often leads to subtle, hard-to-detect errors.

This issue arises when Google Sheets silently compares different data types—like text strings and numbers—without throwing an error. That behavior can lead to incorrect results if you’re not explicitly checking value types in your formulas.

In this post, we’ll break down why this happens, when it’s most likely to affect your formulas, and how to fix it using functions like ISNUMBER or N to ensure your logical tests behave as expected.

What Causes This Behavior in Google Sheets?

Google Sheets allows comparisons between values of different types—such as comparing a text string with a number—without displaying an error. Instead, it applies a process called type coercion, where it tries to interpret the values in a compatible way.

Take the formula:

=A1 > 1000

Google Sheets will evaluate this comparison even if A1 contains text. But here’s the catch:

• If A1 contains a value like "mango" or "100" (entered or formatted as text), the comparison happens alphabetically (lexicographically), not numerically.
• In lexicographic comparisons, most text strings are considered greater than numbers because text is treated as coming “after” numeric values.

Examples:

• "mango" > 1000 → TRUE
• "100" (text) > 1000 → TRUE

So, the formula returns TRUE — even though logically, you might expect it to return FALSE or even show an error.

This mismatch can lead to incorrect logic in formulas, especially when the cell looks like a number but is actually stored as text.

Solving Unexpected TRUE in Logical Tests

Let’s say you want a formula that returns TRUE only when both A1 and A2 are greater than or equal to 500, and FALSE otherwise.

Suppose:

• A1 contains 600
• A2 contains a hyphen (-), which is text

A typical formula might look like:

=AND(A1>=500, A2>=500)

This will incorrectly return TRUE, even though A2 is not a number. Here’s how to fix it.

Option 1: Using the ISNUMBER Function

=AND(A1>=500, A2>=500, ISNUMBER(A1), ISNUMBER(A2))

Here’s how this formula works:

• A1>=500 and A2>=500 check whether each cell meets the numeric condition.
• ISNUMBER(A1) and ISNUMBER(A2) ensure that the values being compared are actually numbers — not text.

If either cell contains text — even if it looks like a number — ISNUMBER will return FALSE, which causes the entire formula to return FALSE, since all conditions in AND must be TRUE.

This approach ensures you’re only comparing valid numeric values, preventing logical errors caused by hidden or unintended text content.

Option 2: Using the N Function

=AND(N(A1)>=500, N(A2)>=500)

• The N function returns:
  • The number if the input is numeric
  • 0 if the input is text
• That means text values are treated as zero, so they naturally fail the condition >=500.

This method is simpler and works well in most situations — especially if you’re okay treating text as zero.

Tip: I recommend the N() approach when you want a clean and concise formula, and you’re confident that text values should not be considered valid.

Additional Tip: Don’t Overlook Dates

In Google Sheets, dates are stored as serial numbers starting from December 30, 1899, which is represented by the number 0. That means a date like July 1, 2025 is internally represented by the number 45839.

So if you’re checking whether a value is greater than or equal to 500, dates could unintentionally pass the test.

If you want to exclude dates, add NOT(ISDATE(...)) to your formula.

How to Exclude Dates in Logic

Using N() with date exclusion:

=AND(N(A1)>=500, N(A2)>=500, NOT(ISDATE(A1)), NOT(ISDATE(A2)))

Using ISNUMBER() with date exclusion:

=AND(A1>=500, A2>=500, ISNUMBER(A1), ISNUMBER(A2), NOT(ISDATE(A1)), NOT(ISDATE(A2)))

Summary

If you’re seeing unexpected TRUE results in logical tests in Google Sheets, you’re likely comparing values of mixed types, such as text and numbers. Since Google Sheets doesn’t warn you about this and tries to make the comparison work, formulas like A1 > 1000 may behave incorrectly.

To prevent this:

• Use ISNUMBER() to ensure the value is numeric and avoid unexpected TRUE results in comparisons.
• Use N() to convert text to zero and avoid false TRUE results.
• Use NOT(ISDATE(...)) if there’s a chance the input could be a date.

These small additions to your formulas can save you from subtle errors in filtering, conditional formatting, and logical decision-making in your Sheets.

Resources

• Combined Use of IF, AND, and OR Logical Functions in Google Sheets
• Master the IF Function in Google Sheets: Beyond the Basics
• How to Use the AND Logical Function in Arrays in Google Sheets
• How to Use IFS Logical Function in Google Sheets
• How to Use the ISLOGICAL Function in Google Sheets
• The Role of the TRUE Logical Function in Google Sheets
• ISDATE Function and Its Best Alternative in Google Sheets