Conditional Functions Reference Sheet

< Back to Search results

 Category: [Excel]  Demo Available 

Conditional functions reference sheet



Checks if all conditions are met and returns only “TRUE” or “FALSE” – the purpose is to check multiple conditions.


The above formula tests if a number in A1 is greater than zero and less than 100, use The AND function can be used as the logical test inside the IF function to avoid extra nested IFs, and can be combined with the OR function.


The above formula will provide "Approved" only if the value in A1 is greater than 0 and less than 00.


Text   cells or empty cells are ignored.

The AND function is not case sensitive and does not support wildcards.



Checks if a condition is met and returns a value if yes and if no.

IF C2 > 100 then multiply C2 by 5%, if not then multiply C2 by 2%


IF C2 > 100 then say yes, if not then say no



– if a cell contains an error, you can tell Excel to display an alternative result.

IF B2 has the number 20 and B3 is blank then in cell B4 if the follow formula is entered then the following message will be provided in B4: “Please enter a value in B3.” i.E. this replaces the #DIV/0! Error. As soon as a value is entered in B3 the formula will provide the required result.


This can also be combined with VLOOKUP as follows:



If VLOOKUP returns #N/A error because a lookup value is not found then the formula above will return the message “Not Found”.


Changes “TRUE” to “FALSE”, and “FALSE” to “TRUE”

If Cell D1 is blank then the following formula will return “TRUE”


This can also be revered to “FALSE” as follows:



Checks if any conditions are met and returns only “TRUE” or “FALSE”

To test if the value in A1 OR the value in B1 is greater than 85, use the following formula:



The following formula will return "Pass" if the value in A1 is greater than 85 OR the value in B1 is greater than 85.

=IF(OR(A1>85,B1>85), "Pass", "Fail")


If you enter OR as an array formula, you can test all values in a range against a condition. For example, this array formula will return TRUE if any cell in B1:B100 is greater than 25:




The “exclusive or” statement returns true if the number of TRUE statements is odd.

Additional information can be found: https://support.office.com/en-us/article/xor-function-1548d4c2-5e47-4f77-9a92-0533bba14f37



Combine IF with AND to have multiple conditions

IF Statement between two numbers:



See also:

Dates and time Excel formulas reference sheet
Conditional Functions Reference Sheet
Excel different types of data reference sheet
Useful formulae reference sheet
Financial formulae reference sheet
Index and match reference sheet
Lookup formulae reference sheet
Maths functions excel formulae sheet


Free Training Course: Lesson 1 - Excel Fundamentals


See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions


Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.



stars (0 Reviews)