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 the numbers greater than other numbers in excel. To do so, we have applied different logical arguments, functions, and VBA codes.


Download Practice Workbook

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


9 Quick Ways to Apply ‘If Greater Than’ in Excel

1. Use 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. Here below is a data set of the marks obtained by several students. We want to find who got more numbers than 80.

Use Logical Operator to Test ‘If Greater Than’ Condition

Step 1:

  • To use the logical operator, type the following formula in cell D5
=C5>80

Use Logical Operator to Test ‘If Greater Than’ Condition

Step 2:

  • To see the result, press

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

Use Logical Operator to Test ‘If Greater Than’ Condition

Step 3:

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

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

Use Logical Operator to Test ‘If Greater Than’ Condition

Read more: How to Use Greater Than or Equal to in Excel Formula


2. Use the OR Function to Apply ‘If Greater Than’

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.

Use the OR Function to Apply ‘If Greater Than’

Step 1:

  • In cell E5, type the following formula to use the OR Function.
=OR(C5>60,D5>60)
  • Press Enter to the result.

Use the OR Function to Apply ‘If Greater Than’

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.

Use the OR Function to Apply ‘If Greater Than’

Read more: How to Perform Greater than and Less than in Excel (5 Methods)


3. Use the AND Function to Perform ‘If Greater Than’

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)

Use the OR Function to Apply ‘If Greater Than’

Step 2:

  • Then, Click Enter.

Use the AND function to Perform ‘If Greater Than’

In the above screenshot, the result will show to ‘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

Use the AND function to Perform ‘If Greater Than’

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


4. Use the IF Function to Apply ‘If Greater Than’

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.

Use the AND function to Perform ‘If Greater Than’

Step 1:

  • Firstly, in cell D5, enter the formula below to apply the IF function,
=IF(C5>80,"Passed","Failed")

Use the IF Function to Apply ‘If Greater Than’

Step 2:

  • Then, hit Enter button to see the change.

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

Use the IF Function to Apply ‘If Greater Than’

Step 3:

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

Use the IF Function to Apply ‘If Greater Than’


5. Use the COUNTIF Function to Test ‘If Greater Than’ Condition

In Excel, COUNTIF is a function that 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.

Use the COUNTIF Function to Test ‘If Greater Than’ Condition

Step 1:

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

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

Use the COUNTIF Function to Test ‘If Greater Than’ Condition

Step 2:

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

Use the COUNTIF Function to Test ‘If Greater Than’ Condition

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


Similar Readings:


6. Use the SUMIF Function to Apply ‘If Greater Than’

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.

 Use the SUMIF Function to Apply ‘If Greater Than’

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)

 Use the SUMIF Function to Apply ‘If Greater Than’ Step 2:

  • Then, press Enter to find the total.

 Use the SUMIF Function to Apply ‘If Greater Than’

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. Use the AVERAGEIF Function to Perform ‘If Greater Than’

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.

Use the AVERAGEIF Function to Perform ‘If Greater Than’

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.

Use the AVERAGEIF Function to Perform ‘If Greater Than’

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. Use Conditional Formatting to Apply ‘If Greater Than’

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.

Use Conditional Formatting

Step 2:

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

Use Conditional Formatting

Step 3:

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

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

Use Conditional Formatting


9. Run 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.

Run A VBA Code

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

Run A VBA Code

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

Run A VBA Code

Step 2:

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

Run A VBA Code

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


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.

We, The Exceldemy Team, are always responsive to your queries.

Stay with us & keep learning.


Further Readings

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo