Home > Formulas > Logical > Topics Excel Logical Functions GuideA short guide to the Excel logical functions, including IF, IFS, IFERROR, AND, OR, TRUE, FALSE and more. Some logical functions, such as LAMBBDA, LET and MAKEARRAY, are only available in Excel 365 or Excel for the Web. Authors: Debra Dalgleish |
IF FunctionThe Excel IF function is helpful if you want to check something on a worksheet, then show result A or B. This short video shows the steps for setting up a simple IF formula. There are written steps on the IF function page. Video Timeline
Example - IFIn this formula, there are two IF functions, with one nested inside the other. =IF(C7="","", IF(D7="","", C7*D7)
More Information - IFClick the link to read more about the IF function, and see other examples, and get a download file. |
IFS FunctionIn Excel 2016 and later, there is also an IFS function, which you can use instead of multiple nested IF functions. With the IFS function:
Example - IFSIn this example, instead of using a nested IF formula, I used an IFS formula in column E. =IFS(COUNTA(B9:D9)=3, C9*D9, B9="","", C9="","no price", D9="","no qty") The formula checks 3 cells in an order form, to see if these cells have values entered:
More Information - IFSClick the link to read more about the IFS function, and see other examples, and get a download file. |
IFERROR / IFNA Functionsthe IFERROR and IFNA functions check the results of a calculation. If the result is an error, the value that you specify is returned instead of that error.
Example - IFERRORIn this example, I used IFERROR with the SEARCH function, to prevent a #VALUE! error from showing in cell D5. =IFERROR(SEARCH(B5,B2),"Not Found") The SEARCH function checks cell B2, to see if it contains the text that I typed in cell B5.
|
AND / OR / XOR FunctionsUse the AND and OR functions to combine 2 or more tests, such as column B contains "Paid" and column D contains "Shipped".
You can combine AND and OR functions with other Excel functions, such as IF function.. Example - ANDThis example shows AND combined in a formula with IF. =IF(AND(B4="Paid",D4="Shipped"),"Completed","Pending")
Example - OR / XORThis example shows the difference between OR and XOR. Both formulas check the Status and Amt cells, to see if they are empty.
More Information - ANDClick the link to read more about the AND function, and see other examples, and get a download file. |
More Logical FunctionsThe following logical functions are also available, depending on which Excel version you're using.
Note: The links go to each function's Help page on the Microsoft website.
|
Download the Sample File
More Functions |
Last updated: May 23, 2023 9:33 AM