MS Excel-Assignment - All

ASSIGNMENT NO.1 

Instructions: Perform this practical into pc. All data is provided.

ASSIGNMENT NO.2

Instructions: Perform this practical into pc. All data is provided.

                                

ASSIGNMENT NO.3

ASSIGNMENT NO.4

1. Use drop down list to create students field

2. Calculate total (p+c+m) using relative cell reference

3. Calculate percentage using absolute cell reference

4. Calculate %Math, Phy & Chem using mixed cell reference

5. Apply conditional formatting showing marks less than 50.

ASSIGNMENT NO.5

Exercise 1: Relative Cell Reference

Open a new Excel worksheet.

In cell A1, type "Item."

In cells A2 to A6, type the names of different items (e.g., Apple, Banana, Orange, etc.).

In cell B1, type "Quantity."

In cells B2 to B6, enter some quantity values (e.g., 10, 15, 20, etc.).

In cell C1, type "Price per Unit."

In cell C2, enter a price (e.g., 1.25).

In cell D1, type "Total Cost."

In cell D2, create a formula that multiplies the quantity (cell B2) by the price per unit (cell C2). Use a relative cell reference for both B2 and C2 so that you can copy this formula to calculate the total cost for other items.

Exercise 2: Absolute Cell Reference

In the same worksheet from Exercise 1, go to cell E2.

Create a formula that calculates the total cost for the first item (Apple) by multiplying the quantity (cell B2) by the price per unit (cell C2).

Now, use an absolute cell reference to lock the reference to cell C2 so that when you copy this formula to calculate the total cost for other items, the price per unit remains the same.

Exercise 3: Mixed Cell Reference

In the same worksheet from Exercise 1, go to cell F2.

Create a formula that calculates the total cost for the first item (Apple) by multiplying the quantity (cell B2) by the price per unit (cell $C$2).

Use a mixed cell reference to lock the column reference for cell C but keep the row reference relative. This way, when you copy the formula to calculate the total cost for other items, it will always refer to the price per unit in column C, but the row reference will change.

Exercise 4: Mixed Cell Reference in Data Validation

In a new worksheet, create a list of items in column A starting from cell A1 (e.g., Apple, Banana, Orange, etc.).

In cell B1, type "Price per Unit."

In cell B2, enter a price (e.g., 1.25).

Select cell C2 and create a data validation drop-down list using the list of items in column A as a source. Use a mixed cell reference to lock the column reference for cell B but keep the row reference relative. This way, when you copy the data validation to other cells in column C, it always refers to the price per unit in cell B2.

ASSIGNMENT NO.6 LOGICAL FORMULAS

Assignment 1: High-Value Comparisons

1. Formula: =1350=1700 → Result: FALSE
2. Formula: =1350>1700 → Result: FALSE
3. Formula: =1350<=1700 → Result: TRUE
4. Formula: =1700<>1350 → Result: TRUE
5. Formula (with both values as 1700): =1700=1700 → Result: TRUE

---

Assignment 2: Comparing Negative Values

1. Formula: =-300<-150 → Result: TRUE
2. Formula: =-300>-150 → Result: FALSE
3. Formula: =-300=-150 → Result: FALSE
4. Formula: =-150<=-300 → Result: FALSE
5. Formula: =-300<>-150 → Result: TRUE

---

Assignment 3: Fractional Comparisons

1. Formula: =3.5<4.2 → Result: TRUE
2. Formula: =3.5=4.2 → Result: FALSE
3. Formula: =3.5>=4.2 → Result: FALSE
4. Formula (with both values as 4.2): =4.2=4.2 → Result: TRUE
5. Formula: =3.5<>4.2 → Result: TRUE

---

Assignment 4: Price Comparisons

1. Formula: =225<250 → Result: TRUE
2. Formula: =225=250 → Result: FALSE
3. Formula: =225>=250 → Result: FALSE
4. Formula (with both prices as 250): =250=250 → Result: TRUE
5. Formula: =225<>250 → Result: TRUE

---

Assignment 5: Comparing Large and Small Values

1. Formula: =5000>120 → Result: TRUE
2. Formula: =5000<120 → Result: FALSE
3. Formula: =5000=120 → Result: FALSE
4. Formula (with both values as 5000): =5000>=5000 → Result: TRUE
5. Formula: =5000<>120 → Result: TRUE

---

Assignment 6: Small Number Comparisons

1. Formula: =5>3 → Result: TRUE
2. Formula: =5<3 → Result: FALSE
3. Formula: =5=3 → Result: FALSE
4. Formula (with both numbers as 3): =3=3 → Result: TRUE
5. Formula: =5<>3 → Result: TRUE

---

Assignment 7: Age Eligibility

1. Formula: =21>18 → Result: TRUE
2. Formula: =18=21 → Result: FALSE
3. Formula: =21<=18 → Result: FALSE
4. Formula (with both values as 18): =18=18 → Result: TRUE
5. Formula: =18<>21 → Result: TRUE

---

Assignment 8: Inventory Checks

1. Formula: =1500>1300 → Result: TRUE
2. Formula: =1500=1300 → Result: FALSE
3. Formula: =1300<1500 → Result: TRUE
4. Formula (with both values as 1500): =1500<=1500 → Result: TRUE
5. Formula: =1300<>1500 → Result: TRUE

---

Assignment 9: Salary Comparison

1. Formula: =3500<4500 → Result: TRUE
2. Formula: =3500=4500 → Result: FALSE
3. Formula: =4500>=3500 → Result: TRUE
4. Formula (with both values as 4500): =4500=4500 → Result: TRUE
5. Formula: =3500<>4500 → Result: TRUE

---

Assignment 10: Exam Score Comparison

1. Formula: =78<85 → Result: TRUE
2. Formula: =78=85 → Result: FALSE
3. Formula: =85>78 → Result: TRUE
4. Formula (with both values as 85): =85=85 → Result: TRUE
5. Formula: =78<>85 → Result: TRUE