How to Apply ‘If Greater Than’ Condition In Excel (9 Ways)

While working with a big data set, we frequently need to compare numbers. In some cases, we need to find the summation, average or conditional applications of the greater numbers in a particular worksheet. In this tutorial, you will learn how to find numbers greater than other numbers in Excel. To do so, we have applied different logical arguments, functions, and VBA codes.

1. Using Logical Operator to Test ‘If Greater Than’ Condition

In Excel, a logical operator is used to compare two numbers. In each given case, the outcome of the comparison may either be TRUE or FALSE. Below is a data set of the marks obtained by several students. We want to find who got more numbers than 80.

Step 1:

To use the logical operator, type the following formula in cell D5

=C5>80

Step 2:

To see the result, press

Therefore, you will see the result will show ‘TRUE’ as the value is greater than 80.

Step 3:

To apply the logical operator (>) in each cell, repeat the steps or use the AutoFill Handle Tool.

In the below screenshot, you will see the complete result.

2. Using the OR Function with If Greater Than Operator

The OR function is a logical function that may be used to evaluate many conditions at once. OR returns one of two values: TRUE or FALSE. For instance, we have a data set of a student’s obtained marks in two consecutive months. Now, we want to know if he got more than 60 in either of the two terms.

Step 1:

In cell E5, type the following formula to use the OR Function.

=OR(C5>60,D5>60)

Press Enter to the result.

As the two values in cells C5 and D5 satisfied the condition (C5>60 and D5>60), It will show the result as ‘TRUE’.

Step 3:

Then, to use the OR Function in the required cells, repeat the steps.

3. Combining AND Function with If Greater Than Operator

The AND function in Excel is a logical function that is used to require multiple conditions simultaneously. The AND function either returns TRUE or FALSE. For example, we’re looking for which area the student acquired more than 60 marks in both terms.

Step 1:

Type the formula below to apply the AND Function,

=AND(C5>60,D5>60)

Step 2:

Then, Click Enter.

In the above screenshot, the result will show ‘TRUE’ as both the cell value C5 and D5 is greater than 60.

Step 3:

To find the results for the next cells, repeat the steps

As a result, you will get all the values as presented in the above screenshot.

4. Using the IF Function to Apply If Greater Than Condition

The IF function performs a logical test and outputs one value if the result is TRUE, and another if the result is FALSE. To improve the logical test, the IF function can be applied with logical functions like AND and OR. We want to return ‘Passed’ for numbers more than 80 and ‘Failed’ for numbers less than 80.

Step 1:

Firstly, in cell D5, enter the formula below to apply the IF function,

=IF(C5>80,"Passed","Failed")

Step 2:

Then, hit the Enter button to see the change.

The following cell D5 will show the result as ‘passed’ as it meets the condition for the value greater than 80.

Step 3:

To make changes in all the cells, just repeat the previous steps.

5. Using the COUNTIF Function with ‘If Greater Than’ Operator

In Excel, the COUNTIF function allows you to count the number of cells in a range that satisfies a single condition. COUNTIF may be applied to count cells with dates, numbers, or text in them. Hence, we want to calculate the number of the total people who got more than 80.

Step 1:

To count the number of persons, simply enter the formula in cell C14.

=COUNTIF(C5:C11,">"&80)

Step 2:

After typing the formula, press Enter to make it count.

Consequently, you will see that the value will show ‘3’ as a result. It’s because 3 persons from the above list have marked more than 80.

6. Applying If Greater Than Operator with the SUMIF Function

The SUMIF function in Excel displays the total of cells that satisfy a single criterion. The SUMIF function may be performed to count cells with dates, numbers, or text in them. In the following example, we want to sum the total if the values of the cells are greater than 60.

Step 1:

Firstly, to sum up, the marks more than 60, in cell F6, type the formula below.

=SUMIF($C$5:$C$11,">"&60,$C$5:$C$11)

Step 2:

Then, press Enter to find the total.

As in the below screenshot, the result shown is 408. The value 408 comes from summing the values greater than 60 from the list (81,79,85,74,89).

7. Using the AVERAGEIF Function with the ‘If Greater Than’ Operator

The AVERAGEIF function in Excel returns the average of integers in a range that satisfies specified conditions. According to the below data set, we want to evaluate the average of those numbers which averaged more than 80.

Step 1:

Firstly, In cell E13, type the following formula to find the conditional average.

=AVERAGEIF(C5:C11,">80",D5:D11)

Then, hit Enter to see the average.

The result in the below screenshot is 84.333, which has come from the average value of marks greater than 80 (81.5,89,83.5).

8. Using Conditional Formatting to Apply ‘If Greater Than’ Condition

In Excel, Conditional Formatting allows you to highlight cells with a certain color based on their conditions. Here we will highlight the cell’s value which is greater than 80.

Step 1:

Make your dataset as with a table header.

Step 2:

Select the table and click the formatting sign right of the table.
Select the Greater than option from the Formatting

Step 3:

Input the range in the left side box.
Select the formatting color in the right-side box.
Finally, press Enter.

Therefore, you will get values greater than 80 in red-marked cells.

9. Running a VBA Code

Macro code in Excel is a programming code developed in the VBA (Visual Basic for Applications) programming language. The purpose of applying a macro code is to automate an operation that you would otherwise have to do manually in Excel.

For example, we want to apply the VBA code to differentiate the value greater than 80. Value for greater than 80, it will return ‘passed’ and ‘failed’ for less than 80.

Step 1:

Press Alt + F11 to open the Macro-Enabled Worksheet.
Click Insert from the tab.
Select
Then, paste the following VBA codes.

Sub nnn()
Dim score As Integer , result As String
score = Range("C5").Value
If score >= 80 Then
    result = "passed"
Else
    result = "failed"
End If
Range("D5").Value = result
End Sub

Where,

score = Range(“reference cell”).Value

Range(“return cell”).Value = result

Consequently, you will get the result in cell D5 as programmed.

Step 2:

Repeat the previous steps for the range C5:C11 and return the result in range D5:D11.

Therefore, you will get results as shown in the below image.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

If Greater.xlsm

Conclusion

To conclude, I hope this article has provided detailed guidance to apply ‘if greater than’ condition in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

Stay with us & keep learning.

How to Apply ‘If Greater Than’ Condition In Excel (9 Ways)

1. Using Logical Operator to Test ‘If Greater Than’ Condition

2. Using the OR Function with If Greater Than Operator

3. Combining AND Function with If Greater Than Operator

4. Using the IF Function to Apply If Greater Than Condition

5. Using the COUNTIF Function with ‘If Greater Than’ Operator

6. Applying If Greater Than Operator with the SUMIF Function

7. Using the AVERAGEIF Function with the ‘If Greater Than’ Operator

8. Using Conditional Formatting to Apply ‘If Greater Than’ Condition

9. Running a VBA Code

Conclusion

Further Readings

Leave a reply Cancel reply