Session 6: Advanced Formulas and Functions
Objective: Introduce students to more advanced Excel formulas and functions,
focusing on logical functions, text functions, and date/time functions.
Duration: 1 hour
1. Review of Session 5 (5 minutes)
- Quick recap of key points from the previous session.
- Answer any lingering questions.
2. Introduction to Logical Functions (20 minutes)
- IF Function:
- Syntax: `=IF(logical_test, value_if_true, value_if_false)`
- Example: `=IF(B2 >= 50, "Pass", "Fail")`
- Demonstration: Creating a pass/fail system based on student scores.
- Nested IF Functions:
- Using multiple IF statements within one formula.
- Example: `=IF(B2 >= 90, "A", IF(B2 >= 80, "B", IF(B2 >= 70, "C", "D")))`
- Demonstration: Grading system with multiple conditions.
- AND & OR Functions:
- Syntax: `=AND(condition1, condition2, ...)`, `=OR(condition1,
condition2, ...)`
- Example: `=AND(B2 >= 50, C2 = "Yes")`
- Example: `=OR(B2 >= 50, C2 = "Yes")`
- Demonstration: Checking multiple conditions in a single formula.
3. Introduction to Text Functions (15 minutes)
- CONCATENATE/CONCAT Function:
- Syntax: `=CONCATENATE(text1, text2, ...)`, `=CONCAT(text1, text2, ...)`
- Example: `=CONCATENATE(A2, " ", B2)` or `=CONCAT(A2, " ", B2)`
- Demonstration: Combining first and last names into a full name.
- LEFT, RIGHT, and MID Functions:
- Syntax: `=LEFT(text, num_chars)`, `=RIGHT(text, num_chars)`, `=MID(text,
start_num, num_chars)`
- Example: `=LEFT(A2, 3)`, `=RIGHT(A2, 3)`, `=MID(A2, 2, 3)`
- Demonstration: Extracting parts of text strings.
- LEN and FIND Functions:
- Syntax: `=LEN(text)`, `=FIND(find_text, within_text, [start_num])`
- Example: `=LEN(A2)`, `=FIND(" ", A2)`
- Demonstration: Finding the length of a text string and the position of a
specific character.
4. Introduction to Date and Time Functions (15 minutes)
- TODAY and NOW Functions:
- Syntax: `=TODAY()`, `=NOW()`
- Example: `=TODAY()`, `=NOW()`
- Demonstration: Displaying the current date and time.
- DATE and TIME Functions:
- Syntax: `=DATE(year, month, day)`, `=TIME(hour, minute, second)`
- Example: `=DATE(2024, 5, 18)`, `=TIME(14, 30, 0)`
- Demonstration: Creating specific date and time values.
- YEAR, MONTH, DAY, HOUR, MINUTE, SECOND Functions:
- Syntax: `=YEAR(date)`, `=MONTH(date)`, `=DAY(date)`, `=HOUR(time)`,
`=MINUTE(time)`, `=SECOND(time)`
- Example: `=YEAR(A2)`, `=MONTH(A2)`, `=DAY(A2)`
- Demonstration: Extracting components of dates and times.
5. Hands-on Practice (10 minutes)
- Create a new worksheet and enter sample data (e.g., student names, scores,
attendance status, dates of birth).
- Use the IF function to determine pass/fail status based on scores.
- Create full names using CONCATENATE/CONCAT function.
- Extract first names using LEFT and find the length of the full names using
LEN.
- Use TODAY function to display the current date and calculate ages using
YEAR and DATE functions.
6. Detailed Steps and Demonstrations:
1. IF Function:
1. Select the cell where you want the result to appear.
2. Type `=IF(` to start the formula.
3. Enter the logical test (e.g., `B2 >= 50`).
4. Enter the value if true (e.g., `"Pass"`).
5. Enter the value if false (e.g., `"Fail"`).
6. Close the formula with `)` and press Enter.
- Example:
- `=IF(B2 >= 50, "Pass", "Fail")`
2. Nested IF Functions:
1. Type `=IF(` to start the formula.
2. Enter the first logical test (e.g., `B2 >= 90`).
3. Enter the value if true (e.g., `"A"`).
4. Enter another IF function for the value if false (e.g., `IF(B2 >= 80, "B", IF(B2
>= 70, "C", "D"))`).
5. Close each IF function with `)` and press Enter.
- **Example**:
- `=IF(B2 >= 90, "A", IF(B2 >= 80, "B", IF(B2 >= 70, "C", "D")))`
3. AND & OR Functions:
1. Type `=AND(` or `=OR(` to start the formula.
2. Enter the conditions separated by commas (e.g., `B2 >= 50, C2 = "Yes"`).
3. Close the formula with `)` and press Enter.
- Example:
- `=AND(B2 >= 50, C2 = "Yes")`
- `=OR(B2 >= 50, C2 = "Yes")`
4. CONCATENATE/CONCAT Function:
1. Type `=CONCATENATE(` or `=CONCAT(` to start the formula.
2. Enter the text strings and cell references separated by commas (e.g., `A2, "
", B2`).
3. Close the formula with `)` and press Enter.
- Example:
- `=CONCATENATE(A2, " ", B2)`
- `=CONCAT(A2, " ", B2)`
5. LEFT, RIGHT, and MID Functions:
1. Type `=LEFT(`, `=RIGHT(`, or `=MID(` to start the formula.
2. Enter the text string or cell reference and the number of characters to
extract (e.g., `A2, 3` for LEFT and RIGHT, or `A2, 2, 3` for MID).
3. Close the formula with `)` and press Enter.
- **Example**:
- `=LEFT(A2, 3)`
- `=RIGHT(A2, 3)`
- `=MID(A2, 2, 3)`
6. LEN and FIND Functions:
1. Type `=LEN(` or `=FIND(` to start the formula.
2. Enter the text string or cell reference (e.g., `A2` for LEN or `" ", A2` for
FIND).
3. Close the formula with `)` and press Enter.
- Example:
- `=LEN(A2)`
- `=FIND(" ", A2)`
7. TODAY and NOW Functions:
1. Type `=TODAY()` or `=NOW()` in a cell.
2. Press Enter to display the current date or date and time.
- Example:
- `=TODAY()`
- `=NOW()`
8. DATE and TIME Functions:
1. Type `=DATE(` or `=TIME(` to start the formula.
2. Enter the year, month, and day for DATE or hour, minute, and second for
TIME separated by commas (e.g., `2024, 5, 18` for DATE and `14, 30, 0` for
TIME).
3. Close the formula with `)` and press Enter.
- Example:
- `=DATE(2024, 5, 18)`
- `=TIME(14, 30, 0)`
9. YEAR, MONTH, DAY, HOUR, MINUTE, SECOND Functions:
1. Type `=YEAR(`, `=MONTH(`, `=DAY(`, `=HOUR(`
