=AND

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

=AND(A1>0,A1<100)

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.

=AND(A1>0,A1<100)

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

Note:

Text   cells or empty cells are ignored.

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

=IF

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,C2*0.05,C2*0.02)

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

=IF(C2>100,”yes”,”no”)

=IFERROR

– 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.

=IFERROR(B2/B3)

This can also be combined with VLOOKUP as follows:

=IFERROR(VLOOKUP(VALUE,DATE,COLUMN,0),”Not Found”)

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

=NOT

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

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

=ISBLANK(D1)

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

=NOT(ISBLANK(D1))

=OR

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:

=OR(A1>85,B1>85)

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:

={OR(B1:B100>25}

=XOR

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

IF AND

Combine IF with AND to have multiple conditions

IF Statement between two numbers:

=IF(AND(D7>=D9,D9<=D10),D12,D13)