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.
Isolated IF function
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)
Nested IF function
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")
=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".
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.
- How to apply if function in Excel: with dates, with text > Guide
- Excel if range of cell contain a text put a cell value in [solved] > Forum - Excel
- Excel multiple functions in one cell > Forum - Excel
- Having Excel IF formulas print text and calc [solved] > Forum - Excel
- Search and find using VBA in Excel > Guide
- Change date format in Excel: to dd/mm/yyyy, mm/dd/yyyy
- Copy data from one excel sheet to another: automatically
- Fix arrows keys in Excel: Scroll lock, alternative method
- How to take screenshot in Excel: shortcut, sheet, cell
- How to apply a function to multiple sheets on Excel
- Convert numbers to words in Excel: without VBA, formula
- How to change author name in Excel: spreadsheet, VBA
- How many IF statements can you nest in Excel
- How to use Excel color cell if formula
- How to display multiple columns in a validation list
- How to use an IF statement to add 1 to total
- How to perform a partial cell match in Excel?
- How to run macro when data entered in a cell
- Check if a value exists in an array VBA
- VBA select case like: operator, string, statement
- What is the VBA code to select last sheet in workbook
- VBA Excel color codes: index number, list
- How to insert file path in excel: cell, sheet
- How to disable auto recover in Excel
- How to enable VBA in Excel: Mac, Office 365
- How combine IF function, SEARCH and ISERROR in Excel
- How to enter multiple lines in a single Excel cell
- How to insert GIF in Excel: 365, sheet, VBA
- How to count names in Excel: formula, using COUNTIF
- Open and convert an Excel file in Notepad
- Repeat rows in Excel: based on cell value, VBA
- How to automatically transfer data between sheets in Excel
- How to manipulate data in Excel: VBA
- How to copy a Macro into a blank cell
- Insert a hyperlink in Excel: with text, to another tab
- Recover Excel file: previous version
- Run macro on opening: worksheet, workbook
- How to transfer data from one Excel sheet to another?
- Most useful Excel formulas: for data analysis
- Recalculate Excel Workbook Before Saving
- Select the default number of processors in Excel
- Insert picture in Excel: cell, shortcut, using formula
- How to add sheet to workbook: VBA, Excel
- Unlock password protected Excel file: Macro
- Excel functions in French
- How to create a cascading combo box: Excel, VBA
- How to create calculator in Excel VBA
- Excel export data: from one sheet to another
- How to add a number of days to a date in Excel
- How to fill multiple Excel sheets from master sheet
- How to copy data to another workbook: using VBA
- Transfer Excel data from one sheet to another: VBA
- Excel VBA add command button programatically
- How to change a column to numeric in Excel?
- Mark sheet grade formula in Excel: template
- How to connect VB 6.0 with MS Access
- How to remove leading apostrophe in Excel
- How to create UserForm: in Excel, VBA
- Select empty cell in Excel: VBA
- Excel send value to another cell
- Using VBA to find last non empty row: in column, in table
- How to show or hide formula bar in Excel: VBA, shortcut
- How to change enter key function in Excel
- How to use Excel auto numbering formula
- Conditional formatting with dates: in Excel
- How to calculate VAT in Excel: formula
- Transfer a worksheet to another Excel workbook: without VBA
- Delete duplicates in Excel: column, formula
- Send email with attachment Excel: VBA, macros
- How to Insert a Transparent Image into an Excel Document
- Compare two Excel sheets: and combine data
- Copy data from one Excel workbook to another
- Auto generate serial number in Excel: VBA, formula
- How to generate email notifications for Excel updates
- How to create a drop-down List in Excel
- Split a workbook into individual files in Excel
- How to insert blank rows using macros in Excel
- Run Macros in Excel: online, shortcuts, VBA
- How to clear formatting in Excel
- Insert an image into an Excel comment box
- Credit summation formula
- Create new sheet based on cell value: Excel, macro
- How to insert multiple rows in Excel with a macro?
- How to change Excel date format
- How to create a timer in Excel VBA
- How to use conditional formatting in Excel
- How to count occurrences of characters and numbers in Excel
- How to split text in columns in Excel
- Shortcuts to insert in Excel
- How to copy data to multiple worksheets in Excel
- Create a new workbook and copy data in Excel via a Macro
- Update dates in Excel spreadsheet: each year, formula
- How to change row color in Excel VBA based on value