Assignments Of Class 3

Working with Formulas and Functions

Assignment 1: Basic Arithmetic Formulas

Objective: Use basic arithmetic operations (addition, subtraction, multiplication, division) to calculate results in a dataset.
Dataset:

Create a table with columns: Product, Quantity Sold, Price per Unit, and Total Sales.
Populate it with at least 5 products and random data.

Tasks:

Use a formula to calculate the Total Sales for each product (Quantity Sold * Price per Unit).
Add a column called Discount (%) and another called Final Price, applying a discount to each product based on the total sales.

Example Output:

Product	Quantity Sold	Price per Unit	Total Sales	Discount (%)	Final Price
A	10	$20	$200	10%	$180

Assignment 2: Using Common Functions

Objective: Practice using basic functions like SUM, AVERAGE, MIN, MAX, and COUNT.
Dataset:

Create a table with columns: Employee, Sales Amount, Bonus, and Total Compensation.
Fill in at least 10 rows of random data.

Tasks:

Calculate the total sales using the SUM function.
Calculate the average sales per employee using the AVERAGE function.
Find the maximum and minimum sales using the MAX and MIN functions.
Count the total number of employees using the COUNT function.
Use SUM to calculate the Total Compensation (Sales Amount + Bonus).

Assignment 3: Using Cell References (Relative, Absolute, and Mixed)

Objective: Understand and apply different types of cell references: relative, absolute, and mixed.
Dataset:

Create a table with columns: Product, Base Price, Quantity, and Total Cost.

Tasks:

Use relative cell references to calculate the Total Cost for each product (Base Price * Quantity).
In a new column, calculate the Tax Amount (using 8% tax) for each product. Use an absolute cell reference to refer to the tax rate stored in a separate cell.
Calculate the Final Price by adding the Tax Amount to the Total Cost. Use mixed references to ensure the correct calculation when copied to other cells.

Assignment 4: Text Functions (LEFT, RIGHT, MID, CONCAT)

Objective: Use text functions to extract and manipulate data from strings.
Dataset:

Create a list of product codes in one column, each containing 8 characters (e.g., PRT12345).

Tasks:

Use the LEFT function to extract the first 3 characters (the product type).
Use the RIGHT function to extract the last 3 characters (the product number).
Use the MID function to extract the middle two characters from the product code.
Use the CONCAT function to combine the product type, a space, and the product number into a new column.

Example Output:

Product Code	Product Type	Product Number	Combined
PRT12345	PRT	345	PRT 345

Assignment 5: Loan Payment Calculation (PMT Function)

Objective: Calculate the monthly payments for a loan using the PMT function.
Dataset:

Create a table with columns: Loan Amount, Interest Rate, Number of Payments, and Monthly Payment.

Tasks:

Assume the loan amount is $10,000, the interest rate is 5%, and the loan term is 5 years (60 payments). Use the PMT function to calculate the monthly payment.

Change the number of payments to 120 and recalculate the monthly payment.

Create similar rows for at least 3 other loan amounts and interest rates, and calculate the monthly payments.

Example Output:

Loan Amount	Interest Rate	Number of Payments	Monthly Payment
$10,000	5%	60	$188.71
$15,000	6%	120	$166.79

Assignment 6: Logical Functions (IF, AND, OR)

Objective: Use logical functions (IF, AND, OR) to create conditional calculations.
Dataset:

Create a table with columns: Student, Marks, and Result.

Tasks:

Use the IF function to determine if each student has passed (marks >= 50).
Use the AND function to check if a student has passed and scored more than 80 (for merit).
Use the OR function to assign “Special Mention” to students who scored below 50 or above 90.

Example Output:

Student	Marks	Result
John	85	Passed
Jane	45	Special Mention
Sam	92	Special Mention

Assignment 7: Random Data Generation (RANDBETWEEN)

Objective: Use the RANDBETWEEN function to generate random numbers for simulation or sample data.
Dataset:

Create a table with columns: Employee, Sales Target, Actual Sales.

Tasks:

Use RANDBETWEEN to generate random values for the Sales Target between 500 and 1500.
Use RANDBETWEEN to generate random values for Actual Sales between 300 and 1800.
In a new column, calculate whether the employee met their sales target using the IF function (Actual Sales >= Sales Target).

Example Output:

Employee	Sales Target	Actual Sales	Target Met
Alice	1200	1400	Yes
Bob	800	750	No

Assignment 8: Conditional Formatting Based on Functions

Objective: Use conditional formatting to highlight cells based on the result of a formula or function.
Dataset:

Create a table with columns: Employee, Sales, Target, and Achievement %.

Tasks:

Calculate the Achievement % by using the formula: (Sales/Target) * 100.

Apply conditional formatting to highlight cells in the Achievement % column:

Green for values >= 100% (indicating target met or exceeded).
Yellow for values between 80% and 100% (near target).
Red for values < 80% (underperforming).

Example Output:

Employee	Sales	Target	Achievement %
John	1200	1000	120% (Green)
Mary	700	1000	70% (Red)
Alice	850	1000	85% (Yellow)

Assignment 9: Financial Functions (FV, PV, RATE)

Objective: Use financial functions to calculate future value, present value, and rate of investment.
Dataset:

Create a table with columns: Investment, Interest Rate, Periods, and Future Value.

Tasks:

Use the FV function to calculate the future value of an investment of $1,000 over 5 years at an interest rate of 6%.
Use the PV function to calculate the present value of an amount you want to have in 5 years, assuming a future value of $5,000 and an interest rate of 4%.
Use the RATE function to determine the interest rate required to grow an investment of $10,000 to $15,000 in 10 years.

Example Output:

Investment	Interest Rate	Periods	Future Value
$1,000	6%	5	$1,338.23
$5,000	4%	5	$4,451.46

Assignment 10: Nested Functions (IF, AND, OR)

Objective: Use nested functions to handle complex logical conditions.
Dataset:

Create a table with columns: Student, Marks in Math, Marks in Science, Marks in English, and Result.

Tasks:

1.Use a nested IF function to determine the result based on marks in Math, Science, and English:

Pass if marks in all subjects are >= 50.
Merit if marks in all subjects are >= 80.
Fail if any subject has marks < 50.

2.Use a combination of AND and OR functions to check if a student qualifies for an honor roll:

Honor roll for students who either score more than 90 in any two subjects, or an average of 85 in all subjects.

Example Output:

Student	Math	Science	English	Result	Honor Roll
John	85	92	88	Merit	Yes
Jane	45	78	82	Fail	No

Assignment 11: Statistical Functions (MEDIAN, MODE, STDEV)

Objective: Use statistical functions to analyze a dataset.
Dataset:

Create a table with columns: Age, Income, Savings.

Tasks:

Use the MEDIAN function to find the median age of the group.

Use the MODE function to find the most common income value in the dataset.

Use the STDEV function to calculate the standard deviation of savings amounts.

Example Output:

Age	Income	Savings
25	40000	5000
32	45000	8000
25	38000	3000

Assignment 12: Date Functions (TODAY, NETWORKDAYS, EDATE)

Objective: Use date functions to calculate and manipulate dates.
Dataset:

Create a table with columns: Employee, Hire Date, Current Date, and Years of Service.

Tasks:

Use the TODAY function to insert the current date.
Use the NETWORKDAYS function to calculate the number of workdays an employee has been in the company.
Use the EDATE function to calculate an employee's next review date (12 months from the hire date).

Example Output:

Employee	Hire Date	Current Date	Years of Service	Review Date
Alice	01/15/2015	09/17/2024	9	01/15/2025
Bob	06/20/2018	09/17/2024	6	06/20/2025

Assignment 13: Lookup Functions (VLOOKUP, HLOOKUP)

Objective: Use lookup functions to search for specific data within a dataset.
Dataset:

Create a table with columns: Product ID, Product Name, Price, and Stock.

Tasks:

Use the VLOOKUP function to search for a product's price using the Product ID.
Use the HLOOKUP function to find stock levels for products across different regions in a horizontal table format.

Example Output:

Product ID	Product Name	Price	Stock
101	Laptop	$1200	25
102	Mouse	$25	100

Assignment 14: Using the IFERROR Function

Objective: Handle errors gracefully in formulas using the IFERROR function.
Dataset:

Create a table with columns: Division, Number1, Number2, and Result.

Tasks:

Use the IFERROR function to handle any division errors (e.g., dividing by zero).
Ensure that if there's an error, the cell displays "N/A" instead of the error.

Example Output:

Number1	Number2	Result
10	2	5
5	0	N/A

Assignment 15: Text Manipulation with Functions

Objective: Use text functions such as LEN, TRIM, UPPER, and LOWER to manipulate text data.
Dataset:

Create a list of employee names in various formats (e.g., mixed upper and lowercase, extra spaces).

Tasks:

Use the UPPER function to convert all names to uppercase.

Use the LOWER function to convert all names to lowercase.

Use the TRIM function to remove any extra spaces from the names.

5. Use the LEN function to count the number of characters in each name.

Example Output:

Employee Name	UPPER(Name)	LOWER(Name)	Trimmed Name	Name Length
John Doe	JOHN DOE	john doe	John Doe	8
Alice Brown	ALICE BROWN	alice brown	Alice Brown	11

SOLUTIONS

Assignment 1: Basic Arithmetic Formulas

Instructions:

Create a new worksheet:

Open Excel and create a new worksheet.

Enter the dataset:

In cell A1, enter "Product".
In cell B1, enter "Quantity Sold".
In cell C1, enter "Price per Unit".
In cell D1, enter "Total Sales".
In cell E1, enter "Discount (%)".
In cell F1, enter "Final Price".
Fill in the next rows with at least 5 products:

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

| A | 10 | $20 | | 10% | |

| B | 5 | $50 | | 5% | |

| C | 8 | $15 | | 15% | |

| D | 12 | $30 | | 0% | |

| E | 6 | $25 | | 20% | |

Calculate Total Sales:

In cell D2, enter the formula: =B2*C2 to calculate the total sales.
Drag the fill handle down to apply the formula to cells D3 to D6.

Calculate Final Price:

In cell F2, enter the formula: =D2*(1-E2).
Drag the fill handle down to apply to cells F3 to F6.

Example Output:

Product	Quantity Sold	Price per Unit	Total Sales	Discount (%)	Final Price
A	10	$20	$200	10%	$180
B	5	$50	$250	5%	$237.50
C	8	$15	$120	15%	$102
D	12	$30	$360	0%	$360
E	6	$25	$150	20%	$120

Assignment 2: Using Common Functions

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Employee".
In cell B1, enter "Sales Amount".
In cell C1, enter "Bonus".
In cell D1, enter "Total Compensation".
Fill in at least 10 rows with random data:

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

| John | $2000 | $300 | |

| Mary | $1500 | $200 | |

| Alex | $2200 | $350 | |

| Sara | $1800 | $250 | |

| David | $2400 | $400 | |

| Emma | $1900 | $220 | |

| Tom | $2300 | $300 | |

| Lily | $1700 | $250 | |

| Max | $2100 | $300 | |

| Anna | $1600 | $200 | |

Calculate Total Compensation:

In cell D2, enter: =B2+C2.
Drag the fill handle down to apply to D3 to D11.

Calculate Total Sales:

In cell B12, enter: =SUM(B2:B11).

Calculate Average Sales:

In cell B13, enter: =AVERAGE(B2:B11).

Find Maximum and Minimum Sales:

In cell B14, enter: =MAX(B2:B11).
In cell B15, enter: =MIN(B2:B11).

Count Total Employees:

In cell B16, enter: =COUNT(A2:A11).

Example Output:

Employee	Sales Amount	Bonus	Total Compensation
John	$2000	$300	$2300
Mary	$1500	$200	$1700
Alex	$2200	$350	$2550
Sara	$1800	$250	$2050
David	$2400	$400	$2800
Emma	$1900	$220	$2120
Tom	$2300	$300	$2600
Lily	$1700	$250	$1950
Max	$2100	$300	$2400
Anna	$1600	$200	$1800
Total Sales:	$19,000		Total Employees: 10
Average Sales:	$1900		Max Sales: $2400
Min Sales:	$1500

Assignment 3: Using Cell References (Relative, Absolute, and Mixed)

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Product".
In cell B1, enter "Base Price".
In cell C1, enter "Quantity".
In cell D1, enter "Total Cost".
In cell E1, enter "Tax Amount".
In cell F1, enter "Final Price".
Fill in at least 5 products:

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

| A | $100 | 5 | | | |

| B | $200 | 3 | | | |

| C | $150 | 2 | | | |

| D | $250 | 4 | | | |

| E | $300 | 1 | | | |

Calculate Total Cost:

In cell D2, enter: =B2*C2 (relative reference).
Drag the fill handle down to apply to D3 to D6.

Set the Tax Rate:

In cell H1, enter "Tax Rate".
In cell H2, enter 0.08 (this is the 8% tax).

Calculate Tax Amount:

In cell E2, enter: =D2*$H$2 (absolute reference to the tax rate).
Drag the fill handle down to apply to E3 to E6.

Calculate Final Price:

In cell F2, enter: =D2+E2 (mixed reference if needed).
Drag the fill handle down to apply to F3 to F6.

Example Output:

Product	Base Price	Quantity	Total Cost	Tax Amount	Final Price
A	$100	5	$500	$40	$540
B	$200	3	$600	$48	$648
C	$150	2	$300	$24	$324
D	$250	4	$1000	$80	$1080
E	$300	1	$300	$24	$324

Assignment 4: Text Functions (LEFT, RIGHT, MID, CONCAT)

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Product Code".
Fill in the next rows:

| Product Code |

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

| PRT12345 |

| ABC98765 |

| XYZ54321 |

| DEF65432 |

| GHI11122 |

Extract Data Using Text Functions:

In cell B1, enter "Product Type".
In cell C1, enter "Product Number".
In cell D1, enter "Combined".
In cell B2, enter: =LEFT(A2, 3).
In cell C2, enter: =RIGHT(A2, 3).
In cell D2, enter: =MID(A2, 4, 5).
In cell E2, enter: =CONCAT(B2, " ", C2).
Drag down the formulas for rows 3 to 6.

Example Output:

Product Code	Product Type	Product Number	Combined
PRT12345	PRT	345	PRT 345
ABC98765	ABC	765	ABC 765
XYZ54321	XYZ	321	XYZ 321
DEF65432	DEF	432	DEF 432
GHI11122	GHI	122	GHI 122

Assignment 5: Loan Payment Calculation (PMT Function)

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Loan Amount".
In cell B1, enter "Interest Rate".
In cell C1, enter "Number of Payments".
In cell D1, enter "Monthly Payment".
Fill in at least 4 rows with different loan amounts:

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

| $10,000 | 5% | 60 | |

| $15,000 | 6% | 120 | |

| $20,000 | 4.5% | 48 | |

| $25,000 | 3.5% | 36 | |

Calculate Monthly Payment:

In cell D2, enter: =PMT(B2/12, C2, -A2).
Drag the fill handle down to apply to D3 to D5.

Example Output:

Loan Amount	Interest Rate	Number of Payments	Monthly Payment
$10,000	5%	60	$188.71
$15,000	6%	120	$166.79
$20,000	4.5%	48	$470.80
$25,000	3.5%	36	$764.30

Assignment 6: Logical Functions (IF, AND, OR)

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Student".
In cell B1, enter "Marks".
In cell C1, enter "Result".
Fill in the next rows:

| Student | Marks | Result |

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

| John | 85 | |

| Jane | 45 | |

| Sam | 92 | |

| Lily | 70 | |

| Mike | 50 | |

Determine Results:

In cell C2, enter: =IF(B2>=50, "Passed", "Failed").
Drag down to apply to C3 to C6.

Check for Merit:

In cell C2, modify to include AND: =IF(AND(B2>=50, B2>80), "Merit", IF(B2>=50, "Passed", "Failed")).

Assign Special Mention:

Modify cell C2 to include OR: =IF(OR(B2<50, B2>90), "Special Mention", IF(AND(B2>=50, B2>80), "Merit", "Passed")).

Example Output:

Student	Marks	Result
John	85	Passed
Jane	45	Special Mention
Sam	92	Special Mention
Lily	70	Passed
Mike	50	Passed

Assignment 7: Random Data Generation (RANDBETWEEN)

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Employee".
In cell B1, enter "Sales Target".
In cell C1, enter "Actual Sales".
In cell D1, enter "Target Met".
Fill in 5 employee names:

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

| Bob | | | |

| Eva | | | |

Generate Random Data:

In cell B2, enter: =RANDBETWEEN(500, 1500).
In cell C2, enter: =RANDBETWEEN(300, 1800).
In cell D2, enter: =IF(C2>=B2, "Yes", "No").
Drag down the formulas to apply to rows 3 to 6.

Example Output:

Employee	Sales Target	Actual Sales	Target Met
Alice	1200	1400	Yes
Bob	800	750	No
Charlie	900	950	Yes
David	700	600	No
Eva	1500	1600	Yes

Assignment 8: Conditional Formatting Based on Functions

Instructions:

Create a new worksheet:

Open Excel and create a new worksheet.

Enter the dataset:

In cell A1, enter "Employee".
In cell B1, enter "Sales".
In cell C1, enter "Target".
In cell D1, enter "Achievement %".
Fill in the next rows (A2 to D6) with the following data:

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

| John | 1200 | 1000 | |

| Mary | 700 | 1000 | |

| Alice | 850 | 1000 | |

Calculate Achievement %:

In cell D2, enter the formula: =(B2/C2)*100.
Drag the fill handle down to apply the formula to cells D3 to D6.

Apply conditional formatting:

Select the range D2
Go to the Home tab, click on Conditional Formatting, then New Rule.

Choose "Format cells that contain" and set the rules as follows:

For Green: Format cells >= 100% → choose a green fill.
For Yellow: Format cells between 80% and 100% → choose a yellow fill.
For Red: Format cells < 80% → choose a red fill.

Example Output:

Employee	Sales	Target	Achievement %
John	1200	1000	120% (Green)
Mary	700	1000	70% (Red)
Alice	850	1000	85% (Yellow)

Assignment 9: Financial Functions (FV, PV, RATE)

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Investment".
In cell B1, enter "Interest Rate".
In cell C1, enter "Periods".
In cell D1, enter "Future Value".
Fill in the next rows:

| Investment | Interest Rate | Periods |

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

| 1000 | 6% | 5 |

| 5000 | 4% | 5 |

Calculate Future Value using FV function:

In cell D2, enter: =FV(B2,C2,0,-A2).
Drag the fill handle down to apply to D3.

Calculate Present Value using PV function:

In cell D4, enter: =PV(4%,5,0,5000).
D4 will display the present value.

Calculate Rate using RATE function:

In cell D5, enter: =RATE(10,0,-10000,15000).
This will calculate the required interest rate.

Example Output:

Investment	Interest Rate	Periods	Future Value
1000	6%	5	$1,338.23
5000	4%	5	$4,451.46

Assignment 10: Nested Functions (IF, AND, OR)

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Student".
In cell B1, enter "Marks in Math".
In cell C1, enter "Marks in Science".
In cell D1, enter "Marks in English".
In cell E1, enter "Result".
In cell F1, enter "Honor Roll".
Fill in the next rows:

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

| John | 85 | 92 | 88 |

| Jane | 45 | 78 | 82 |

Determine Result:

In cell E2, enter: =IF(AND(B2>=50, C2>=50, D2>=50), "Pass", IF(AND(B2>=80, C2>=80, D2>=80), "Merit", "Fail")).
Drag down to apply to E3.

Determine Honor Roll:

In cell F2, enter: =IF(OR(AVERAGE(B2:D2)>90, COUNTIF(B2:D2,">90")>=2), "Yes", "No").
Drag down to apply to F3.

Example Output:

Student	Math	Science	English	Result	Honor Roll
John	85	92	88	Merit	Yes
Jane	45	78	82	Fail	No

Assignment 11: Statistical Functions (MEDIAN, MODE, STDEV)

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Age".
In cell B1, enter "Income".
In cell C1, enter "Savings".
Fill in the next rows:

| Age | Income | Savings |

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

| 25 | 40000 | 5000 |

| 32 | 45000 | 8000 |

| 28 | 38000 | 3000 |

Calculate Median Age:

In cell A5, enter: =MEDIAN(A2:A4).

Calculate Mode Income:

In cell B5, enter: =MODE(B2:B4).

Calculate Standard Deviation for Savings:

In cell C5, enter: =STDEV(C2:C4).

Example Output:

Age	Income	Savings
25	40000	5000
32	45000	8000
28	38000	3000
Median Age: 28	Mode Income: 40000	Standard Deviation for Savings: 3565.10

Assignment 12: Date Functions (TODAY, NETWORKDAYS, EDATE)

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Employee".
In cell B1, enter "Hire Date".
In cell C1, enter "Current Date".
In cell D1, enter "Years of Service".
In cell E1, enter "Review Date".
Fill in the next rows:

| Employee | Hire Date |

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

| Alice | 01/15/2015 |

| Bob | 06/20/2018 |

Insert Current Date:

In cell C2, enter: =TODAY().
Drag down to apply to C3.

Calculate Years of Service:

In cell D2, enter: =DATEDIF(B2,C2,"Y").
Drag down to apply to D3.

Calculate Review Date:

In cell E2, enter: =EDATE(B2,12).
Drag down to apply to E3.

Example Output:

Employee	Hire Date	Current Date	Years of Service	Review Date
Alice	01/15/2015	09/17/2024	9	01/15/2026
Bob	06/20/2018	09/17/2024	6	06/20/2025

Assignment 13: Lookup Functions (VLOOKUP, HLOOKUP)

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Product ID".
In cell B1, enter "Product Name".
In cell C1, enter "Price".
In cell D1, enter "Stock".
Fill in the next rows:

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

| 101 | Laptop | $1200 | 25 |

| 102 | Mouse | $25 | 100 |

Create a lookup table:

In a separate area, enter product IDs to search for:

| Search Product ID | Found Product Name | Found Price |

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

| 101 | | |

Use VLOOKUP:

In cell B3, enter: =VLOOKUP(A3,A2:D3,2,FALSE).
In cell C3, enter: =VLOOKUP(A3,A2:D3,3,FALSE).

Example Output:

Search Product ID	Found Product Name	Found Price
101	Laptop	$1200

Assignment 14: Using the IFERROR Function

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Division".
In cell B1, enter "Number1".
In cell C1, enter "Number2".
In cell D1, enter "Result".
Fill in the next rows:

| Division | Number1 | Number2 |

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

| A | 10 | 2 |

| B | 5 | 0 |

Calculate Result with IFERROR:

In cell D2, enter: =IFERROR(B2/C2,"N/A").
Drag down to apply to D3.

Example Output:

Division	Number1	Number2	Result
A	10	2	5
B	5	0	N/A

Assignment 15: Text Manipulation with Functions

Instructions:

Create a new worksheet:

Open a new worksheet.

Enter the dataset:

In cell A1, enter "Employee Name".
Fill in the next rows:

| Employee Name |

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

| John Doe |

| Alice Brown |

Manipulate text using functions:

In cell B1, enter: =UPPER(A2) to convert to uppercase.
In cell C1, enter: =LOWER(A2) to convert to lowercase.
In cell D1, enter: =TRIM(A2) to remove extra spaces.
In cell E1, enter: =LEN(A2) to count characters.

Drag formulas down to apply to subsequent rows.

Example Output:

Employee Name	UPPER(Name)	LOWER(Name)	Trimmed Name	Name Length
John Doe	JOHN DOE	john doe	John Doe	8
Alice Brown	ALICE BROWN	alice brown	Alice Brown	11

************************************************************

SOLUTIONS

Assignment 1: Basic Arithmetic Formulas

Instructions:

1. Create a new worksheet:

o Open Excel and create a new worksheet.

2. Enter the dataset:

o In cell A1, enter "Product".

o In cell B1, enter "Quantity Sold".

o In cell C1, enter "Price per Unit".

o In cell D1, enter "Total Sales".

o In cell E1, enter "Discount (%)".

o In cell F1, enter "Final Price".

o Fill in the next rows with at least 5 products:

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

| A | 10 | $20 | | 10% | |

| B | 5 | $50 | | 5% | |

| C | 8 | $15 | | 15% | |

| D | 12 | $30 | | 0% | |

| E | 6 | $25 | | 20% | |

3. Calculate Total Sales:

o In cell D2, enter the formula: =B2*C2 to calculate the total sales.

o Drag the fill handle down to apply the formula to cells D3 to D6.

4. Calculate Final Price:

o In cell F2, enter the formula: =D2*(1-E2).

o Drag the fill handle down to apply to cells F3 to F6.

Example Output:

Product	Quantity Sold	Price per Unit	Total Sales	Discount (%)	Final Price
A	10	$20	$200	10%	$180
B	5	$50	$250	5%	$237.50
C	8	$15	$120	15%	$102
D	12	$30	$360	0%	$360
E	6	$25	$150	20%	$120

Assignment 2: Using Common Functions

Instructions:

1. Create a new worksheet:

o Open a new worksheet.

2. Enter the dataset:

o In cell A1, enter "Employee".

o In cell B1, enter "Sales Amount".

o In cell C1, enter "Bonus".

o In cell D1, enter "Total Compensation".

o Fill in at least 10 rows with random data:

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

| John | $2000 | $300 | |

| Mary | $1500 | $200 | |

| Alex | $2200 | $350 | |

| Sara | $1800 | $250 | |

| David | $2400 | $400 | |

| Emma | $1900 | $220 | |

| Tom | $2300 | $300 | |

| Lily | $1700 | $250 | |

| Max | $2100 | $300 | |

| Anna | $1600 | $200 | |

3. Calculate Total Compensation:

o In cell D2, enter: =B2+C2.

o Drag the fill handle down to apply to D3 to D11.

4. Calculate Total Sales:

o In cell B12, enter: =SUM(B2:B11).

5. Calculate Average Sales:

o In cell B13, enter: =AVERAGE(B2:B11).

6. Find Maximum and Minimum Sales:

o In cell B14, enter: =MAX(B2:B11).

o In cell B15, enter: =MIN(B2:B11).

7. Count Total Employees:

o In cell B16, enter: =COUNT(A2:A11).

Example Output:

Employee	Sales Amount	Bonus	Total Compensation
John	$2000	$300	$2300
Mary	$1500	$200	$1700
Alex	$2200	$350	$2550
Sara	$1800	$250	$2050
David	$2400	$400	$2800
Emma	$1900	$220	$2120
Tom	$2300	$300	$2600
Lily	$1700	$250	$1950
Max	$2100	$300	$2400
Anna	$1600	$200	$1800
Total Sales:	$19,000		Total Employees: 10
Average Sales:	$1900		Max Sales: $2400
Min Sales:	$1500

Assignment 3: Using Cell References (Relative, Absolute, and Mixed)

Instructions:

1. Create a new worksheet:

o Open a new worksheet.

2. Enter the dataset:

o In cell A1, enter "Product".

o In cell B1, enter "Base Price".

o In cell C1, enter "Quantity".

o In cell D1, enter "Total Cost".

o In cell E1, enter "Tax Amount".

o In cell F1, enter "Final Price".

o Fill in at least 5 products:

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

| A | $100 | 5 | | | |

| B | $200 | 3 | | | |

| C | $150 | 2 | | | |

| D | $250 | 4 | | | |

| E | $300 | 1 | | | |

3. Calculate Total Cost:

o In cell D2, enter: =B2*C2 (relative reference).

o Drag the fill handle down to apply to D3 to D6.

4. Set the Tax Rate:

o In cell H1, enter "Tax Rate".

o In cell H2, enter 0.08 (this is the 8% tax).

5. Calculate Tax Amount:

o In cell E2, enter: =D2*$H$2 (absolute reference to the tax rate).

o Drag the fill handle down to apply to E3 to E6.

6. Calculate Final Price:

o In cell F2, enter: =D2+E2 (mixed reference if needed).

o Drag the fill handle down to apply to F3 to F6.

Example Output:

Product	Base Price	Quantity	Total Cost	Tax Amount	Final Price
A	$100	5	$500	$40	$540
B	$200	3	$600	$48	$648
C	$150	2	$300	$24	$324
D	$250	4	$1000	$80	$1080
E	$300	1	$300	$24	$324

Assignment 4: Text Functions (LEFT, RIGHT, MID, CONCAT)

Instructions:

1. Create a new worksheet:

o Open a new worksheet.

2. Enter the dataset:

o In cell A1, enter "Product Code".

o Fill in the next rows:

| Product Code |

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

| PRT12345 |

| ABC98765 |

| XYZ54321 |

| DEF65432 |

| GHI11122 |

3. Extract Data Using Text Functions:

o In cell B1, enter "Product Type".

o In cell C1, enter "Product Number".

o In cell D1, enter "Combined".

o In cell B2, enter: =LEFT(A2, 3).

o In cell C2, enter: =RIGHT(A2, 3).

o In cell D2, enter: =MID(A2, 4, 5).

o In cell E2, enter: =CONCAT(B2, " ", C2).

o Drag down the formulas for rows 3 to 6.

Example Output:

Product Code	Product Type	Product Number	Combined
PRT12345	PRT	345	PRT 345
ABC98765	ABC	765	ABC 765
XYZ54321	XYZ	321	XYZ 321
DEF65432	DEF	432	DEF 432
GHI11122	GHI	122	GHI 122

Assignment 5: Loan Payment Calculation (PMT Function)

Instructions:

1. Create a new worksheet:

o Open a new worksheet.

2. Enter the dataset:

o In cell A1, enter "Loan Amount".

o In cell B1, enter "Interest Rate".

o In cell C1, enter "Number of Payments".

o In cell D1, enter "Monthly Payment".

o Fill in at least 4 rows with different loan amounts:

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

| $10,000 | 5% | 60 | |

| $15,000 | 6% | 120 | |

| $20,000 | 4.5% | 48 | |

| $25,000 | 3.5% | 36 | |

3. Calculate Monthly Payment:

o In cell D2, enter: =PMT(B2/12, C2, -A2).

o Drag the fill handle down to apply to D3 to D5.

Example Output:

Loan Amount	Interest Rate	Number of Payments	Monthly Payment
$10,000	5%	60	$188.71
$15,000	6%	120	$166.79
$20,000	4.5%	48	$470.80
$25,000	3.5%	36	$764.30

Assignment 6: Logical Functions (IF, AND, OR)

Instructions:

1. Create a new worksheet:

o Open a new worksheet.

2. Enter the dataset:

o In cell A1, enter "Student".

o In cell B1, enter "Marks".

o In cell C1, enter "Result".

o Fill in the next rows:

| Student | Marks | Result |

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

| John | 85 | |

| Jane | 45 | |

| Sam | 92 | |

| Lily | 70 | |

| Mike | 50 | |

3. Determine Results:

o In cell C2, enter: =IF(B2>=50, "Passed", "Failed").

o Drag down to apply to C3 to C6.

4. Check for Merit:

o In cell C2, modify to include AND: =IF(AND(B2>=50, B2>80), "Merit", IF(B2>=50, "Passed", "Failed")).

5. Assign Special Mention:

o Modify cell C2 to include OR: =IF(OR(B2<50, B2>90), "Special Mention", IF(AND(B2>=50, B2>80), "Merit", "Passed")).

Example Output:

Student	Marks	Result
John	85	Passed
Jane	45	Special Mention
Sam	92	Special Mention
Lily	70	Passed
Mike	50	Passed

Assignment 7: Random Data Generation (RANDBETWEEN)

Instructions:

1. Create a new worksheet:

o Open a new worksheet.

2. Enter the dataset:

o In cell A1, enter "Employee".

o In cell B1, enter "Sales Target".

o In cell C1, enter "Actual Sales".

o In cell D1, enter "Target Met".

o Fill in 5 employee names:

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

| Bob | | | |

| Eva | | | |

3. Generate Random Data:

o In cell B2, enter: =RANDBETWEEN(500, 1500).

o In cell C2, enter: =RANDBETWEEN(300, 1800).

o In cell D2, enter: =IF(C2>=B2, "Yes", "No").

o Drag down the formulas to apply to rows 3 to 6.

Example Output:

Employee	Sales Target	Actual Sales	Target Met
Alice	1200	1400	Yes
Bob	800	750	No
Charlie	900	950	Yes
David	700	600	No
Eva	1500	1600	Yes

Assignment 8: Conditional Formatting Based on Functions

Solution Steps:

1. Create the Table:

o Columns: Employee, Sales, Target, Achievement %.

o Sample Data:

| Employee | Sales | Target |

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

| John | 1200 | 1000 |

| Mary | 700 | 1000 |

| Alice | 850 | 1000 |

2. Calculate Achievement %:

o In the Achievement % column (D), use the formula:

= (B2 / C2) * 100

o Drag the formula down for other rows.

3. Apply Conditional Formatting:

o Highlight the Achievement % column.

o Go to Home > Conditional Formatting > New Rule.

o Select Use a formula to determine which cells to format and enter:

§ For Green: =D2>=100

§ For Yellow: =AND(D2<100, D2>=80)

§ For Red: =D2<80

o Set the formatting for each rule accordingly.

Example Output:

Employee	Sales	Target	Achievement %
John	1200	1000	120% (Green)
Mary	700	1000	70% (Red)
Alice	850	1000	85% (Yellow)

Assignment 9: Financial Functions (FV, PV, RATE)

Solution Steps:

1. Create the Table:

o Columns: Investment, Interest Rate, Periods, Future Value.

o Sample Data:

| Investment | Interest Rate | Periods |

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

| $1,000 | 6% | 5 |

| $5,000 | 4% | 5 |

2. Calculate Future Value:

o In the Future Value column (D), use:

§ For $1,000: =FV(B2, C2, 0, -A2)

§ For $5,000: =PV(0, C3, 0, A3)

§ For the rate calculation: =RATE(C4, 0, -A4, B4)

Example Output:

Investment	Interest Rate	Periods	Future Value
$1,000	6%	5	$1,338.23
$5,000	4%	5	$4,451.46

Assignment 10: Nested Functions (IF, AND, OR)

Solution Steps:

1. Create the Table:

o Columns: Student, Marks in Math, Marks in Science, Marks in English, Result, Honor Roll.

o Sample Data:

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

| John | 85 | 92 | 88 |

| Jane | 45 | 78 | 82 |

2. Determine Result:

o In Result column (E):

=IF(AND(B2>=50, C2>=50, D2>=50), IF(AND(B2>=80, C2>=80, D2>=80), "Merit", "Pass"), "Fail")

o For Honor Roll (F):

=IF(OR((B2>=90)+(C2>=90)+(D2>=90) >= 2, AVERAGE(B2:D2)>=85), "Yes", "No")

Example Output:

Student	Math	Science	English	Result	Honor Roll
John	85	92	88	Merit	Yes
Jane	45	78	82	Fail	No

Assignment 11: Statistical Functions (MEDIAN, MODE, STDEV)

Solution Steps:

1. Create the Table:

o Columns: Age, Income, Savings.

o Sample Data:

| Age | Income | Savings |

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

| 25 | 40000 | 5000 |

| 32 | 45000 | 8000 |

| 25 | 38000 | 3000 |

2. Calculate Statistical Measures:

o Median Age:

=MEDIAN(A2:A4)

o Mode Income:

=MODE(B2:B4)

o Standard Deviation of Savings:

=STDEV(C2:C4)

Example Output:

Age	Income	Savings
25	40000	5000
32	45000	8000
25	38000	3000

Statistical Results:

Median Age: 25
Mode Income: 40000
Standard Deviation of Savings: 2500

Assignment 12: Date Functions (TODAY, NETWORKDAYS, EDATE)

Solution Steps:

1. Create the Table:

o Columns: Employee, Hire Date, Current Date, Years of Service, Review Date.

o Sample Data:

| Employee | Hire Date | Current Date |

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

| Alice | 01/15/2015| =TODAY() |

| Bob | 06/20/2018| =TODAY() |

2. Calculate Years of Service:

o In Years of Service (D):

=DATEDIF(B2, C2, "Y")

o In Review Date (E):

=EDATE(B2, 12)

Example Output:

Employee	Hire Date	Current Date	Years of Service	Review Date
Alice	01/15/2015	09/17/2024	9	01/15/2025
Bob	06/20/2018	09/17/2024	6	06/20/2025

Assignment 13: Lookup Functions (VLOOKUP, HLOOKUP)

Solution Steps:

1. Create the Table:

o Columns: Product ID, Product Name, Price, Stock.

o Sample Data:

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

| 101 | Laptop | $1200 | 25 |

| 102 | Mouse | $25 | 100 |

2. Using VLOOKUP:

o To find the price of a product using Product ID:

=VLOOKUP(101, A2:D3, 3, FALSE)

3. Using HLOOKUP:

o For horizontal lookup, set your data accordingly and use:

=HLOOKUP("Price", A1:D2, 2, FALSE)

Example Output:

Product ID	Product Name	Price	Stock
101	Laptop	$1200	25
102	Mouse	$25	100

Assignment 14: Using the IFERROR Function

Solution Steps:

1. Create the Table:

o Columns: Division, Number1, Number2, Result.

o Sample Data:

| Division | Number1 | Number2 |

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

| A | 10 | 2 |

| B | 5 | 0 |

2. Using IFERROR for Division:

o In Result column:

=IFERROR(A2/B2, "N/A")

Example Output:

Division	Number1	Number2	Result
A	10	2	5
B	5	0	N/A

Assignment 15: Text Manipulation with Functions

Solution Steps:

1. Create the Table:

o Columns: Employee Name, UPPER(Name), LOWER(Name), LEN(Name).

o Sample Data:

| Employee Name |

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

| John Doe |

| Jane Smith |

2. Manipulate Text:

o In UPPER(Name):

=UPPER(A2)

o In LOWER(Name):

=LOWER(A2)

o In LEN(Name):

=LEN(A2)

Example Output:

Employee Name	UPPER(Name)	LOWER(Name)	LEN(Name)
John Doe	JOHN DOE	john doe	8
Jane Smith	JANE SMITH	jane smith	10

Assignments Of Class 3:Working with Formulas and Functions

Assignments Of Class 3

Working with Formulas and Functions

SOLUTIONS

Class 3:Working with Formulas and Functions

MSOFFICE101

Contact form

Assignments Of Class 3:Working with Formulas and Functions

Assignments Of Class 3

Working with Formulas and Functions

SOLUTIONS

You Might Like

Contact form