Class 7: Advanced
Formulas and Functions
Objective:
Learn to use advanced
Excel functions and formulas to solve complex problems, including logical,
lookup, and text-based operations.
1. Logical
Functions
Logical functions
allow you to make decisions based on specified conditions. These functions
check whether conditions are met and return a particular value based on that
condition.
Key Logical
Functions:
- IF Function: The IF function checks whether a
condition is true or false and returns one value if true, and another
value if false.
Syntax:
=IF(logical_test, value_if_true, value_if_false)
Example: If a score in cell A1 is greater than or equal to 50, return "Pass", otherwise return "Fail".
=IF(A1>=50,
"Pass", "Fail")
- AND Function: The AND function returns TRUE if all
conditions are true, and FALSE if any condition is false.
Syntax:
=AND(logical1, logical2, ...)
Example: Check if both conditions (A1 greater than 50
and B1 less than 100) are true.
=AND(A1>50, B1<100)
- OR Function: The OR function returns TRUE if any
of the conditions are true, and FALSE if all conditions are false.
Syntax:
=OR(logical1, logical2, ...)
Example: Check if either A1 is greater than 50 or B1 is
less than 100.
=OR(A1>50, B1<100)
2. Lookup Functions
Lookup functions are
essential for searching through data in large tables. These functions help you
retrieve specific data based on a search criterion.
Key Lookup
Functions:
- VLOOKUP (Vertical Lookup): The VLOOKUP function looks for a value in
the leftmost column of a table and returns a value from a specified column
in the same row.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: The value you want to
search for.
- table_array: The range of the table where
the lookup will occur.
- col_index_num: The column number from
which to return the value.
- range_lookup: TRUE for an approximate
match, FALSE for an exact match.
Example: Find the price of a product (in column 2)
based on the product name in column 1.
=VLOOKUP("Product A", A2:B10, 2, FALSE)
- HLOOKUP (Horizontal Lookup): The HLOOKUP function searches for a value
in the top row of a table and returns a value from a specified row.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example: Look up a student's score (from row 3) based
on their name in the top row.
=HLOOKUP("John", A1:F3, 3, FALSE)
- INDEX Function: The INDEX function returns the value of a
cell within a table, based on row and column numbers.
Syntax:
=INDEX(array, row_num, [column_num])
Example: Return the value from the second row and third
column of a table.
=INDEX(A2:C10, 2, 3)
- MATCH Function: The MATCH function searches for a value
in a range and returns the relative position of that item.
Syntax:
=MATCH(lookup_value, lookup_array, [match_type])
Example: Find the position of "John" in the
range A1
.=MATCH("John", A1:A10, 0)
3. Text Functions
Text functions help
manipulate and manage text data in Excel. They are useful for combining or
extracting specific parts of text strings.
Key Text Functions:
- CONCATENATE Function (or CONCAT): The CONCATENATE or CONCAT function joins
two or more text strings into one.
Syntax:
=CONCATENATE(text1, text2, ...)
or
=CONCAT(text1, text2, ...)
Example: Combine the first name in A1 with the last
name in B1.
=CONCATENATE(A1, " ", B1)
- LEFT Function: The LEFT function extracts a specified
number of characters from the beginning of a text string.
Syntax:
=LEFT(text, num_chars)
Example: Extract the first three characters from the
text in cell A1.
=LEFT(A1, 3)
- RIGHT Function: The RIGHT function extracts a specified
number of characters from the end of a text string.
Syntax:
=RIGHT(text, num_chars)
Example: Extract the last two characters from the text
in cell A1.
=RIGHT(A1, 2)
- MID Function: The MID function extracts a specific
number of characters from the middle of a text string.
Syntax:
=MID(text, start_num, num_chars)
Example: Extract 4 characters from the text in cell A1
starting from the second character.
=MID(A1, 2, 4)
Exercise:
- Task 1: Logical Functions (IF, AND, OR)
- Use the IF function to determine if a
student passed or failed based on their score (>= 50).
- Use the AND function to check if a
student scored above 50 in both Math and English.
- Use the OR function to check if a student
passed in either Math or English.
- Task 2: Lookup Functions (VLOOKUP, INDEX,
MATCH)
- Use VLOOKUP to find the price of a
product based on its name in a list.
- Use INDEX and MATCH together to look up a
value in a table.
- Task 3: Text Functions (CONCATENATE, LEFT,
RIGHT, MID)
- Combine a person’s first name and last
name into one cell using CONCATENATE.
- Use LEFT to extract the first two
characters from a product code.
- Use MID to extract the middle three
characters from a long text string.
Assignment Example:
Assignment 1: Using
Logical Functions
Task: Determine if an employee gets a bonus based on
performance in two categories. The employee must score 80 or above in both
categories to receive a bonus.
Solution: Use the AND function combined with IF:
=IF(AND(A1>=80, B1>=80), "Bonus", "No Bonus")
This formula checks if
both scores are 80 or higher and returns "Bonus" if true, or "No
Bonus" if false.
Assignment 2:
Lookup Function (VLOOKUP)
Task: Use VLOOKUP to find the price of a product
based on its name from a list of products and prices.
Solution:
=VLOOKUP("Product A", A2:B10, 2, FALSE)
This formula searches
for "Product A" in the first column of the range A2:B10 and returns
the corresponding price from the second column.
Assignment 3: Text
Function (CONCATENATE)
Task: Combine the first name in cell A1 and the last
name in cell B1, separating them with a space.
Solution:
=CONCATENATE(A1, " ", B1)
This formula combines
the values of cells A1 and B1 with a space in between.
Conclusion:
In this lesson, we
covered three advanced categories of functions in Excel: Logical Functions
(IF, AND, OR), Lookup Functions (VLOOKUP, HLOOKUP, INDEX, MATCH), and Text
Functions (CONCATENATE, LEFT, RIGHT, MID). These functions allow you to
perform complex calculations, search for data, and manipulate text in powerful
ways.
