If a cell is less than a specific value
This tutorial shows how to test if a cell is less 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 cell is less than a specific value
|
=IF(C8<$C$5,"Yes","No")
|
This formula uses the Excel IF function, combined with the less than sign (<), to test if the value in cell C8 is less 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.
In this example the value in cell C5 represents the specific value that other cells are being tested against, therefore we have treated cell C5 as an absolute row and column by inserting the $ sign in front of the row and column reference. This will ensure that when you drag the formula across other cells, this cell reference will remain unchanged. |
Sub If_a_cell_is_less_than_a_specific_value()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is less than a specific value
If ws.Range("C8") < ws.Range("C5") Then
If ws.Range("C8") < ws.Range("C5") Then
ws.Range("D8") = "Yes"
Else
ws.Range("D8") = "No"
End If
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D8") in the VBA code.
Cell to Test: Select the cell that you want to test if it's less than a specific value by changing the cell reference ("C8") in the VBA code.
Specific Value: Select the specific value that you want to test other cells against for being less than by changing the value in cell ("C5").
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are less than a specific value and the 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 cell is less than a specific value the VBA code will return a value of "Yes". If a cell is greater than or equal to 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 ("D8") in the VBA code.
Cell to Test: Select the cell that you want to test if it's less than a specific value by changing the cell reference ("C8") in the VBA code.
Specific Value: Select the specific value that you want to test other cells against for being less than by changing the value in cell ("C5").
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are less than a specific value and the 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 cell is less than a specific value the VBA code will return a value of "Yes". If a cell is greater than or equal to 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.
METHOD 2. If a cell is less than a specific value using VBA with a For Loop
VBA
Sub If_a_cell_is_less_than_a_specific_value_using_For_Loop()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is less than a specific value across a range of cells with a For Loop
For x = 8 To 14
For x = 8 To 14
If ws.Cells(x, 3) < ws.Range("C5") Then
ws.Cells(x, 4) = "Yes"
Else
ws.Cells(x, 4) = "No"
End If
Next x
End Sub
ADJUSTABLE PARAMETERS
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (8 to 14). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 3, in ws.Cells(x, 3).
Output Column: Select the output column by changing number 4, in ws.Cells(x, 4).
Specific Value: Select the specific value that you want to test other cells against for being less than by changing the value in cell ("C5").
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are less than a specific value and the 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 cell is less than a specific value the VBA code will return a value of "Yes". If a cell is greater than or equal to 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 and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (8 to 14). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 3, in ws.Cells(x, 3).
Output Column: Select the output column by changing number 4, in ws.Cells(x, 4).
Specific Value: Select the specific value that you want to test other cells against for being less than by changing the value in cell ("C5").
Worksheet Selection: Select the worksheet which captures the cells that you want to test if they are less than a specific value and the 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 cell is less than a specific value the VBA code will return a value of "Yes". If a cell is greater than or equal to 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 cell is less 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 cell is less than a specific value and return a specified value by using an Excel IF function. In this example, if a cell is less than the value specified in cell C5 the formula will return a value of "Yes". If the cell is equal to or greater than the value specified in cell C5 the formula will return a value of "No".
This tutorial provides two VBA methods that can be applied to test if a cell is less than a specific value and return a specified value. The first method is applied against a single cell and the second method uses the For Loop to loop through all of the relevant cells, as per the example in the image, to test each of the cells in a range and return specific values.
FORMULA
=IF(value_1 < value_2, value_if_true, value_if_false)
=IF(value_1 < value_2, value_if_true, value_if_false)
ARGUMENTS
value_1: A value tested to check if it's less than value_2.
value_2: A specific value that you want to test the cells against for being less than.
value_if_true: Value to be returned if the cell that is being tested is less than a specific value.
value_if_false: Value to be returned if the cell that is being tested is greater than or equal to a specific value.
value_1: A value tested to check if it's less than value_2.
value_2: A specific value that you want to test the cells against for being less than.
value_if_true: Value to be returned if the cell that is being tested is less than a specific value.
value_if_false: Value to be returned if the cell that is being tested is greater than or equal to a specific value.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If a cell is greater than a specific value | How to test if a cell is greater than a specific value and return a value using Excel and VBA methods | |
| If a cell is greater than or equal to a specific value | How to test if a cell is greater than or equal to a specific value and return a specified value using Excel and VBA methods | |
| If a cell is less than or equal to a specific value | How to test if a cell is less than or equal to a specific value and return a specified value using Excel and VBA methods | |
| If a cell is blank | How to test if a cell is blank and return a value if the test is True or False through the use of Excel formulas or VBA | |
| If a cell is not blank | How to test if a cell is not blank and return a value if the test is True or False through the use of Excel formulas or VBA |
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 |