*This article will explain how to use the conditional functions IF, AND, OR and NOT on Microsoft Excel. Each of these functions can be used as part of a formula in a cell to compare data samples in any number of columns or sheets in an Excel file. For ease of understanding, we have also included a visual map that illustrates how simple changes in each formula affects your results.*

The **IF function** allows you to apply a condition to your data whereby one result is returned if your condition is **TRUE** and another result if the condition is **FALSE**. IF functions can be either **isolated**, meaning that only one condition is applied to your data, or **nested**, where multiple criteria are applied to generate a TRUE or FALSE result.

Here are two examples of an **isolated IF function**. In these examples, we are only applying one condition to generate our results:

*If the grade in column A1 is greater than or equal to 19, then display 20; otherwise, display A1*.

**= IF (A1> = 19, 20, A1)**

*If the data in A1 is "Discount", then subtract 10%, otherwise keep A1*.

**= A1 * IF (A1 = "Discount"; 1-10%; 1)**

Here are some examples of a **nested IF function**. In these cases, multiple IF functions are applied to the data, generating one of several responses depending on the data housed in the data constant. You'll notice that each criteria is separated by the use of a semicolon, and that the number of parentheses used at the end of our formula is dependent on the number of functions "nested" inside:

*If the grade in A1 is greater than 12, then display "continue"; if A1 > 18, then display "very good", otherwise display "satisfactory". But if A1 <= 12, then display "improve"*.

**=IF(A1>12;"continue," & IF(A1>18;"very good";"satisfactory");"improve")**

or

**=IF(A1<12;"continue";IF (A1<18;"continue, satisfactory";"continue, very good"))**

*If the grade in A1 is less than 4, then display "poor" ; if A1 is between 4 and 8, display "Unsatisfactory" ; if A1 is between 8 and 12, display "OK" ; if A1 is between 12 and 16, display "Good" ; if not, display "Very Good"*.

**=IF(A1<4;"Poor";SI(A1<8;"Unsatisfactory";IF(A1<12;"OK";SI(A1<16;"Good";"Very Good"))))**

Unlike an **IF function**, which can function in isolation with one given condition, the **AND function** tests a plurality of user-defined conditions and returns TRUE if **all** data conditions are met, and FALSE if even one condition is not met.

Similar to other conditional formulas, you do have the capability to define your own results for TRUE and FALSE. Here's an example:

*Show "The Countess!" if all of these conditions are met: A2 (sex) = woman, B2 (status) = married, C2 (spouse) = count and D2 (score) = present; if not, display "Hello!"*:

**=IF(And(A2="woman";B2="married"; C2="count"; D2="present"); "The Countess!"; "Hello!")**

You'll note that this formula begins with the **IF** function. This use of IF signals to Excel that we are about to apply some sort of logical formula. Immediately following the IF function is our **AND** function and our data, grouped in the same set of parentheses to isolate the functions. Note that the "TRUE" condition ("The Countess!") is listed before the "FALSE" condition (Hello!). Both results are separated by a semicolon and are included in the parentheses.

The **OR function** tests user-defined conditions and returns TRUE (or similar) if any of the conditions stipulated by the user are met, and FALSE (or similar) if none of the conditions apply. The OR function is much less absolute than the AND function. Here's an example:

*Display "Pilot" if A3 contains one of the following conditions: plane, formula 1, motor; display "Conductor" if A3 contains car or work; if not, display "?"*

**=IF(OR(A3="plane";A3="formula 1"; A3="motor");"Pilot"; IF(OR(A3="car";A3="work");"Conductor";"?"))**

The **NOT function** returns an opposite value of a user supplied logical value or expression.

The formula **=IF(ENT(A3)=A3; "whole";"decimal")** could also be written as **=IF(NOT(ENT(A3)=A3); "decimal";"whole")**.

In the same way, the formula **=IF(A4<>"French";"Foreign";"European") is equivalent to <bold>= IF(NOT(A4="French");"Foreign";"European")**.

The functions **AND**, **OR**, and **NOT** are most often associated with the **IF** function.

What's interesting (and satisfying) about these functions is that, together, they can be used to create a flow chart to serve as a visual representation of a problem's algorithm. When using these functions, ensure that all parentheses and semicolons are correctly placed to ensure the best results.

Photo by John Schnobrich on Unsplash

Related

- Excel si
- Si et excel - Best answers
- Fonction if excel - Best answers
- Transfer data from one excel worksheet to another automatically - How-To - Excel
- How to change date format in excel to dd/mm/yyyy - How-To - Excel
- Excel @ in formula ✓ - Forum - Excel
- Number to words in excel without vba ✓ - Forum - Excel
- Excel if cell contains date then return value ✓ - Forum - Office Software

This document, titled « Excel Functions IF, AND, OR, and NOT », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

DON'T MISS

- How to Recover a Disabled Facebook Account
- How to Fix a Blurry Snapchat Video
- How to Recover Your Yahoo Account
- How to Create a Gmail Account
- How to Disabled Write Protection on a USB
- Facebook Code Not Received
- Computer Boots to Black Screen
- How to Get More Followers on Instagram
- How to Get Free Diamonds in Free Fire
- How to Upload High Quality Photos to Instagram
- How to Install a VPN