If a range contains a value greater than
This tutorial shows how to test if a range contains a value greater than a specific value and return a specified value if the test is True or False through the use of an Excel formula or VBA
Example: If a range contains a value greater than
|
This formula uses the Excel COUNTIF function to count the number of cells in a range (C8:C14) that have a value of greater than the value in cell C5. The Excel IF function is then used to test if the Excel COUNTIF function found one or more cells in a range that have a value greater than the value in cell C5. If the test is TRUE the formula will return a "Yes" value, alternatively if the test is FALSE the formula will return a "No" value.
|
Sub If_a_range_contains_a_value_greater_than()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a range contains a value greater than the value in cell (C5) and then return a specified value
If Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), ">" & ws.Range("C5")) > 0 Then
If Application.WorksheetFunction.CountIf(ws.Range("C8:C14"), ">" & ws.Range("C5")) > 0 Then
ws.Range("E8").Value = "Yes"
Else
ws.Range("E8").Value = "No"
End If
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E8") in the VBA code.
Range to Test: Select the range that you want to search through for a value that is greater than a specific value by changing the range reference ("C8:C14") in the VBA code.
Specific Value: Select the specific value that you want to test for by changing the value in cell ("C5").
Worksheet Selection: Select the worksheet which captures the range that you want to test if it contains a value that is grater than a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a range contains a value that is greater than a specific value the VBA code will return a value of "Yes". If a range does not contain a value that is greater than a specific value the VBA code will return a value of "No". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Output Range: Select the output range by changing the cell reference ("E8") in the VBA code.
Range to Test: Select the range that you want to search through for a value that is greater than a specific value by changing the range reference ("C8:C14") in the VBA code.
Specific Value: Select the specific value that you want to test for by changing the value in cell ("C5").
Worksheet Selection: Select the worksheet which captures the range that you want to test if it contains a value that is grater than a specific value by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
True and False Results: In this example if a range contains a value that is greater than a specific value the VBA code will return a value of "Yes". If a range does not contain a value that is greater than a specific value the VBA code will return a value of "No". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
ADDITIONAL NOTES
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
EXPLANATION
This tutorial shows how to test if a range contains a value greater than a specific value and return a specified value if the formula tests true or false, by using an Excel formula and VBA.
This tutorial provides one Excel method that can be applied to test if a range contains a value greater than a specific value and return a specified value by using an Excel IF and COUNTIF functions. In this example, if the Excel COUNTIF function returns a value of greater than 0, meaning the range has cells with a value of greater than 500, the test is TRUE and the formula will return a "Yes" value. Alternatively, if the Excel COUNTIF function returns a value of 0, meaning the range does not have cells with a value of greater than 500, the test is FALSE and the formula will return a "No" value.
This tutorial provides one VBA method that can be applied to test if a range contains a value greater than a specific value and return a specified value.
FORMULA
=IF(COUNTIF(range, ">"value)>0, value_if_true, value_if_false)
=IF(COUNTIF(range, ">"value)>0, value_if_true, value_if_false)
ARGUMENTS
range: The range of cells you want to count from.
value: The value that is used to determine which of the cells should be counted, from a specified range, if the cells value is greater than this value.
If the value is a reference to a cell, as per the example in this tutorial, you need to insert the & sign before the cell reference.
value_if_true: Value to be returned if the range contains a value grater than a specific value.
value_if_false: Value to be returned if the range does not contain a value grater than a specific value.
range: The range of cells you want to count from.
value: The value that is used to determine which of the cells should be counted, from a specified range, if the cells value is greater than this value.
If the value is a reference to a cell, as per the example in this tutorial, you need to insert the & sign before the cell reference.
value_if_true: Value to be returned if the range contains a value grater than a specific value.
value_if_false: Value to be returned if the range does not contain a value grater than a specific value.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If a range contains a specific value by column | How to test if a range contains a specific value by column and return a specified value using Excel and VBA methods | |
| If a range contains a value less than | How to test if a range contains a value less than a specific value and return a specified value using Excel and VBA methods | |
| If a range contains a specific value by row | How to test if a range contains a specific value by row and return a specified value using Excel and VBA methods | |
| If a range contains a value greater than or equal to | How to test if a range contains a value greater than or equal to a specific value and return a specified value using Excel and VBA methods | |
| If a range does not contain a specific value | How to test if a range does not contain a specific value and return a specified value using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| IF Function | The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE | |
| COUNTIF Function | The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria |