IF, AND and OR Functions Combined
Test multiple conditions by applying the IF, AND and OR functions into a single formula
Example: Excel IF, AND and OR Functions combined
|
The IF, AND and OR formula tests if value in cell B8 is equal to Team A and if the value in cell C8 is equal to win or draw. If these tests are TRUE the formula will return OK, otherwise it will return CHECK in cell D8. To apply this formula across all values in the table, simply drag it down.
|
METHOD 2. IF, AND and OR Functions combined using links
EXCEL
|
The IF, AND and OR formula tests if value in cell B8 is equal to the value in cell B5 and if the value in cell C8 is equal to the value in cell C5 or D5. If these tests are TRUE the formula will return OK, otherwise it will return CHECK in cell D8. To apply this formula across all values in the table, simply drag it down.
|
Dim ws As Worksheet
If (ws.Range("C8") = "win" Or ws.Range("C8") = "draw") And ws.Range("B8") = "Team A" Then
Else
End If
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named IF, AND and OR.
Output Range: Select the output range by changing the cell reference ("D8") in the VBA code to any cell in the worksheet that doesn't conflict with the formula.
METHOD 2. IF, AND and OR Functions combined using VBA with links
VBA
Dim ws As Worksheet
If (ws.Range("C8") = ws.Range("$C$5") Or ws.Range("C8") = ws.Range("$D$5")) And ws.Range("B8") = ws.Range("$B$5") Then
Else
End If
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named IF, AND and OR.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("D8") in the VBA code to any cell in the worksheet that doesn't conflict with the formula.
METHOD 3. IF, AND and OR Functions combined using VBA with a For Loop
VBA
Dim ws As Worksheet
For x = 8 To 14
On Error Resume Next
If (ws.Cells(x, 3) = ws.Range("$C$5") Or ws.Cells(x, 3) = ws.Range("$D$5")) And ws.Cells(x, 2) = ws.Range("$B$5") Then
Else
Next
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named IF, AND and OR.
ADJUSTABLE PARAMETERS
Output Ranges: Select the output ranges by changing the For x values (8 to 14).
Output Range: Select the output column by changing the column number.
Test multiple conditions by applying the IF, AND and OR functions into a single formula.
=IF(AND(logical1, [logical2], ...,OR(logical1, [logical2], ...)), [value_if_true], [value_if_false])
logical1: (Required) A condition that you want to test.
logical2: (Optional) A condition that you want to test.
[value_if_true]: (Optional) Return a value if the logic tests TRUE.
[value_if_false]: (Optional) Return a value if the logic tests FALSE.
ADDITIONAL NOTES
Note 1: In Excel 2007 and later the AND and OR functions can accept up to 255 logical arguments. In Excel 2003 the AND and OR functions can only accept up to 30 logical arguments.
Note 2: The AND and OR functions ignore empty cells.