Leading the way in Microsoft Office Development

What is a Conditional Formula ?

If you only want to perform a calculation when a cell contains a specific value you can create a conditional formula.

Allows you to perform calculations on only those numbers that meet a certain condition.

A conditional formula returns one value if a condition is True and a different value if the condition is False.

You can create a conditional formula by using the IF() function.

This function requires three arguments.

Logical_test

this must be an argument that returns a logical value, either True or False.

For example you could use A1>50 to check if the value in cell "A1" is greater than 50.

You can use any combination of cell references, oeprators, constants and even other functions to create the arguments.

You could use SUM(A1:A1)>200

Value_if_true

This is the value that will be returned by the IF function is the logical test evaluates to True.

If you want to return a text string you must enclose in qutation marks.

Value_if_false

Examples

IF(A1>50, "TRUE", "FALSE")

Checking for Multiple Conditions

If you want to check for multiple conditions you can use "nested" IF functions.

When you nest the IF functions, you specify an IF function as the value of either of the Value_if_true or value_if_false arguments

You can combine or nest up to 7 levels of IF functions within one formula.

Examples

IF(A1>50,IF(A2<100,"sum is between 50 and 150","sum is greater than 150"),"sum is less than 50").

To create a condition you can use various comparison operators, such as greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=) and equal to (=).

Type a comparison operator, the condition and then a comma.

=SUM( IF(Named_Range1 > 10,Named_Range2) )

The IF function can be used with array formulas

Must be entered with (Ctrl + Shift + Enter)