CONDITIONAL FORMATTING in MS Excel

Conditional Formatting
Text that contains ....

IF FUNCTIONS in MS Excel

DATA PROCESSING AND ADVANCE MATH OPERATIONS
(IF FUNCTION, CONDITIONAL FORMATTING, COUNTIF AND SUMPRODUCTS)

OBJECTIVES:
* Understand how the IF Function is composed in Excel
* Learn to use Conditional Formatting in practical applications
* Create a simple Database with IF and Conditional Formatting functions

THE IF FUNCTION
=IF(logical_test, [value_if_true], [value_if_false])

WHERE:
logical_test - condition based on statements compared by >,< or =
[value_if_true] - result if Logical Test/Condition is TRUE
[value_if_false] - result if Logical Test/Condition is FALSE

EXAMPLE:
=IF(1=0,"Zero", "Not Zero")

USING THE IF FUNCTION FOR MULTIPLE CONDITIONS

* Including AND or OR functions on your "logical test" will enable to test multiple conditions.

* The AND Function will result to a TRUE if "ALL Conditions are met"

* The OR Function will result to a TRUE if "ANY of the Conditions is met"

EXAMPLE:

Supposed 3 students took exams in Match and Science, the student will pass if the following conditions are met.

1. Score in Math is Greater than or Equal to 20

2. Score in Science is Greather than or Equal to 30

SOLUTION

* First, we need to translate the following conditions to Logical Tests

1. Score in Math is Greater than or Equal to 20 -- (Score in Math>=20)

2. Score in Science is Greather than or Equal to 30 -- (Score in Science>=30)

* Second, we need to determine what Condition to use, AND or OR

   the student will pass if the following conditions are met. = ALL CONDITIONS (AND)

FORMULA

=IF((AND(Ref Cell of Score in Math>=20, Ref Cell of Score in Science>=30)), "Pass", "Fail")

USING AND/OR FUNCTIONS

* SUPPOSE WE HAVE THESE TWO CONDITIONS:

  Condition 1: Score in Math>=20, Score in Science>=30

  Condition 2: Score in Math>=15, Score in Science>=20

* TRANSLATING TO AND/OR FUNCTION

 =OR(AND(Score in Match>=20,Score in Science>=30),AND(Score in Math>=15,Score in        

           Science>=20)),"Pass","Fail")

FORMULA

=IF(OR(AND(Score in Match>=20,Score in Science>=30),AND(Score in Math>=15,Score in 

          Science>=20)),"Pass","Fail")

USING MULTIPLE IF FUNCTIONS (NESTED IF FUNCTIONS)

* Suppose we have three conditions:

GOOD: 60 or more (>=60)

SATISFACTORY: between 60 and 40 (>40 and <60)

POOR: 40 or less (<=40) SOLUTION

*First we need to get the SUM of Math and Science

*Then this formula =IF(TOTAL>=60, "Good",IF(TOTAL>40,"Satisfactory","Poor"))