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 MULTIPLE IF FUNCTIONS (NESTED IF FUNCTIONS)
(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"))
MAKING A SIMPLE TIME IN, TIME OUT COUNTER in MS Excel
MAKING A SIMPLE TIME IN, TIME OUT COUNTER
1.
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"
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)
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
=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) | |||||
Subscribe to:
Posts (Atom)