Skip to content
Home » Use Conditional Formulas by Using Excel’s IF, AND, and OR Formulas

Use Conditional Formulas by Using Excel’s IF, AND, and OR Formulas

Excel is a powerful spreadsheet software that can perform a wide range of calculations and analyses, including conditional formulas. Conditional formulas are used to evaluate logical tests and return results based on the outcome of the test. The IF, AND, and OR formulas are commonly used conditional formulas in Excel.

Excel Conditional Formulas Example

IF Formula

The IF formula is used to evaluate a logical test and return one value if the test is true and another value if the test is false. The syntax of the IF formula is as follows:

=IF(logical_test, value_if_true, value_if_false)
  • logical_test is the condition that is tested.
  • value_if_true is the value that is returned if the condition is true.
  • value_if_false is the value that is returned if the condition is false.

For example, suppose you have a list of dates in column A and you want to check if it rained on a particular date. You can use the IF formula as follows:

=IF(A1="rained", "Yes", "No")

This formula would return “Yes” if cell A1 contains the word “rained” and “No” if it does not.

AND Formula

The AND formula is used to evaluate multiple conditions and return TRUE if all conditions are met and FALSE if any condition is not met. The syntax of the AND formula is as follows:

=AND(condition1, condition2, condition3, ...)
  • condition1, condition2, condition3, and so on are the conditions that are tested.

For example, suppose you have a list of dates in column A and the corresponding temperatures in column B. You want to find out if it rained on a particular date and the temperature was below 50 degrees. You can use the AND formula as follows:

=AND(A1="rained", B1<50)

This formula would return TRUE if cell A1 contains the word “rained” and cell B1 contains a value less than 50.

OR Formula

The OR formula is used to evaluate multiple conditions and return TRUE if any of the conditions are met and FALSE if all conditions are not met. The syntax of the OR formula is as follows:

=OR(condition1, condition2, condition3, ...)
  • condition1, condition2, condition3, and so on are the conditions that are tested.

For example, suppose you have a list of dates in column A and the corresponding temperatures in column B. You want to find out if it rained on a particular date or the temperature was below 50 degrees. You can use the OR formula as follows:

=OR(A1="rained", B1<50)

This formula would return TRUE if cell A1 contains the word “rained” or cell B1 contains a value less than 50.

Nested IF Formula

The IF formula can be nested inside another IF formula to perform more complex logical tests. The syntax of the nested IF formula is as follows:

=IF(logical_test1, value_if_true1, IF(logical_test2, value_if_true2, value_if_false2))
  • logical_test1 is the first condition that is tested.
  • value_if_true1 is the value that is returned if the first condition is true.
  • logical_test2 is the second condition that is tested if the first condition is false.
  • value_if_true2 is the value that is returned if the second condition is true.
  • value_if_false2 is the value that is returned if both the first and second conditions are false.

For example, suppose you have a list of dates in column A and the corresponding temperatures in column B. You want to categorize the days based on the temperature as “cold” if the temperature is less than 50 degrees, “mild” if the temperature is between 50 and 70 degrees, and “warm” if the temperature is above 70 degrees. You can use the nested IF formula as follows:

=IF(B1<50, "cold", IF(B1<70, "mild", "warm"))

This formula would return “cold” if cell B1 contains a value less than 50, “mild” if it is between 50 and 70 degrees, and “warm” if it is above 70 degrees.

Example Table

Here is an example table with data that you can use to apply the formulas:

DateTemperatureRained?
2022-01-0145Yes
2022-01-0249No
2022-01-0363No
2022-01-0446No
2022-01-0560Yes
2022-01-0641Yes
2022-01-0755No
2022-01-0880No
2022-01-0949Yes
2022-01-1070No

You can use this data to apply the IF, AND, and OR formulas that we discussed earlier. For example, to check if it rained on a particular date, you can use the following IF formula:

=IF(C2="Yes", "It rained on this day", "It did not rain on this day")

This formula would return “It rained on this day” if cell C2 contains “Yes” and “It did not rain on this day” if it does not.

To check if it rained on a particular date and the temperature was below 50 degrees, you can use the following AND formula:

=AND(C2="Yes", B2<50)

This formula would return TRUE if cell C2 contains “Yes” and cell B2 contains a value less than 50.

To check if it rained on a particular date or the temperature was below 50 degrees, you can use the following OR formula:

=OR(C2="Yes", B2<50)

This formula would return TRUE if cell C2 contains “Yes” or cell B2 contains a value less than 50.

Leave a Reply

Your email address will not be published. Required fields are marked *

nineteen + 1 =