(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")
* 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"))
