Bhubon Costa
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.
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.
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 AutoFill Handle Tool.
In the below screenshot, you will see the complete result.
Read more: How to Use Greater Than or Equal to Operator 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.
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.
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)
Step 2:
- Then, Click Enter.
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
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.
Step 1:
- Firstly, in cell D5, enter the formula below to apply the IF function,
=IF(C5>80,"Passed","Failed")
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.
Step 3:
- To make changes in all the cells, just repeat the previous steps.
Read More: How to Use Less Than Or Equal to Operator in Excel (8 Examples)
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.
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 value will show ‘3’ as a result. It’s because 3 persons from the above list have marks more than 80.
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.
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. 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.
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. 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.
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. 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.
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 SubWhere,
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.
Read More: Reference Operator in Excel [Basics + Special Uses]
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.
