What Are Comparison Operators in Google Sheets?
When you must compare values in a spreadsheet, Google Sheets has some comparison operators that help you do so. Comparison operators in Google Sheets are used for comparing two values. You can use both functions and symbols for the same.
Use them to see if a value is less than, greater than, or equal to another. For example, we use the greater than and less than operators below to see which number is greater or lesser, respectively. Let’s look at how to use comparison operators in Google Sheets for numbers, cell references, and formulas. In this article, we use all the different comparison operators to check different data types, including numbers, dates, and text values, and apply conditioning formatting as well.
Key Takeaways
- Comparison operators in Google Sheets are used to compare values and return Boolean results (TRUE or FALSE).
- These operators perform conditional logical decisions based on data in the cells.
- There are a total of six comparison operators in Google Sheets, which are “=”, “<“, “<=”, “>”, “>=”, and “<>”.
- Comparison operators can be used in array formulas to evaluate multiple cells simultaneously. For example, =ARRAYFORMULA(A1:A5 > 10).
- Besides comparison operators, you can use the functions EQ, LT, LTE, GT, GTE, and NE.
List of Comparison Operators in Google Sheets
There are a total of six comparison operators in Google Sheets, along with corresponding comparison functions.
The comparison operators are “=”, “<“, “<=”, “>”, “>=”, and “<>”.
#1 – Equal Sign (=)
It checks if two values are equal. The syntax is as follows: =value1 = value2
Example:
To check if the value in cell A2 is equal to 10, write as: =A1 = 10
As the result is FALSE, it is printed in B2.
#2 – Not Equal Sign (<>)
It is used to check if two values are not equal.
The syntax is =value1 <> value2
To check if the value in cell C2 is not equal to 50:
=C2 <> 50
#3 – Greater Than (>) Sign
This operator checks if one value is greater than another. Its syntax is =value1 > value2
Let us check if the value in cell A7 is greater than 100 using the following formula.
Example:
=A7 > 100
If A1 is greater than 100, it returns TRUE; otherwise, it returns FALSE.
#4 – Greater Than or Equal To (>=) Sign
This sign checks if one value is greater than or equal to another. We write the syntax as follows: =value1 >= value2
In this example, let us check if the value in cell A10 is greater than or equal to 150.
Example:
=A10 >= 150
If A10 is 150 or more, this formula returns TRUE; otherwise, it returns FALSE.
#5 – Less than Sign (<)
It checks if one value is less than another. It is written as follows: =value1 < value2.
Example: Let us check if the value 20 is less than 30.
=20 < 30
As 20 is less than 30, this formula returns TRUE.
#6 – Less Than or Equal To Sign (<=)
This sign checks if one value is less than or equal to another. We write the syntax as follows: =value1 <= value2
In this example, let us check if the value 200 is less than or equal to 150.
=200 <= 150
As this equation is false, it returns FALSE.
Examples
Let’s examine some comparison operators in Google Sheets examples to learn how to apply them in different scenarios.
Example #1 – IF with Equal Sign
An IF statement displays a result based on a condition. The equal sign compares values.
In Google Sheets, the IF function performs conditional logic and the equal sign (=) is often used within this function to compare values. Let us look at a simple example. We have to compare two values and print an output based on this result.
Look at the table below.
The syntax for the IF function in Google Sheets is: IF(logical_expression, value_if_true, value_if_false)
Step 1: Now, we are comparing the values in Column A with the corresponding ones in Column B. You would use the following formula: =IF(A2 = B2, “Equal”, “Not Equal”)
- A2 = B2 is the condition being tested.
- If A2 equals B2, the formula returns “Equal.”
- If A2 does not equB2, the formula returns “Not Equal”.
Step 2: Press Enter. You will get the result “Not Equal.” To clarify, drag the formula all the way to cell C6.
Example #2 – AND with Greater Than Sign
Let us compare the marks of some students and based on the results, we can grade them.
Step 1: Let us include the following formula in cell D2.
=IF(AND(B2>80 ,C2>80), “Outstanding”, “Good”)
Here, we compare the marks for maths and science. If both marks are greater than 80, the student is judged as “Outstanding.” Else, he is judged as “Good.”
Step 2: Press Enter. Drag the formula to cell D8 and find the grades for the students based on their marks.
Example #3 – OR with Less Than Sign
Let’s include the following values in a spreadsheet: the number of AC units sold and the revenue. If the number of AC units sold is less than 100 or the revenue is less than $2000, you should apply conditional formatting and shade the cell. Here, we use Google Sheets less than or equal to conditional formatting.
Step 1: Look at the details in the below sheet. Include the following formula in cell C2.
The revenue is less than $2000.
You can use the following formula in column C:
=IF(OR(A2 < 100, B2 < 2000), “Low Sales”, “Normal”)
Press Enter.
This formula will return “Alert” if either the number of units sold is less than 50 or the revenue is less than $1000. If neither condition is true, it will return “Normal.”
Step 2: Press Enter. Drag the formula to cell C7 and get the results.
Step 3: To apply conditional formatting, go to Format -> Conditional Formatting.
In the pane on the right, first select the range C2 to C7. After that we apply the formatting conditions. It is done in the “Format Cells if” part. Here, we chose “text contains” as “Low Sales” and shaded the cells in red.
Press Done. It will be applied to the range.
Important Things To Note
- Ensure that the data being compared is of the same type else it can lead to unexpected errors.
- When you compare two texts, remember it is case-insensitive.
- An empty cell is generally treated as zero in numerical comparisons and as empty string when you compare text.
- When comparing very large numbers or very small decimal numbers, ensure that the precision is sufficient.
- Combine comparison operators with logical functions like AND/OR to check for complex comparisons as seen in the examples above.
Frequently Asked Questions (FAQs)
You can use the IF function along with the comparison operators in Google Sheets. Inside the IF formula, you write the expression A2 > B2. It checks whether the value in cell A2 is greater than that in cell B2. Based on the outcome, the particular output is displayed.
If a cell contains an error such as #DIV/0!, #N/A, we get errors when we compare these cells. Use IFERROR to handle errors if they occur.
As is known, dates are stored as serial numbers in Google Sheets. When you compare dates, you are comparing these serial numbers in Google Sheets.
Dates are stored as serial numbers in Google Sheets, so comparisons involving dates are treated as numerical comparisons of these serial numbers. For example, we are comparing the dates in cells A1 and B1. If we use a comparison operator greater than, we get true.
Comparison operators can be used with IF statements to execute different actions based on a condition.
They can be used with Conditional Formatting to format cells based on their values, as seen in our example above.
They are used to filter Data to display only rows that meet certain criteria.
To fix errors based on conditions and comparison.
Recommeded Articles
Guide to What are Comparison Operators in Google Sheets. Here we learn the list of comparison operators and their uses with examples. You can learn more from the following articles. –
Leave a Reply