Create conditional formulation

Testing whether or not situations are true or false and making logical comparisons between expressions are widespread to many duties. You need to use the AND, OR, NOT, and IF capabilities to create conditional formulation.

For instance, the IF perform makes use of the next arguments.

formula with the if function

Method that makes use of the IF perform

Button image logical_test: The situation that you simply need to examine.

Callout 2 value_if_true: The worth to return if the situation is True.

Button image value_if_false: The worth to return if the situation is False.

For extra details about the way to create formulation, see Create or delete a formulation.

What do you need to do?

Create a conditional formulation that leads to a logical worth (TRUE or FALSE)

Create a conditional formulation that leads to one other calculation or in values aside from TRUE or FALSE

Create a conditional formulation that leads to a logical worth (TRUE or FALSE)

To do that activity, use the AND, OR, and NOT capabilities and operators as proven within the following instance.

Instance

The instance could also be simpler to know should you copy it to a clean worksheet.

How do I copy an instance?

Choose the instance on this article.
Vital: Don’t choose the row or column headers.

Choosing an instance from Assist
Press CTRL+C.
In Excel, create a clean workbook or worksheet.
Within the worksheet, choose cell A1, and press CTRL+V.

Vital: For the instance to work correctly, it’s essential to paste it into cell A1 of the worksheet.

To change between viewing the outcomes and viewing the formulation that return the outcomes, press CTRL+` (grave accent), or on the Formulation tab, within the Method Auditing group, click on the Present Formulation button.

After you copy the instance to a clean worksheet, you’ll be able to adapt it to fit your wants.


1
2
3
4
5
6
7
8
9
10
11
12

A	B
Information
15
9
8
Sprockets
Widgets
Method	Description (Outcome)
=AND(A2>A3, A2<A4)	Determines if the worth in cell A2 is bigger than the worth in A3 and in addition if the worth in A2 is lower than the worth in A4. (FALSE)
=OR(A2>A3, A2<A4)	Determines if the worth in cell A2 is bigger than the worth in A3 or if the worth in A2 is lower than the worth in A4. (TRUE)
=NOT(A2+A3=24)	Determines if the sum of the values in cells A2 and A3 shouldn’t be equal to 24. (FALSE)
=NOT(A5=”Sprockets”)	Determines if the worth in cell A5 shouldn’t be equal to “Sprockets.” (FALSE)
=OR(A5<>”Sprockets”,A6 = “Widgets”)	Determines if the worth in cell A5 shouldn’t be equal to “Sprockets” or if the worth in A6 is the same as “Widgets.” (TRUE)

For extra details about the way to use these capabilities, see AND perform, OR perform, and NOT perform.

Prime of Web page

Create a conditional formulation that leads to one other calculation or in values aside from TRUE or FALSE

To do that activity, use the IF, AND, and OR capabilities and operators as proven within the following instance.

Instance

The instance could also be simpler to know should you copy it to a clean worksheet.

How do I copy an instance?

Choose the instance on this article.

Vital: Don’t choose the row or column headers.

selecting an example from help in Excel 2013 for Windows

Choosing an instance from Assist

Press CTRL+C.
In Excel, create a clean workbook or worksheet.
Within the worksheet, choose cell A1, and press CTRL+V.

Vital: For the instance to work correctly, it’s essential to paste it into cell A1 of the worksheet.

To change between viewing the outcomes and viewing the formulation that return the outcomes, press CTRL+` (grave accent), or on the Formulation tab, within the Method Auditing group, click on the Present Formulation button.

After you copy the instance to a clean worksheet, you’ll be able to adapt it to fit your wants.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

A	B
Information
15
9
8
Sprockets
Widgets
Method	Description (Outcome)
=IF(A2=15, “OK”, “Not OK”)	If the worth in cell A2 equals 15, return “OK.” In any other case, return “Not OK.” (OK)
=IF(A2<>15, “OK”, “Not OK”)	If the worth in cell A2 shouldn’t be equal to 15, return “OK.” In any other case, return “Not OK.” (Not OK)
=IF(NOT(A2<=15), “OK”, “Not OK”)	If the worth in cell A2 shouldn’t be lower than or equal to 15, return “OK.” In any other case, return “Not OK.” (Not OK)
=IF(A5<>”SPROCKETS”, “OK”, “Not OK”)	If the worth in cell A5 shouldn’t be equal to “SPROCKETS”, return “OK.” In any other case, return “Not OK.” (Not OK)
=IF(AND(A2>A3, A2<A4), “OK”, “Not OK”)	If the worth in cell A2 is bigger than the worth in A3 and the worth in A2 can also be lower than the worth in A4, return “OK.” In any other case, return “Not OK.” (Not OK)
=IF(AND(A2<>A3, A2<>A4), “OK”, “Not OK”)	If the worth in cell A2 shouldn’t be equal to A3 and the worth in A2 can also be not equal to the worth in A4, return “OK.” In any other case, return “Not OK.” (OK)
=IF(OR(A2>A3, A2<A4), “OK”, “Not OK”)	If the worth in cell A2 is bigger than the worth in A3 or the worth in A2 is lower than the worth in A4, return “OK.” In any other case, return “Not OK.” (OK)
=IF(OR(A5<>”Sprockets”, A6<>”Widgets”), “OK”, “Not OK”)	If the worth in cell A5 shouldn’t be equal to “Sprockets” or the worth in A6 shouldn’t be equal to “Widgets”, return “OK.” In any other case, return “Not OK.” (Not OK)
=IF(OR(A2<>A3, A2<>A4), “OK”, “Not OK”)	If the worth in cell A2 shouldn’t be equal to the worth in A3 or the worth in A2 shouldn’t be equal to the worth in A4, return “OK.” In any other case, return “Not OK.” (OK)

For extra details about the way to use these capabilities, see IF perform, AND perform, and OR perform.

Prime of Web page

Create conditional formulation

What do you need to do?

Create a conditional formulation that leads to a logical worth (TRUE or FALSE)

Instance

Create a conditional formulation that leads to one other calculation or in values aside from TRUE or FALSE

Instance

Leave a Reply Cancel reply