The IF function in Excel is a logical function where you may need to use various comparison operators: equal, not equal, greater than, less than, and so on. Today we are going to show you how to use greater than or equal to in IF function. For this session, we are using Excel 2019, feel free to use yours.
First things first, let’s get to know about today’s practice workbook.
Here we have several students with their respective scores on a couple of tests. We will use this dataset to write greater than or equal to in the IF function.
Note that, it is a basic table with a dummy dataset. In real-life scenarios, you may encounter a much complex and larger dataset.
You are welcome to download the practice workbook from the link below.
Greater Than or Equal To in IF Function
Let’s begin the journey to see how to use greater than or equal to in IF. Unless you have not known about the IF function, please visit this IF article.
1. Greater Than
For a quick note let’s get to know the use of the Greater Than operator first. For example, we will check whether Test 1 score is higher than Test 2 score for each student.
Here we will use the formula written below
=IF(D4>E4, "Score higher in Test 1","Test 1 Score not highest")
D4 is Test 1 and E4 is Test 2 score respectively for the first student. And we have set “Score higher in Test 1” at the if_true_value field and “Test 1 Score not highest” at the if_false_value field.
Here for the first student Test 1 score is greater than Test 2 score. And that’s why we have found the if_true_value as our result.
For the second student also we should find the if_true_value but you can see the third student’s scores are equal (no one is lower, no one is higher). What should be the output for that?
Our logical test for this student is not TRUE since Test 1 is not greater so it will return the if_false_value.
You can see in the image for the third student we have found “Test 1 Score not highest” which is the if_false_value in our formula.
Similarly, for the fourth student, we will find the if_false_value.
Now write the formula for the rest of the values or simply exercise the Excel AutoFill feature.
2. Equal To
In this section, we will see how to use the Equal To operator within the IF function. Our base of example is going to be the same as the previous section, only the formula will be different.
To compare whether Test 1 and Test 2 scores are the same (equal) or not, we will use the formula written below
=IF(D4=E4, "Score same in both tests","Scores are not same")
D4 is Test 1 and E4 is Test 2 score respectively for the first student. And we have set “Score same in both tests” at the if_true_value field and “Scores are not same” at the if_false_value field.
Since the scores of the first student are not equal, we have found the if_false_value. For the second student also we will find the if_false_value (Scores are not the same)
You can see the scores of the third student are equal (it is a small dataset so you can understand at a glance), and our formula will provide the if_true_value (Score same in both tests)
Now do the same for the rest of the values or exercise the AutoFill feature.
3. Greater Than or Equal To
Now we are at the business end of our tutorial, we are going to see how to Greater Than or Equal To operator.
I. Apply on Numbers
In this section, we will see how we can apply the operator to numeric values. Let’s begin.
At first, we will compare whether the score in Test 1 is greater than or equal to the score in Test 2 or not.
And our formula will be
=IF(D4>=E4,"Greater or Equal","Lesser")
Here we have set “Greater or Equal” at the if_true_value field and “Lesser” at the if_false_value field.
Test 1 score for the first student is greater than his Test 2 score, so it returns TRUE and our result here is “Greater or Equal” (if_true_value).
If the listed scores are equal, then also the formula will return the if_true_value.
Here the third student scored 6 on both the tests and our formula returned Greater or Equal.
When the score in Test 1 is lower than the score in Test 2, we will find the if_false_value.
Student Jija scored 9 and 10 in Test 1 and Test 2 respectively, and our formula provided if_false_value (Lesser).
Write the formula for the rest of the values.
We can compare whether the score in Test 2 is greater than or equal to the score in Test 1 or not.
Our formula will be similar, all we need to do is interchange the Cell Reference of the two scores.
=IF(E4>=D4,"Greater or Equal","Lesser")
Since the first student scored lesser in Test 2 than Test 1 the logic will return FALSE and the formula will return the if_false_value (Lesser).
When the values are equal or Test 2 is greater we will find Greater or Equal as result.
For the rest of the values write the formula or use the AutoFill feature.
II. Apply on Texts
We can compare texts to see whether they are greater than or equal or not. For example purpose here we have a table of a couple of items from several shops.
Let’s check whether Item 1 is greater than or equal to Item 2. And the formula will be
=IF(C4>=D4,"Greater or Equal","Lesser")
For the first shop, we found the formula result, Lesser which is if_false_value.
You may wonder how this works, let us help you a bit. Every character has ASCII value, machine (computer) and applications recognize the character with its ASCII value. Here the combined ASCII value of Apple is lower than Banana so we have found the if_false_value.
When it is the same then we will get TRUE in return and the result will be if_true_value.
Similarly, when Item 1 is greater then we will also find the if_true_value (Greater or Equal).
Here ASCII of Banana is higher than the ASCII of Apple, so we have found the if_true_value.
Write the formula for the rest of the values.
That’s all for today. We have shown you how to write greater than or equal to in the IF function in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other approach to write greater than or equal operators.