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


            


MAKING A SIMPLE TIME IN, TIME OUT COUNTER in MS Excel

MAKING A SIMPLE TIME IN, TIME OUT COUNTER

1.
2. Apply Formatting accordingly
A1 - Category: Date, Type: 3/14/01
B1 - Category: Time, type: 1:30 PM
C1 - Category: Time, type: 1:30 PM
D1 - Category: Number, use two decimal places

3. Input Date, Time in and Time out

4. For cell D2, apply the following formula
=IF(C2=B2, 24,MOD(C2-B2,1)*24)

STEVE CHASE' COUNTDOWN TIMER in MS Excel

STEVE CHASE' COUNTDOWN TIMER

1. Type the labels in A1 through A4
A1 = Date and Time today
A2 = Hour
A3 = Minute
A4 = Second

2. Enter the formulas in column B
B1 =NOW()
B2 =HOUR(B1)
B3 =MINUTE(B1)
B4 =SECOND(B1)

*Note: The NOW function displays the current date and time. Pressing F9 key will update the value.
The HOUR function displays the current hour expressed in Military Time; Zero is 12:00 AM and 23 is 11:00PM

3. Type these labels in D1 though D4
D1 = Event Date
D2 = Event Hour
D3 = Event Minute
D4 = Event Second

4. Type in the datails of the Event Schedule in cells E1 through E4

5. Type these labels in cells G1 through G4
G1 = Days
G2 = Hours
G3 = Minutes
G4 = Seconds

6. Type in the FORMULAS in H1 through H4
H1 =INT(E1-B1)
H2 =IF(E2>=B2,E2-B2,24-B2+E2)
H3 =INT(E3-B3)
H4 =INT(E4-B4)

7. Make the Display by using this function
="Just"&H1&"Days"&H2&"Hours"&H3&"Minutes"&H4&"Seconds Left"

Date and Time Functions in MS Excel

Overview of Dates and Time in Excel
Objectives:
* Understand how Date and Time Functions are used in Excel
* Create a Database using Date and Time Functions
* Create a Countdown Timer with Date using Excel Date and Time Functions

DATES IN EXCEL
* Excel Dates can be added, subtracted and included in other calculations
* Excel stores dates in "Assigned Serial Numbers"

1 - assigned Serial Number for Date "January 1, 1900" for WINDOWS
1 - assigned Serial Number for Date "January 1, 1904" for MAC

Question:
What Date represents 30240?

Exercise 1.a. Convert these numbers into Date
143
14344
54

Exercise 1.b. Convert these Dates to Numbers
1 April 14, 2017
2 July 5, 1984
3 March 11, 2015

TIME IN EXCEL
*Times are stored as "Decimal Numbers" between 0.0 and 0.99999
*Excel stores dates in "Assigned Serial Numbers"

0.0 - 00:00:00 (12:00:00 AM)
0.99999 - 11:59:59 PM)

Exercise 1.a. Covert these Decimal Numbers to Time
1 0.4375
2 0.47197
3 0.50347

DATE FUNCTIONS USED IN EXCEL
* NOW() and TODAY () Function
=NOW() - displays current date and time
=TODAY() - displays current date only
* Determining the age based on Birthdate
=int(yearfrac("Cell Number of Birthdate",TODAY()))
--INT (integer-number) Rounds the number to the nearest integer
--YEARFRAC (year fraction) Returns the year fraction representing the number of whole
                   days between start_date and end_date

* Determining if Minor/Adult
=if(int(yearfrac("Cell Number of Birthdate",TODAY())) <18, "Minor","Adult")

* Determining the age on a specific date
=int(yearfrac("Cell Number of Birthdate",DATE(Year,Month,Day)
--MONTH should be in number EXAMPLE: October is 10, January is 1

CALCULATING RETIREMENT DATE IN EXCEL
* Create the table below:

* Determining the Retirement Date (END DATE)
=EDATE("reference cell for birthdate",12*65)

* Determining the number of Years before Retirement
=YEARFRAC(TODAY(), reference cell for end date)

* To Display only the year in the Retirement Date
=YEAR(EDATE(reference cell for birthdate, 12*60)

VLOOKUP, MATCH AND INDEX MATCH - MS EXCEL INTERMEDIATE

VLOOKUP Function
VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right.
FORMULA
=VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup])
VALUE - The value to look for in the first column of a table.
TABLE - The table from which to retrieve a value.
COL_INDEX_NUM - The column in the table from which to retrieve a value.
RANGE_LOOKUP - [optional] TRUE = approximate match (default). FALSE = exact match.
SEARCH
ID Number First Name Last Name Email Address Department
567 Holly Holm hh@hh.com OPD
1 2 3 4 5
ID Num First Name Last name Email Address Department
123 Joe Rogan jr@gmail.com HR
234 Brendan Schaub br@aol.com Purchasing
345 Ronda Rousey rr@yahoo.com Med Admin
456 Manny Pacquiao mp@hotmail.com OR
567 Holly Holm hh@hh.com OPD
MATCH FUNCTION
The basic MATCH function returns a NUMBER based on the relative position of a lookup value within a defined array / column.  
FORMULA
=MATCH(lookup value, lookup array, match type)
LookUpValue RESULT
Pacquiao 4
INDEX FUNCTION
=INDEX(array,MATCH formula )
LookUp Value Index Match
Last Name ID Num
Pacquiao 456
Rousey  
   
=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))
VLOOKUP to a different Sheet
Formula
=VLOOKUP(A2,Sheet1!F10:G20,2,FALSE)