Mastering Excel Basics: A Beginner's Journey to Proficiency Excel Formulae Part 2

Mastering Excel Basics: A Beginner's Journey to Proficiency

Excel Formulae Part 2

SUM Function in

Syntax:

=SUM(number1, [number2], ...)

Arguments:

number1: This is the first number or range of cells you want to add. This argument is required.
[number2], ...: These are additional numbers or ranges of cells that you want to add. These arguments are optional, and you can include up to 255 additional items.

Examples:

Adding a Range of Cells: To sum the values in cells A1 through A5:

=SUM(A1:A5)

Adding Multiple Ranges: To sum the values in cells A1 through A5 and B1 through B5:

=SUM(A1:A5, B1:B5)

Adding Individual Numbers: To sum the numbers 10, 20, and 30:

=SUM(10, 20, 30)

Adding a Combination of Ranges and Numbers: To sum the values in cells A1 through A5 and add 100 to the total:

=SUM(A1:A5, 100)

Steps to Use the SUM Function:

Select the Cell: Click on the cell where you want the sum to appear.
Enter the Formula: Type =SUM( to start the formula.
Select the Range:

If you are summing a range, click and drag to select the range of cells you want to add.
If you are adding individual numbers or multiple ranges, separate each with a comma.

Close the Parenthesis: After entering your numbers or ranges, close the parenthesis ).
Press Enter: Press the Enter key to complete the formula and see the result.

Practical Tips:

Using the AutoSum Button: In , you can quickly insert the SUM function by using the AutoSum button. Select the cell where you want the sum, click on the AutoSum button (Σ) in the Home tab, and will automatically select the range it thinks you want to sum. Press Enter to confirm.
Dealing with Errors:

If your SUM function returns an error, check for issues like non-numeric data in your range, empty cells, or incorrect cell references.
Make sure there are no hidden rows or columns within your selected range that may contain unwanted data.

Using SUM with Filters: When you apply a filter to your data, the SUM function still considers all the values in the specified range, whether visible or hidden. To sum only visible cells, you can use the SUBTOTAL function instead.

Explanation of the MIN Function

The MIN function in is used to find the smallest value in a set of numbers. This function is useful when you need to determine the minimum value in a range of data.

Syntax

MIN(number1, [number2], ...)

number1, number2, ... are the numbers or ranges of numbers from which you want to find the minimum value.
You can include up to 255 numbers or ranges in the function.

Example

Suppose you have a range of numbers in cells A1 to A5: 10, 20, 5, 30, 15. To find the minimum value in this range, you would use the following formula:

=MIN(A1:A5)

The result would be 5, which is the smallest number in the range.

Practical Exercise Questions

Here are some practical exercise questions to help you understand and apply the MIN function:

Exercise 1: Basic Usage of MIN

Task: Enter the following data in cells A1 to A10: 35, 42, 27, 50, 18, 62, 29, 40, 55, 31.
Question: Use the MIN function to find the minimum value in this range.
Expected Formula: =MIN(A1:A10)

Exercise 2: MIN with Multiple Ranges

Task: Enter the following data in cells B1 to B5: 15, 22, 9, 37, 28 and in cells C1 to C5: 14, 33, 19, 8, 25.
Question: Use the MIN function to find the minimum value across both ranges.
Expected Formula: =MIN(B1:B5, C1:C5)

Exercise 3: MIN with Mixed Data

Task: Enter the following data in cells D1 to D7: 12, "Apple", 18, 25, "Orange", 7, 33.
Question: Use the MIN function to find the minimum value, ignoring the text entries.
Expected Formula: =MIN(D1:D7)

Exercise 4: MIN in a Real-World Scenario

Scenario: You are managing a small inventory and have the following stock quantities in cells E1 to E6: 120, 150, 90, 200, 80, 130.
Question: Use the MIN function to find the item with the lowest stock quantity.
Expected Formula: =MIN(E1:E6)

Exercise 5: MIN with Conditional Formatting

Task: Enter the following data in cells F1 to F8: 45, 75, 35, 60, 90, 20, 85, 55.
Question: Use the MIN function to highlight the cell with the minimum value using conditional formatting.
Steps:

Use the formula =F1=MIN($F$1:$F$8) in the conditional formatting rule.
Apply the formatting to highlight the cell with the minimum value.

MAX function in is used to find the largest number in a range of cells. It is a very useful function for quickly identifying the highest value in a set of data.

Syntax

MAX(number1, [number2], ...)

number1, number2, ...: These are the numbers or ranges of numbers for which you want to find the maximum value. You can provide numbers directly, cell references, or ranges of cells.

Example

Suppose you have a list of student scores in cells A1 through A10:

A1: 78

A2: 85

A3: 90

A4: 66

A5: 94

A6: 88

A7: 75

A8: 89

A9: 92

A10: 87

To find the highest score, you would use the formula:

=MAX(A1:A10)

This formula will return 94, as it is the highest value in the range A1

Practical Exercise Questions

Finding the Maximum Sales Figure

You have the following sales figures for a week:

yaml

B2: 1500

B3: 1800

B4: 1350

B5: 1950

B6: 1600

B7: 1750

B8: 2000

Write a formula to find the maximum sales figure.

Comparing Temperatures

You have recorded daily temperatures for two weeks:

C2: 30

C3: 32

C4: 31

C5: 29

C6: 34

C7: 33

C8: 35

C9: 31

C10: 29

C11: 28

C12: 30

C13: 27

C14: 26

C15: 32

Write a formula to find the highest temperature recorded.

Highest Score in a Game

You have scores from a game recorded in cells D2 to D15:

D2: 55

D3: 47

D4: 60

D5: 50

D6: 45

D7: 65

D8: 52

D9: 48

D10: 55

D11: 59

D12: 54

D13: 62

D14: 58

D15: 61

Write a formula to find the highest score.

Maximum Monthly Revenue

A company has recorded their monthly revenues for the first half of the year:

E2: 10000

E3: 12000

E4: 11500

E5: 13000

E6: 14000

E7: 12500

Write a formula to find the month with the highest revenue.

Answers to Practical Exercises

Finding the Maximum Sales Figure

=MAX(B2:B8)

This will return 2000.

Comparing Temperatures

=MAX(C2:C15)

This will return 35.

Highest Score in a Game

=MAX(D2:D15)

This will return 65.

Maximum Monthly Revenue

=MAX(E2:E7)

This will return 14000.

Understanding the COUNT Function

The COUNT function in is used to count the number of cells that contain numbers in a range. It does not count empty cells, text, or logical values.

Syntax:

COUNT(value1, [value2], ...)

value1 (required): The first item, cell reference, or range within which you want to count numbers.
[value2], ... (optional): Additional items, cell references, or ranges within which you want to count numbers, up to a maximum of 255 arguments.

Example: If you have the following numbers in cells A1 to A5:

A1: 10
A2: 20
A3: 30
A4: 40
A5: 50

Using the formula:

=COUNT(A1:A5)

The result will be 5 because there are 5 cells that contain numbers.

Practical Exercise Questions

Exercise 1: Basic Counting

Given the following data in cells A1 to A10:

A1: 5
A2: Text
A3: 15
A4:
A5: 25
A6: TRUE
A7: 35
A8: 45
A9: 55
A10:

Questions:

Write the formula to count the number of cells that contain numbers in the range A1

What is the result of the formula?

Exercise 2: Counting with Multiple Ranges

Given the following data:

Range A1:A5: 10, 20, 30, 40, 50
Range B1:B5: 15, 25, Text, , 45
Range C1:C5: 5, Text, 15, 25,

Questions:

Write the formula to count the number of cells that contain numbers in the range A1

What is the result of the formula?

Exercise 3: Real-World Scenario

You are managing a list of sales data for a small store. The sales data for a week (Monday to Sunday) is recorded in cells B1 to B7, where some days have no sales data recorded yet.

Sales Data:

B1 (Monday): 100
B2 (Tuesday): 200
B3 (Wednesday):
B4 (Thursday): 150
B5 (Friday): 300
B6 (Saturday):
B7 (Sunday): 250

Questions:

Write the formula to count the number of days that have recorded sales.
If the sales data is updated and B3 (Wednesday) gets 180 and B6 (Saturday) gets 210, what will be the new count of days with recorded sales?

Solutions:

Exercise 1:

=COUNT(A1:A10)
The result is 5 (cells A1, A3, A5, A7, and A9 contain numbers).

Exercise 2:

=COUNT(A1:A5, B1:B5, C1:C5)
The result is 9 (5 cells from A1

, 3 cells from B1

, and 1 cell from C1

Exercise 3:

=COUNT(B1:B7)
The new count of days with recorded sales is 7 (all days now have recorded sales).

COUNT A Formula in

The COUNTA function in is used to count the number of cells that are not empty within a specified range. It includes cells that contain numbers, text, logical values, error values, and empty text (""). The formula syntax is:

=COUNTA(value1, [value2], ...)

value1: This is the required argument, which represents the first range or value to be counted.
value2, ...: These are optional arguments, which represent additional ranges or values to be counted.

Practical Exercise Questions

Basic Counting of Non-Empty Cells

Given the following data in column A:

graphql

1 John

2 45

4 #DIV/0!

5 TRUE

7 Hello

Question: Write a formula to count the number of non-empty cells in column A. Answer: =COUNTA(A1:A7)

Counting Non-Empty Cells in Multiple Ranges

Given the following data:

less

A B C

1 John Apple 123

2 Orange 456

3 45 Banana

4 #DIV/0! 789

5 TRUE Pear

Question: Write a formula to count the number of non-empty cells in the ranges A1

and B1

. Answer: =COUNTA(A1:A5, B1:C5)

Counting Non-Empty Cells with Mixed Data Types

Given the following data:

graphql

A B

1 10 Text

2 FALSE 100

3 #N/A 200

4 "" TRUE

5 #VALUE!

Question: Write a formula to count the number of non-empty cells in the range A1

. Answer: =COUNTA(A1:B5)

Counting Non-Empty Cells with Different Ranges

Given the following data:

less

A B C

1 Hello World 10

2 20 30 40

4 FALSE #N/A Text

5 100 200

Question: Write a formula to count the number of non-empty cells in the ranges A1

and C1

. Answer: =COUNTA(A1:A5, C1:C5)

Practice with Creating Your Own Data

Create Your Own Data and Use COUNTA

Create your own data in any range of cells (at least 10 cells) and write a COUNTA formula to count the number of non-empty cells. Verify your result by manually counting the non-empty cells.

Example Data:

mathematica

D E

1 Alpha 123

2 Beta 456

3 Gamma

4 Delta

6 Epsilon 789

Example Formula: =COUNTA(D1:E6)

Excel Formula: AVERAGE

The AVERAGE function in Excel is used to calculate the arithmetic mean of a group of numbers. This function is very useful for quickly finding the central tendency of data sets. The syntax for the AVERAGE function is:

excel

Copy code

AVERAGE(number1, [number2], ...)

Where:

number1 is the first number, cell reference, or range for which you want the average.
[number2], ... are additional numbers, cell references, or ranges for which you want the average.

Practical Exercise Questions

Exercise 1: Calculating Average Sales

Open a new Excel workbook.
Enter the following sales data in column A, starting from cell A1:

Copy code

150

200

250

300

350

400

In cell B1, type the formula to calculate the average sales from the data in column A.

Solution:

excel

Copy code

=AVERAGE(A1:A6)

Exercise 2: Average Test Scores

Create a new Excel sheet.
Enter the names of five students in column A, starting from cell A1.
Enter their respective test scores in column B, starting from cell B1:

Copy code

Alice 85

Bob 90

Charlie 78

David 88

Eve 95

In cell B7, type the formula to calculate the average score of the students.

Solution:

excel

Copy code

=AVERAGE(B1:B5)

Exercise 3: Average Temperature

Open a new Excel sheet.
Enter the daily temperatures for a week in column A, starting from cell A1:

Copy code

In cell B1, type the formula to calculate the average temperature over the week.

Solution:

excel

Copy code

=AVERAGE(A1:A7)

Exercise 4: Average Monthly Expenses

Create a new Excel workbook.
Enter the following monthly expenses in column A, starting from cell A1:

yaml

Copy code

1200

1350

1100

1250

1300

1400

In cell B1, type the formula to calculate the average monthly expenses.

Solution:

excel

Copy code

=AVERAGE(A1:A6)

Exercise 5: Average Grades with Missing Data

Open a new Excel sheet.
Enter the following grades in column A, with some cells left blank to simulate missing data:

scss

Copy code

(blank)

In cell B1, type the formula to calculate the average grade, ignoring the blank cells.

Solution:

excel

Copy code

=AVERAGE(A1:A7)

Tips for Using the AVERAGE Function

Ensure there are no text values in the range, as they can cause errors.
Blank cells in the range are ignored in the calculation of the average.
You can use the AVERAGE function with conditions by combining it with other functions like IF.

The IF function in Excel is one of the most useful and versatile functions for performing logical tests and making decisions based on the outcomes of these tests. Here’s a detailed explanation of the IF function along with some practical exercises.

Excel IF Function

Syntax:

excel

Copy code

=IF(logical_test, value_if_true, value_if_false)

logical_test: This is the condition you want to test. It can be any value or expression that can be evaluated to TRUE or FALSE.
value_if_true: This is the value that will be returned if the logical_test evaluates to TRUE.
value_if_false: This is the value that will be returned if the logical_test evaluates to FALSE.

Example: Suppose you have a score of a student in cell A1. You want to check if the score is passing (greater than or equal to 50). If the score is passing, you want to return "Pass", otherwise "Fail".

excel

Copy code

=IF(A1 >= 50, "Pass", "Fail")

Practical Exercise Questions

Exercise 1: Basic IF Function

Create a list of students’ scores in column A (from A2 to A11).
In column B, write an IF formula to determine if the student has "Passed" or "Failed" based on a passing score of 60.

Example Data:

A2: 45
A3: 78
A4: 62
A5: 59
A6: 85
A7: 90
A8: 30
A9: 70
A10: 55

Formula in B2:

excel

Copy code

=IF(A2 >= 60, "Pass", "Fail")

Drag this formula down from B2 to B10.

Exercise 2: Nested IF Function

Extend the previous exercise by categorizing the scores into "Fail", "Pass", and "Excellent". A score of 60-79 is "Pass", and 80 or above is "Excellent".

Example Data:

A2: 45
A3: 78
A4: 62
A5: 59
A6: 85
A7: 90
A8: 30
A9: 70
A10: 55

Formula in B2:

excel

Copy code

=IF(A2 >= 80, "Excellent", IF(A2 >= 60, "Pass", "Fail"))

Drag this formula down from B2 to B10.

Exercise 3: IF with Text and Numbers

Create a sales sheet with sales figures in column A (from A2 to A11).
In column B, write an IF formula to give a bonus of 10% of the sales if the sales are more than $500, otherwise return 0.

Example Data:

A2: 450
A3: 600
A4: 300
A5: 700
A6: 500
A7: 1000
A8: 800
A9: 200
A10: 750

Formula in B2:

excel

Copy code

=IF(A2 > 500, A2 * 0.1, 0)

Drag this formula down from B2 to B10.

Exercise 4: Combining IF with AND/OR

Assume you have two test scores in columns A and B for each student (A2

and B2

In column C, write an IF formula to check if a student has passed both tests (pass mark is 50). Return "Pass" if both scores are 50 or above, otherwise "Fail".

Example Data:

A2: 45, B2: 55
A3: 78, B3: 65
A4: 62, B4: 70
A5: 59, B5: 50
A6: 85, B6: 95
A7: 90, B7: 80
A8: 30, B8: 40
A9: 70, B9: 75
A10: 55, B10: 60

Formula in C2:

excel

Copy code

=IF(AND(A2 >= 50, B2 >= 50), "Pass", "Fail")

Drag this formula down from C2 to C10.

Tips for Using IF Function

Logical Operators: You can use logical operators like >, <, >=, <=, =, and <> in the logical_test argument.
Nested IFs: You can nest multiple IF functions to handle more than two outcomes.
Text and Numbers: The IF function can handle both text and numbers as outputs for value_if_true and value_if_false.

Excel Formulae Details:

SUMIF: This formula adds the cells specified by a given condition or criteria.

Syntax: SUMIF(range, criteria, [sum_range])
Example: =SUMIF(A1:A10, ">5", B1:B10) - This sums all cells in B1

where the corresponding cell in A1

is greater than 5.

SUMIF Formula in Excel

Purpose: The SUMIF function adds all numbers in a range of cells, based on a given condition.

Syntax:

excel

Copy code

SUMIF(range, criteria, [sum_range])

range: The range of cells that you want to apply the criteria to.
criteria: The condition that must be met.
sum_range (optional): The actual cells to sum. If omitted, Excel sums the cells in range.

Practical Exercise Questions

Exercise Data

Create the following table in an Excel worksheet:

Product	Quantity	Price_per_Unit	Total_Cost
Apples	10	2.0	20.0
Oranges	5	3.0	15.0
Bananas	8	1.5	12.0
Apples	6	2.5	15.0
Oranges	7	3.0	21.0
Bananas	9	1.8	16.2
Apples	15	2.2	33.0
Oranges	12	3.2	38.4
Bananas	3	1.6	4.8

Questions

Sum of Total Cost for Apples

Write a SUMIF formula to find the total cost of all Apples.

excel

Copy code

=SUMIF(A2:A10, "Apples", D2:D10)

Sum of Quantities for Oranges

Write a SUMIF formula to find the total quantity of Oranges.

excel

Copy code

=SUMIF(A2:A10, "Oranges", B2:B10)

Sum of Total Cost for Bananas

Write a SUMIF formula to find the total cost of all Bananas.

excel

Copy code

=SUMIF(A2:A10, "Bananas", D2:D10)

Sum of Total Cost for Products with a Quantity Greater than 10

Write a SUMIF formula to find the total cost of all products where the quantity is greater than 10.

excel

Copy code

=SUMIF(B2:B10, ">10", D2:D10)

AVERAGEIF: This formula calculates the average of the cells specified by a given condition or criteria.

Syntax: AVERAGEIF(range, criteria, [average_range])
Example: =AVERAGEIF(A1:A10, ">=10", B1:B10) - This calculates the average of cells in B1

where the corresponding cell in A1

is greater than or equal to 10.

Definition: The AVERAGEIF function in Excel calculates the average of the cells that meet a specified condition or criteria.

Syntax:

scss

Copy code

AVERAGEIF(range, criteria, [average_range])

range: The range of cells that you want to apply the criteria to.
criteria: The condition that you want to apply to the cells in the range.
average_range (optional): The actual cells to average. If omitted, Excel averages the cells in the range.

Example: Suppose you have a list of student scores and you want to calculate the average score of students who scored above 70.

Practical Exercise

Data: Let's create a sample data set for student scores.

Student	Score
Alice	85
Bob	67
Carol	74
Dave	58
Eve	92
Frank	78
Grace	81
Helen	73
Ian	65
John	89

Exercise Questions:

Calculate the average score of students who scored 70 or above.
Calculate the average score of students whose names start with the letter "A".

Solutions:

Average score of students who scored 70 or above:

Formula: =AVERAGEIF(B2:B11, ">=70")

Explanation: The range is B2

(the scores), and the criteria are ">=70" (scores greater than or equal to 70).

Average score of students whose names start with the letter "A":

Formula: =AVERAGEIF(A2:A11, "A*", B2:B11)

Explanation: The range is A2

(the names), the criteria are "A*" (names starting with "A"), and the average_range is B2

(the scores).

Practical Steps in Excel:

Open Excel:

Enter the provided data in columns A and B.

Enter the Formulas:

In a new cell, enter the formula for the first exercise: =AVERAGEIF(B2:B11, ">=70")
In another new cell, enter the formula for the second exercise: =AVERAGEIF(A2:A11, "A*", B2:B11)

Check Results:

After entering the formulas, Excel will calculate the average based on the specified criteria.

Expected Results:

For the first exercise: The average score of students who scored 70 or above should be approximately 82.625.
For the second exercise: Since only "Alice" has a name starting with "A" and her score is 85, the average should be 85.

Additional Practice:

Calculate the average score of students who scored below 70.
Calculate the average score of students whose names start with the letter "B".
Calculate the average score of students whose scores are between 60 and 80.

COUNTIF: This formula counts the number of cells that meet a condition or criteria.

Syntax: COUNTIF(range, criteria)
Example: =COUNTIF(A1:A10, "Apples") - This counts the number of cells in A1

that contain the word "Apples".

COUNTIF is a very useful function in Microsoft Excel that helps in counting the number of cells within a range that meet a single condition. Here’s a detailed explanation and some practical exercises to help you understand and use COUNTIF effectively.

Explanation of COUNTIF

Syntax:

excel

Copy code

COUNTIF(range, criteria)

range: The range of cells that you want to apply the criteria to.
criteria: The condition that a cell in the range must meet to be counted.

Practical Exercise Questions

Let's set up a practical scenario to use COUNTIF.

Scenario:

You are managing a small store, and you want to keep track of the inventory and sales. You have the following data in an Excel sheet.

Product ID	Product Name	Category	Price	Quantity Sold
101	Apple	Fruit	0.50	150
102	Banana	Fruit	0.30	200
103	Carrot	Vegetable	0.20	120
104	Lettuce	Vegetable	1.00	80
105	Orange	Fruit	0.60	180
106	Potato	Vegetable	0.40	160
107	Broccoli	Vegetable	1.20	90
108	Strawberry	Fruit	1.50	75
109	Tomato	Vegetable	0.80	130
110	Grapes	Fruit	2.00	110

Exercise 1: Count the Number of Fruit Products

Task: Use the COUNTIF function to count how many products belong to the 'Fruit' category.

Steps:

Select the cell where you want to display the result (e.g., G2).
Enter the following formula:

excel

Copy code

=COUNTIF(C2:C11, "Fruit")

Press Enter.

Solution: The formula will count the number of cells in the range C2:C11 that contain the word "Fruit".

Exercise 2: Count the Products with Quantity Sold Greater than 100

Task: Use the COUNTIF function to count how many products have a quantity sold greater than 100.

Steps:

Select the cell where you want to display the result (e.g., G3).
Enter the following formula:

excel

Copy code

=COUNTIF(E2:E11, ">100")

Press Enter.

Solution: The formula will count the number of cells in the range E2:E11 that have a value greater than 100.

Exercise 3: Count the Number of Products with Price less than 1.00

Task: Use the COUNTIF function to count how many products have a price less than 1.00.

Steps:

Select the cell where you want to display the result (e.g., G4).
Enter the following formula:

excel

Copy code

=COUNTIF(D2:D11, "<1.00")

Press Enter.

Solution: The formula will count the number of cells in the range D2:D11 that have a value less than 1.00.

Example Excel Data and Solutions

Here’s how your Excel sheet might look with the formulas and results:

plaintext

Copy code

|------------|---------------|----------|-------|---------------|--------------|---------------------|--------------|

| 101 | Apple | Fruit | 0.50 | 150 | | | |

| 102 | Banana | Fruit | 0.30 | 200 | | | |

| 103 | Carrot | Vegetable| 0.20 | 120 | | | |

| 104 | Lettuce | Vegetable| 1.00 | 80 | | | |

| 105 | Orange | Fruit | 0.60 | 180 | | | |

| 106 | Potato | Vegetable| 0.40 | 160 | | | |

| 107 | Broccoli | Vegetable| 1.20 | 90 | | | |

| 108 | Strawberry | Fruit | 1.50 | 75 | | | |

| 109 | Tomato | Vegetable| 0.80 | 130 | | | |

| 110 | Grapes | Fruit | 2.00 | 110 | | | |

| | | | | | 5 | 8 | 6 |

Explanation of the Results

Fruits Count:

The formula =COUNTIF(C2:C11, "Fruit") counts the cells in the range C2:C11 that contain "Fruit".
The result is 5 because there are 5 products listed as "Fruit".

Quantity Sold > 100:

The formula =COUNTIF(E2:E11, ">100") counts the cells in the range E2:E11 that have a value greater than 100.
The result is 8 because there are 8 products with quantities sold greater than 100.

Price < 1.00:

The formula =COUNTIF(D2:D11, "<1.00") counts the cells in the range D2:D11 that have a value less than 1.00.
The result is 6 because there are 6 products with prices less than 1.00.

Additional Tips

Using Cell References for Criteria: You can use cell references instead of hardcoding the criteria. For example, if you have "Fruit" in cell G1, you can write the formula as:

excel

Copy code

=COUNTIF(C2:C11, G1)

Combining COUNTIF with Other Functions: You can combine COUNTIF with other functions like SUM, AVERAGE, etc., for more complex calculations.
Using Wildcards in COUNTIF: You can use wildcards to count cells that match a pattern. For example, =COUNTIF(C2:C11, "F*") counts all cells starting with the letter "F".

Understanding the SUMIFS Function in Excel

The SUMIFS function in Excel is used to sum up the values in a range that meet multiple criteria. It is an extension of the SUMIF function, which sums values based on a single criterion. The SUMIFS function allows you to specify more than one criterion.

Syntax

excel

Copy code

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

sum_range: The range of cells to sum.
criteria_range1: The range of cells that you want to apply the criteria to.
criteria1: The condition that must be met in the criteria_range1.
criteria_range2, criteria2, ...: Additional ranges and their respective conditions (optional).

Example

Let's say you have a sales data table with columns for Date, Product, Region, and Sales Amount. You want to find the total sales for a specific product in a specific region.

Practical Exercise Questions

Exercise 1: Basic SUMIFS Usage

Objective: Calculate the total sales for the product "Laptop" in the "North" region.
Data: Use the following table as your data source.

Date	Product	Region	Sales Amount
01-Jan-2024	Laptop	North	1500
02-Jan-2024	Laptop	South	1200
03-Jan-2024	Tablet	North	800
04-Jan-2024	Laptop	North	2000
05-Jan-2024	Phone	North	700
06-Jan-2024	Laptop	South	1800
07-Jan-2024	Tablet	North	900
08-Jan-2024	Laptop	North	1700

Solution:

Step 1: Select a cell where you want the result to appear.
Step 2: Enter the formula:

excel

Copy code

=SUMIFS(D2:D9, B2:B9, "Laptop", C2:C9, "North")

Step 3: Press Enter.

This formula sums the Sales Amount for rows where the Product is "Laptop" and the Region is "North".

Exercise 2: Advanced SUMIFS with Date Range

Objective: Calculate the total sales for the product "Tablet" in the "North" region for January 2024.
Data: Use the same table as in Exercise 1.
Solution:

Step 1: Select a cell where you want the result to appear.
Step 2: Enter the formula:

excel

Copy code

=SUMIFS(D2:D9, B2:B9, "Tablet", C2:C9, "North", A2:A9, ">=01-Jan-2024", A2:A9, "<=31-Jan-2024")

Step 3: Press Enter.

This formula sums the Sales Amount for rows where the Product is "Tablet", the Region is "North", and the Date is within January 2024.

Additional Practice Data and Questions

Exercise 3: Sales Analysis by Multiple Criteria

Objective: Calculate the total sales for the product "Phone" in any region except "North".
Data: Add the following rows to the table from Exercise 1.

Date	Product	Region	Sales Amount
09-Jan-2024	Phone	South	1000
10-Jan-2024	Laptop	West	1300
11-Jan-2024	Phone	East	1100

Solution:

Step 1: Select a cell where you want the result to appear.
Step 2: Enter the formula:

excel

Copy code

=SUMIFS(D2:D12, B2:B12, "Phone", C2:C12, "<>North")

Step 3: Press Enter.

This formula sums the Sales Amount for rows where the Product is "Phone" and the Region is not "North".

Sample Data Sheet

Here's a sample data table that you can use for these exercises. Copy this table into an Excel sheet starting from cell A1.

Date	Product	Region	Sales Amount
01-Jan-2024	Laptop	North	1500
02-Jan-2024	Laptop	South	1200
03-Jan-2024	Tablet	North	800
04-Jan-2024	Laptop	North	2000
05-Jan-2024	Phone	North	700
06-Jan-2024	Laptop	South	1800
07-Jan-2024	Tablet	North	900
08-Jan-2024	Laptop	North	1700
09-Jan-2024	Phone	South	1000
10-Jan-2024	Laptop	West	1300
11-Jan-2024	Phone	East	1100

COUNTIFS function in Microsoft Excel. COUNTIFS is used to count cells that meet multiple criteria. It allows you to specify conditions across multiple ranges and count only those cells that meet all the conditions you specify.

Syntax of COUNTIFS

The syntax of COUNTIFS is:

excel

Copy code

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

criteria_range1: The first range to apply the first condition.
criteria1: The condition to apply to criteria_range1.
[criteria_range2, criteria2], ...: Additional ranges and their corresponding conditions (optional).

Practical Exercise

Scenario: You have a dataset of sales transactions in an Excel sheet. Each row represents a transaction with columns for Date, Salesperson, Product, and Quantity. You want to use COUNTIFS to find out how many transactions meet certain criteria.

Data for Exercise:

Date	Salesperson	Product	Quantity
2023-01-01	John	A	10
2023-01-02	Mary	B	15
2023-01-02	John	A	20
2023-01-03	Mary	C	5
2023-01-03	John	B	8
2023-01-03	Mary	A	12

Exercise Questions:

Count transactions by a single criterion:

How many transactions were made by John?

Count transactions using multiple criteria:

How many transactions were made by Mary for product A?

Count transactions using multiple criteria from different columns:

How many transactions were made by John for product A?

Solutions:

Count transactions by a single criterion:

Formula: =COUNTIFS(B2:B7, "John")
Result: 3 (John appears in three rows)

Count transactions using multiple criteria:

Formula: =COUNTIFS(B2:B7, "Mary", C2:C7, "A")
Result: 1 (Mary sold product A in one transaction)

Count transactions using multiple criteria from different columns:

Formula: =COUNTIFS(B2:B7, "John", C2:C7, "A")
Result: 2 (John sold product A in two transactions)

Practical Exercise Instructions:

Open a new Excel worksheet.
Enter the provided data into columns A to D.
Use the COUNTIFS function to answer each exercise question.
Verify your results manually by checking against the dataset.

Additional Tips:

Ensure criteria are entered in the correct format (text in double quotes, numbers as they appear).
Double-check cell references and ranges to avoid errors.
Experiment with different criteria combinations to become comfortable with the COUNTIFS function.

AVERAGEIFS function in Microsoft Excel is used to calculate the average of values in a range that meet multiple criteria. Here's how it works and some practical exercises to help students understand its usage:

AVERAGEIFS Function Overview:

Syntax:

scss

Copy code

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

average_range: This is the range of cells that you want to average.
criteria_range1, criteria_range2, ...: These are the ranges that contain the criteria you want to apply to average_range.
criteria1, criteria2, ...: These are the conditions or criteria in the form of a number, expression, cell reference, or text that define which cells in criteria_range1, criteria_range2, etc., to average.

Practical Exercise:

Let's go through an example and then provide exercises for practice.

Example:

Suppose you have a table of student scores in different subjects:

Student	Math Score	Science Score	English Score
Alice	85	78	92
Bob	70	80	75
Carol	90	85	88
Dave	82	78	80
Eve	75	82	79

Exercise 1: Average Math Scores

Calculate the average math score using AVERAGEIFS.

Solution:

less

Copy code

=AVERAGEIFS(B2:B6, B2:B6, ">=", 70)

This formula calculates the average of math scores (B2:B6) where scores are greater than or equal to 70.

Exercise 2: Average Science Scores for Students with English Score > 80

Calculate the average science score for students who scored more than 80 in English.

Solution:

less

Copy code

=AVERAGEIFS(C2:C6, D2:D6, ">80")

This formula calculates the average of science scores (C2:C6) where the corresponding English scores (D2:D6) are greater than 80.

Exercise 3: Average English Scores for Students named 'Bob'

Calculate the average English score for the student named 'Bob'.

Solution:

less

Copy code

=AVERAGEIFS(E2:E6, A2:A6, "Bob")

This formula calculates the average of English scores (E2:E6) where the student name (A2:A6) is "Bob".

Practical Exercises for Students:

Provide the following exercises for students to practice using AVERAGEIFS with different criteria:

Exercise 4: Average Math Scores for Students with Math Score > 80

Extend the table with additional data for more students if needed.

Exercise 5: Average Science Scores for Students with Math Score between 70 and 85

Ensure students understand how to use multiple criteria in AVERAGEIFS.

Exercise 6: Average English Scores for Students with a Total Score (Math + Science + English) greater than 250

This exercise encourages students to combine different columns in their criteria.

Solution Hints:

Provide solutions or hints for the exercises to guide students through their practice. Ensure they understand how to select the correct ranges and criteria for AVERAGEIFS.

Note:

Please note that this function is available on Office 2019 and for Office 365 users.

MAXIFS function in Excel is used to find the maximum value based on one or more conditions. It was introduced in Excel 2016 and is very useful when you need to find the highest value in a range that meets specific criteria.

Syntax of MAXIFS:

scss

Copy code

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

max_range: The range of cells that you want to find the maximum value from.
criteria_range1, criteria_range2, ...: These are the ranges where you set the conditions.
criteria1, criteria2, ...: These are the conditions or criteria that determine which cells in the criteria_range1, criteria_range2, etc. to include.

Practical Exercise:

Let's go through a practical exercise to understand how MAXIFS works:

Scenario: You have a list of students along with their scores in different subjects. You want to find the highest score in Mathematics for students who are in Grade A.

Data:

Student Name	Grade	Mathematics Score	Science Score
John	A	85	90
Emily	B	92	88
Michael	A	78	85
Sarah	A	90	82
David	C	80	78

Exercise: Find the highest score in Mathematics for students who are in Grade A.

Solution Steps:

Set up the Excel sheet:

Enter the student data into an Excel sheet similar to the above format.

Use MAXIFS function:

In an empty cell, say D2, enter the following formula:

less

Copy code

=MAXIFS(C2:C6, B2:B6, "A")

C2:C6 is the range of Mathematics scores.
B2:B6 is the range of Grades.
"A" is the criteria (Grade A).

Result: Excel will calculate and display the highest Mathematics score among students in Grade A.

Exercise Questions:

Exercise 1: Find the highest score in Science for students who are in Grade B.

Data: Use the same data table provided above.

Solution:

less

Copy code

=MAXIFS(D2:D6, B2:B6, "B")

Exercise 2: Find the highest Mathematics score for students named "John".

Data: Use the same data table provided above.

Solution:

less

Copy code

=MAXIFS(C2:C6, A2:A6, "John")

Exercise 3: Find the highest Science score for students who have scored more than 80 in Mathematics.

Data: Use the same data table provided above.

Solution:

less

Copy code

=MAXIFS(D2:D6, C2:C6, ">80")

Note:

Please note that this function is available on Office 2019 and for Office 365 users.

MINIFS function in Excel is used to find the minimum value in a range that meets multiple criteria. It was introduced in Excel 2019 and Excel for Microsoft 365. Here’s how it works and some practical exercises to reinforce learning:

Syntax of MINIFS Function:

scss

Copy code

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

min_range: The range of cells that you want to find the minimum value for.
criteria_range1, criteria_range2, ...: The ranges that contain the criteria.
criteria1, criteria2, ...: The criteria to be met. These can be numbers, expressions, cell references, or text.

Practical Exercises:

Exercise 1: Finding the Minimum Sales Amount by Region

Given data in Excel:

css

Copy code

| A | B | C |

|--------|-------------|-----------|

| Region | Salesperson | Sale Amount |

| East | John | 500 |

| West | Mary | 700 |

| East | Alice | 600 |

| North | Bob | 400 |

| South | Dave | 550 |

| West | Carol | 800 |

Exercise: Calculate the minimum sale amount in the East region.

Solution:

less

Copy code

=MINIFS(C2:C7, A2:A7, "East")

Output: 500

Exercise: Calculate the minimum sale amount where the region is either East or West.

Solution:

less

Copy code

=MINIFS(C2:C7, A2:A7, "East", A2:A7, "West")

Output: 500 (since the minimum sale amount for East is 500 and for West is 700, 500 is the minimum)

Exercise: Calculate the minimum sale amount where the region is East and the salesperson is Alice.

Solution:

less

Copy code

=MINIFS(C2:C7, A2:A7, "East", B2:B7, "Alice")

Output: 600

These exercises help in understanding how MINIFS operates with different combinations of criteria.

Additional Exercises:

Calculate the minimum temperature in different cities for a specific month.
Find the lowest price for a particular product category from a dataset.
Determine the smallest value in a range based on multiple conditions such as dates and categories.

Practical Tips:

Ensure criteria ranges are correctly aligned with the corresponding criteria.
Use cell references for criteria that might change (e.g., comparing against values in other cells).
Test with different scenarios to solidify understanding.

Excel functions AND and OR, which are essential for logical operations in Excel.

1. AND Function

The AND function in Excel returns TRUE if all of its arguments are TRUE, and FALSE if any of the arguments are FALSE. It can take up to 255 arguments.

Syntax: =AND(logical1, [logical2], ...)

Example: Suppose we have data in columns A and B. We want to check if both conditions are met:

Cell A1 should be greater than 10.
Cell B1 should be less than 20.

In cell C1, you would use the formula:

scss

Copy code

=AND(A1 > 10, B1 < 20)

If both conditions are true, C1 will display TRUE; otherwise, it will display FALSE.

Practical Exercise for AND Function:

Exercise Data:

A	B
15	18
12	22
8	16
11	10

Exercise Questions:

Use the AND function to check if:

Cell A2 is greater than 10 and Cell B2 is less than 20.
Cell A3 is greater than 10 and Cell B3 is less than 20.
Cell A4 is greater than 10 and Cell B4 is less than 20.

Solutions:

For cell C2: =AND(A2 > 10, B2 < 20) → Result: TRUE
For cell C3: =AND(A3 > 10, B3 < 20) → Result: FALSE
For cell C4: =AND(A4 > 10, B4 < 20) → Result: FALSE

2. OR Function

The OR function in Excel returns TRUE if any of its arguments are TRUE, and FALSE if all arguments are FALSE. Like AND, it can also take up to 255 arguments.

Syntax: =OR(logical1, [logical2], ...)

Example: Using the same data from columns A and B:

Cell D1 should display TRUE if either:

Cell A1 is greater than 15, OR
Cell B1 is less than 15.

In cell D1, you would use the formula:

scss

Copy code

=OR(A1 > 15, B1 < 15)

If either condition is true, D1 will display TRUE; otherwise, it will display FALSE.

Practical Exercise for OR Function:

Exercise Data:

A	B
17	12
14	18
8	20
11	10

Exercise Questions:

Use the OR function to check if:

Cell A2 is greater than 15 or Cell B2 is less than 15.
Cell A3 is greater than 15 or Cell B3 is less than 15.
Cell A4 is greater than 15 or Cell B4 is less than 15.

Solutions:

For cell C2: =OR(A2 > 15, B2 < 15) → Result: TRUE
For cell C3: =OR(A3 > 15, B3 < 15) → Result: FALSE
For cell C4: =OR(A4 > 15, B4 < 15) → Result: TRUE

Conclusion

These exercises provide hands-on practice with the AND and OR functions in Excel, helping students understand how to use logical operations to evaluate conditions based on data in different cells.

The ISBLANK function in Excel is used to check if a cell is empty. It returns TRUE if a cell is empty (contains no value), and FALSE if it contains any content (including spaces, text, or formulas).

Syntax:

scss

Copy code

=ISBLANK(value)

value: The cell or range of cells that you want to test. It can be a single cell reference, a range of cells, or an array constant.

Practical Exercise:

Let's create a practical exercise to understand how the ISBLANK function works in Excel.

Sample Data:

Copy the following sample data into your Excel sheet:

A	B	C	D
123		Hello
	456		World

Exercise Questions:

Use the ISBLANK function to check if cells in column A are empty.
Use the ISBLANK function to check if cells in column B are empty.
Use the ISBLANK function to check if cells in column C are empty.
Use the ISBLANK function to check if cells in column D are empty.

Solutions:

Let's provide solutions for each question using the ISBLANK function.

Question 1: Check if cells in column A are empty

rust

Copy code

=ISBLANK(A1) --> Result: FALSE

=ISBLANK(A2) --> Result: TRUE

=ISBLANK(A3) --> Result: TRUE

Question 2: Check if cells in column B are empty

rust

Copy code

=ISBLANK(B1) --> Result: TRUE

=ISBLANK(B2) --> Result: FALSE

=ISBLANK(B3) --> Result: TRUE

Question 3: Check if cells in column C are empty

rust

Copy code

=ISBLANK(C1) --> Result: FALSE

=ISBLANK(C2) --> Result: FALSE

=ISBLANK(C3) --> Result: TRUE

Question 4: Check if cells in column D are empty

rust

Copy code

=ISBLANK(D1) --> Result: TRUE

=ISBLANK(D2) --> Result: FALSE

=ISBLANK(D3) --> Result: FALSE

Instructions:

Open Excel and enter the sample data into cells A1

In cell E1, enter the formula =ISBLANK(A1) and drag it down to E3 to apply the formula to all relevant cells.
Similarly, use cells F1, G1, and H1 for Questions 2, 3, and 4 respectively, and drag the formulas down to see the results.

VLOOKUP Formula in Excel:

The VLOOKUP function in Excel searches for a value in the leftmost column of a table and returns a value in the same row from a column you specify. It's a powerful tool for looking up and retrieving specific data from a table based on matching criteria.

Syntax:

excel

Copy code

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: The value you want to search for.
table_array: The range of cells that contains the data. The first column of this range must contain the value you are searching for.
col_index_num: The column number in the table_array from which to retrieve the matching value.
range_lookup: Optional. This argument can be either TRUE (approximate match) or FALSE (exact match). If omitted, TRUE is the default, which means an approximate match.

Practical Exercise with Sample Data:

Sample Data: Suppose we have the following data in Excel:

Employee ID	Employee Name	Department	Salary
101	John Doe	HR	45000
102	Jane Smith	Marketing	52000
103	Alex Johnson	IT	48000
104	Emily Brown	Finance	50000

Exercise:

Use VLOOKUP to find Employee Name based on Employee ID:

In cell E2, enter the following formula to find the name of the employee with Employee ID 103:

excel

Copy code

=VLOOKUP(103, A2:D5, 2, FALSE)

Explanation:

103 is the lookup value (Employee ID we are searching for).
A2:D5 is the table array (the range where our data is).
2 specifies the column index number (we want to retrieve the Employee Name, which is in the second column of our table array).
FALSE indicates an exact match.

Result: Alex Johnson (the employee name with Employee ID 103).

Use VLOOKUP to find Salary based on Employee Name:

In cell F2, enter the formula to find the salary of John Doe:

excel

Copy code

=VLOOKUP("John Doe", A2:D5, 4, FALSE)

Explanation:

"John Doe" is the lookup value (Employee Name we are searching for).
A2:D5 is the table array.
4 specifies the column index number (Salary is in the fourth column of our table array).
FALSE for an exact match.

Result: 45000 (the salary of John Doe).

Additional Exercise: 3. Challenge Exercise - Using VLOOKUP with Dynamic Ranges:

Expand the table by adding more rows and practice using VLOOKUP with the updated range. Ensure your formula adapts to the expanded data dynamically.

Solution for Challenge Exercise:

Suppose we added two more rows to our data:

Employee ID	Employee Name	Department	Salary
101	John Doe	HR	45000
102	Jane Smith	Marketing	52000
103	Alex Johnson	IT	48000
104	Emily Brown	Finance	50000
105	Michael Clark	Operations	47000
106	Sarah Green	Sales	49000

Update the VLOOKUP formula in E2 to:

excel

Copy code

=VLOOKUP(103, A2:D7, 2, FALSE)

Update the VLOOKUP formula in F2 to:

excel

Copy code

=VLOOKUP("John Doe", A2:D7, 4, FALSE)

By adjusting the range A2:D7, the VLOOKUP function now considers the expanded data range, ensuring accurate results even with additional rows.

The issue with using VLOOKUP to find a name directly from a table where names are not in the first column is that VLOOKUP requires the lookup value to be in the first column of the range specified. If the names are not in the first column, VLOOKUP will not work as expected.

For scenarios where you need to search for a value in a column other than the first one, you can use the INDEX and MATCH functions together.

INDEX and MATCH:

INDEX returns the value of a cell in a table based on the row and column number.
MATCH returns the relative position of an item in an array that matches a specified value.

Syntax:

excel

Copy code

=INDEX(array, row_num, [column_num])

=MATCH(lookup_value, lookup_array, [match_type])

Using INDEX and MATCH Together:

excel

Copy code

=INDEX(column_to_return_value_from, MATCH(lookup_value, column_to_lookup_value_in, 0))

Practical Exercise with Sample Data Using INDEX and MATCH:

Sample Data:

plaintext

Copy code

|-------------|---------------|--------------|--------|

| 101 | John Doe | HR | 45000 |

| 102 | Jane Smith | Marketing | 52000 |

| 103 | Alex Johnson | IT | 48000 |

| 104 | Emily Brown | Finance | 50000 |

| 105 | Michael Clark | Operations | 47000 |

| 106 | Sarah Green | Sales | 49000 |

Exercise:

Find the Salary based on Employee Name using INDEX and MATCH:

In cell F2, enter the following formula to find the salary of John Doe:

excel

Copy code

=INDEX(D2:D7, MATCH("John Doe", B2:B7, 0))

Explanation:

D2:D7 is the array from which we want to return the value (Salary column).
MATCH("John Doe", B2:B7, 0) finds the position of "John Doe" in the range B2:B7 (Employee Name column), with 0 indicating an exact match.

Result: 45000 (the salary of John Doe).

Find the Department based on Employee Name using INDEX and MATCH:

In cell G2, enter the following formula to find the department of Alex Johnson:

excel

Copy code

=INDEX(C2:C7, MATCH("Alex Johnson", B2:B7, 0))

Explanation:

C2:C7 is the array from which we want to return the value (Department column).
MATCH("Alex Johnson", B2:B7, 0) finds the position of "Alex Johnson" in the range B2:B7 (Employee Name column), with 0 indicating an exact match.

Result: IT (the department of Alex Johnson).

Additional Exercise: 3. Challenge Exercise - Using INDEX and MATCH for Different Lookups:

Find the Employee ID based on Employee Name:

excel

Copy code

=INDEX(A2:A7, MATCH("Sarah Green", B2:B7, 0))

Result: 106 (the Employee ID of Sarah Green).

Find the Employee Name based on Department:

excel

Copy code

=INDEX(B2:B7, MATCH("Finance", C2:C7, 0))

Result: Emily Brown (the Employee Name in the Finance department).

By using INDEX and MATCH together, you can perform more flexible lookups in Excel, especially when the lookup value is not in the first column of your data range.

XLOOKUP Formula

Syntax:

css

Copy code

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Arguments:

lookup_value: The value you want to look up.
lookup_array: The range or array to search.
return_array: The range or array that contains the value to return.
[if_not_found]: (Optional) The value to return if no match is found.
[match_mode]: (Optional) The type of match to perform:

0 for exact match (default)
-1 for exact match or next smaller item
1 for exact match or next larger item
2 for wildcard match

[search_mode]: (Optional) The search mode to use:

1 for search from first to last (default)
-1 for search from last to first
2 for binary search ascending order
-2 for binary search descending order

Practical Exercise Questions

Exercise 1: Employee Lookup

Objective: Use the XLOOKUP function to find employee details based on their ID.

Sample Data:

Employee ID	Name	Department	Salary
101	John Doe	HR	60000
102	Jane Smith	IT	75000
103	Emily Davis	Finance	85000
104	Michael Brown	IT	72000
105	Sarah Wilson	HR	63000

Tasks:

Find the name of the employee with ID 103.
Find the department of the employee with ID 104.
Find the salary of the employee with ID 102.
If the employee ID does not exist, return "Not Found".

Solutions:

=XLOOKUP(103, A2:A6, B2:B6, "Not Found") - Returns "Emily Davis"
=XLOOKUP(104, A2:A6, C2:C6, "Not Found") - Returns "IT"
=XLOOKUP(102, A2:A6, D2:D6, "Not Found") - Returns 75000
=XLOOKUP(106, A2:A6, B2:B6, "Not Found") - Returns "Not Found"

Exercise 2: Product Price Lookup

Objective: Use the XLOOKUP function to find product prices based on product codes.

Sample Data:

Product Code	Product Name	Price
P001	Laptop	1200
P002	Smartphone	800
P003	Tablet	600
P004	Monitor	300
P005	Keyboard	50

Tasks:

Find the price of the product with code P003.
Find the name of the product with code P002.
Find the price of the product with code P005.
If the product code does not exist, return "Unavailable".

Solutions:

=XLOOKUP("P003", A2:A6, C2:C6, "Unavailable") - Returns 600
=XLOOKUP("P002", A2:A6, B2:B6, "Unavailable") - Returns "Smartphone"
=XLOOKUP("P005", A2:A6, C2:C6, "Unavailable") - Returns 50
=XLOOKUP("P006", A2:A6, C2:C6, "Unavailable") - Returns "Unavailable"

Sample Data to Copy into Excel

Employee Data

plaintext

Copy code

Employee ID Name Department Salary

101 John Doe HR 60000

102 Jane Smith IT 75000

103 Emily Davis Finance 85000

104 Michael Brown IT 72000

105 Sarah Wilson HR 63000

Product Data

plaintext

Copy code

Product Code Product Name Price

P001 Laptop 1200

P002 Smartphone 800

P003 Tablet 600

P004 Monitor 300

P005 Keyboard 50

Conclusion

The XLOOKUP function is a powerful tool in Excel for finding values in a range or array. It can be used for a variety of lookups and can replace older functions like VLOOKUP and HLOOKUP with more flexibility and additional options.

The XLOOKUP function is available in Excel for Microsoft 365, Excel 2019, and Excel 2021. If you're using an earlier version of Excel, XLOOKUP will not be available. In such cases, you can use alternative functions like VLOOKUP, HLOOKUP, INDEX, and MATCH to achieve similar results.

Here’s how you can achieve the same results using VLOOKUP and INDEX & MATCH in earlier versions of Excel:

Alternative Formulas

Using VLOOKUP

Syntax:

scss

Copy code

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: The value you want to look up.
table_array: The range of cells that contains the data.
col_index_num: The column number in the table_array from which to retrieve the value.
range_lookup: (Optional) TRUE for approximate match, FALSE for exact match.

Practical Exercise Questions with VLOOKUP

Exercise 1: Employee Lookup

Sample Data:

Employee ID	Name	Department	Salary
101	John Doe	HR	60000
102	Jane Smith	IT	75000
103	Emily Davis	Finance	85000
104	Michael Brown	IT	72000
105	Sarah Wilson	HR	63000

Tasks and Solutions:

Find the name of the employee with ID 103: =VLOOKUP(103, A2:D6, 2, FALSE) - Returns "Emily Davis"
Find the department of the employee with ID 104: =VLOOKUP(104, A2:D6, 3, FALSE) - Returns "IT"
Find the salary of the employee with ID 102: =VLOOKUP(102, A2:D6, 4, FALSE) - Returns 75000
If the employee ID does not exist, return "Not Found": =IFERROR(VLOOKUP(106, A2:D6, 2, FALSE), "Not Found") - Returns "Not Found"

Exercise 2: Product Price Lookup

Sample Data:

Product Code	Product Name	Price
P001	Laptop	1200
P002	Smartphone	800
P003	Tablet	600
P004	Monitor	300
P005	Keyboard	50

Tasks and Solutions:

Find the price of the product with code P003: =VLOOKUP("P003", A2:C6, 3, FALSE) - Returns 600
Find the name of the product with code P002: =VLOOKUP("P002", A2:C6, 2, FALSE) - Returns "Smartphone"
Find the price of the product with code P005: =VLOOKUP("P005", A2:C6, 3, FALSE) - Returns 50
If the product code does not exist, return "Unavailable": =IFERROR(VLOOKUP("P006", A2:C6, 3, FALSE), "Unavailable") - Returns "Unavailable"

Using INDEX and MATCH

INDEX Syntax:

scss

Copy code

INDEX(array, row_num, [column_num])

MATCH Syntax:

scss

Copy code

MATCH(lookup_value, lookup_array, [match_type])

Practical Exercise Questions with INDEX and MATCH

Exercise 1: Employee Lookup

Sample Data:

Employee ID	Name	Department	Salary
101	John Doe	HR	60000
102	Jane Smith	IT	75000
103	Emily Davis	Finance	85000
104	Michael Brown	IT	72000
105	Sarah Wilson	HR	63000

Tasks and Solutions:

Find the name of the employee with ID 103: =INDEX(B2:B6, MATCH(103, A2:A6, 0)) - Returns "Emily Davis"
Find the department of the employee with ID 104: =INDEX(C2:C6, MATCH(104, A2:A6, 0)) - Returns "IT"
Find the salary of the employee with ID 102: =INDEX(D2:D6, MATCH(102, A2:A6, 0)) - Returns 75000
If the employee ID does not exist, return "Not Found": =IFERROR(INDEX(B2:B6, MATCH(106, A2:A6, 0)), "Not Found") - Returns "Not Found"

Exercise 2: Product Price Lookup

Sample Data:

Product Code	Product Name	Price
P001	Laptop	1200
P002	Smartphone	800
P003	Tablet	600
P004	Monitor	300
P005	Keyboard	50

Tasks and Solutions:

Find the price of the product with code P003: =INDEX(C2:C6, MATCH("P003", A2:A6, 0)) - Returns 600
Find the name of the product with code P002: =INDEX(B2:B6, MATCH("P002", A2:A6, 0)) - Returns "Smartphone"
Find the price of the product with code P005: =INDEX(C2:C6, MATCH("P005", A2:A6, 0)) - Returns 50
If the product code does not exist, return "Unavailable": =IFERROR(INDEX(C2:C6, MATCH("P006", A2:A6, 0)), "Unavailable") - Returns "Unavailable"

Sample Data to Copy into Excel

Employee Data

plaintext

Copy code

Employee ID Name Department Salary

101 John Doe HR 60000

102 Jane Smith IT 75000

103 Emily Davis Finance 85000

104 Michael Brown IT 72000

105 Sarah Wilson HR 63000

Product Data

plaintext

Copy code

Product Code Product Name Price

P001 Laptop 1200

P002 Smartphone 800

P003 Tablet 600

P004 Monitor 300

P005 Keyboard 50

These alternative formulas will allow you to perform similar lookups in earlier versions of Excel.

I extend my heartfelt gratitude and appreciation to you for thoroughly covering the content of this Post on my blog (https://aklc02.blogspot.com/ ). I trust that you have found it immensely valuable for enhancing your Knowledge skills. I encourage you to explore other Post on this platform, each offering significant insights and knowledge. If you have found this blog beneficial in any way, I kindly request you to show your support by liking and sharing it with others, spreading the wealth of knowledge to a wider audience. Thank you for being a part of this journey and contributing to the growth of our learning community.

Thanks & Regards

[AK Learning Center]

[aklearning0202@gmail.com]