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.

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"

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

*

=int(yearfrac("Cell Number of Birthdate",TODAY()))

--

--

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

* Create the table below:

=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

*

**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 wholedays 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)