Microsoft Excel And - Or - Not Functions

AND, OR, NOT Functions

The other logical functions are used to combine more than one logical test or to reverse the logical effect of an expression.

Examples:

AND(A1="UK",B1>500)                                     A1 is "UK" and B1 is greater than 500.

OR(A1="UK",B1>500)                                       Either A1 is "UK" or B1 is greater than 500.

NOT(AND(A1="UK",B1>500))                          A1 is not "UK" and B1 is not greater

than 500.

OR(AND(A1="UK",B1>500),A1="France")     Either A1 is "UK" and B1 is greater

than 500 or A1 is "France".

Joining conditional tests together

You may need to combine different logical tests in order to make the relevant judgement. Use the logical functions AND and OR to join your expressions. Please note that these are functions in Excel, not operators, so both AND and OR are entered in front of the expressions being combined and not between them. For example, in our formula both the value in A1 must be equal to 100 and the value in C1 must be greater than 250:

AND(A1=100,C1>250)    This is the correct form, the function form.

A1=100 AND C1>250      Although more logical, this is incorrect, Excel does not

have an AND operator.

You can combine up to 255 tests; just list them separated by commas inside the function’s parentheses.

For example, if the value of cell A1 is 100 and the value of C1 is greater than 250 then display "Big" in the cell otherwise display "Small".

=IF(AND(A1=100,C1>250), "Big", "Small")

If the value of cell A1 is 100 or the value of C1 is greater than 250 then display "Big" in the cell otherwise display "Small".

=IF(OR(A1=100,C1>250), "Big", "Small")

Although the logical test may consist of several parts it still only has  one outcome, TRUE or FALSE. The individual logical tests may have a variety of TRUE or FALSE values but we must consider the outcome of the whole expression.

If the tests are combined using the AND function then in order for the whole expression to be TRUE all the tests must evaluate to TRUE. If you use OR to combine the tests then only one of the tests has to be TRUE in order for the  whole expression to be TRUE.

Function	Individual Test Results	Logical Result
AND	TRUE, TRUE, FALSE	FALSE
OR	TRUE, TRUE, FALSE	TRUE

Mouse Training Course Index

Contact Email

For Further Course Details Visit Our Homepage

Microsoft Accredited IT Training Provider

Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators

Microsoft MOS Test Centre London