MS Excel 2003 Training 101 - Conditional Formulas

Testing whether conditions are true or false and making logical comparisons between expressions are common to many tasks. You can use the AND, OR, NOT, and IF function to create conditional formulas.
The IF function uses the following arguments.



Formula with the IF function

 logical_test: The condition that you want to check.
 value_if_true: The value to return if the condition is true.
 value_if_false: The value to return if the condition is false.

What do you want to do?
Create a conditional formula that results in a logical value (TRUE or FALSE)
Create a conditional formula that results in another calculation or in values other than TRUE or FALSE
Create a conditional formula that results in a logical value (TRUE or FALSE)
To do this task, use the AND, OR, and NOT functions, and operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.).



Create a conditional formula that results in another calculation or in values other than TRUE or FALSE

To do this task, use the IF, AND, and OR functions.



Advanced If statement usage:
Using multiple criteria with the “AND” and “OR” functions

Sometimes you need to check more than one criteria with the If statement. For example:
Only if the student has grades greater than 80 in Math, greater than 80 in English and greater than 80 in History, only then write “good student”, else write “Average Student”.


The way to implement such a case is using the “AND” function which will include all the multiple criteria inside of it.
This “AND” function, is then put inside the criteria section of the If statement.
Thus for the above example, the whole function could look something like this:


=IF(AND(C1>80,D1>80,E1>80) , “Good student” , “Average Student” )

Note that the criteria part: AND(C1>80,D1>80,E1>80) will be met only if all three parts of it are met.


The function “OR” is used in a same manner, but its meaning is slightly different.
Instead of checking if all its parts is met (as is the case of the “AND” function), it checks if at least one of the criteria inside it is met. If it is, then the whole “OR” is considered met.


Hence, in the following formula:

=IF(OR(C1>80,D1>80,E1>80) , “Good student” , “Average Student” )

It is enough that C1 is really greater than 80 , or that D1 is really greater than 80 or that E1 is really greater than 80 (or some combination of these three), to have the if function write “Good student”. Only if none of these cells (C1, D1 or E1) are greater than 80 the If function will result writing “Average Student”.