ADVANCED SPREADSHEET TOOLS

COMPREHENSIVE PRACTICE EXERCISE SETS WITH COMPLETE SOLUTIONS

PRACTICE SET 1: International Space Station Operations

Instructions: - Save your Excel file as: RollNo_Name_Set1.xlsx (Example:
“001_Kunal_Shah_Set1”) - Ensure all formulas are properly calculated - Check formatting
requirements are met before submission - Answer any TWO questions - Question 1 and
Question 2

Question 1: Data Validation and Logical Functions - Space Mission Supply Management
Scenario: You are managing supply inventory for the International Space Station (ISS)
Mission Control in Bengaluru.
Data to Enter:

Supply Quantity Price per Unit Total Discount Final

Item Available (₹) Value (%) Value
Oxygen 25 18500 5
Cylinders
Water 15 45000 10
Filtration
Units
Freeze- 30 3500 0
Dried
Food
Packs
Solar 20 125000 5
Panel
Modules
Medical 18 22000 0
Supply
Kits

Tasks:
1. Calculate Total Value = Quantity Available × Price per Unit
2. Calculate Final Value after applying discount percentage
3. Apply Data Validation to Discount column: allow only values 0, 5, 10, 15
 4. Create a drop-down list for Supply Item with 5 more options: “Communication
Devices”, “Thermal Blankets”, “Scientific Instruments”, “Emergency Repair Tools”,
“Backup Batteries”
5. Use IF function to display “High Priority” if Quantity Available ≥ 20, otherwise “Low
Priority” in a new column

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up Your Excel Sheet
Action: Open Microsoft Excel - Click: Start Menu → Excel → Blank Workbook
Step 2: Create Column Headers
Goal: Set up the table structure
The Clicks: - Click on cell A1, type: Supply Item - Press Tab, type in B1: Quantity
Available - Press Tab, type in C1: Price per Unit (₹) - Press Tab, type in D1: Total Value -
Press Tab, type in E1: Discount (%) - Press Tab, type in F1: Final Value - Press Tab, type
in G1: Priority Status
Step 3: Enter the Data
Goal: Input all supply information
The Clicks: - Click on cell A2, type: Oxygen Cylinders - Press Tab, type: 25 - Press Tab,
type: 18500 - Press Tab (skip Total Value for now) - Press Tab, type: 5 - Press Tab twice
(skip Final Value for now)
Repeat for remaining rows: - A3: Water Filtration Units, B3: 15, C3: 45000, E3: 10 - A4:
Freeze-Dried Food Packs, B4: 30, C4: 3500, E4: 0 - A5: Solar Panel Modules, B5: 20, C5:
125000, E5: 5 - A6: Medical Supply Kits, B6: 18, C6: 22000, E6: 0
Step 4: Calculate Total Value
Goal: Multiply Quantity × Price per Unit
The Formula: =B2*C2
The Clicks: - Click on cell D2 - Type: =B2*C2 - Press Enter - Result: 462500
Copy Formula Down: - Click on D2 again - Move cursor to bottom-right corner of cell
(small square) until cursor becomes + (fill handle) - Click and drag down to D6 - Results:
D2: 462500, D3: 675000, D4: 105000, D5: 2500000, D6: 396000
Step 5: Calculate Final Value (After Discount)
Goal: Calculate Final Value = Total Value - (Total Value × Discount%)
The Formula: =D2-(D2*E2/100) or simplified: =D2*(1-E2/100)
The Clicks: - Click on cell F2 - Type: =D2*(1-E2/100) - Press Enter - Result: 439375
Copy Formula Down: - Click on F2 - Drag fill handle down to F6 - Results: F2: 439375, F3:
607500, F4: 105000, F5: 2375000, F6: 396000
Step 6: Apply Data Validation to Discount Column
Goal: Restrict Discount column to accept only 0, 5, 10, 15
The Clicks: - Select the range E2:E6 (click E2, hold Shift, click E6) - Go to Data tab in the
Ribbon - Click on Data Validation button (in Data Tools group) - A dialog box appears
In the Data Validation Dialog: - Under Settings tab: - Allow: Select List from dropdown -
Source: Type: 0,5,10,15 - Click OK
Test it: Click on any cell in E2:E6 - you should see a dropdown arrow with values 0, 5, 10,
15
Step 7: Create Drop-Down List for Supply Item
Goal: Add dropdown options for Supply Item column
The Clicks: - Select the range A2:A6 - Go to Data tab - Click Data Validation
In the Data Validation Dialog: - Allow: Select List - Source: Type: Oxygen
Cylinders,Water Filtration Units,Freeze-Dried Food Packs,Solar Panel
Modules,Medical Supply Kits,Communication Devices,Thermal
Blankets,Scientific Instruments,Emergency Repair Tools,Backup
Batteries - Click OK

Step 8: Add IF Function for Priority Status

Goal: Display “High Priority” if Quantity ≥ 20, else “Low Priority”
The Formula: =IF(B2>=20,"High Priority","Low Priority")
The Clicks: - Click on cell G2 - Type: =IF(B2>=20,"High Priority","Low Priority")
- Press Enter - Result: High Priority (because 25 ≥ 20)
Copy Formula Down: - Click on G2 - Drag fill handle down to G6 - Results: - G2: High
Priority (25 ≥ 20) - G3: Low Priority (15 < 20) - G4: High Priority (30 ≥ 20) - G5: High
Priority (20 ≥ 20) - G6: Low Priority (18 < 20)
Step 9: Final Formatting
Goal: Make the table professional-looking
The Clicks: - Select range A1:G6 - Go to Home tab - In Font group, click Bold (or press
Ctrl+B) for headers (row 1) - In Alignment group, click Center for headers - In Font group,
click Borders dropdown → All Borders
Step 10: Save Your Work
The Clicks: - Press Ctrl+S - File name: Type your RollNo_Name_Set1.xlsx (e.g.,
001_Kunal_Shah_Set1.xlsx) - Click Save

Question 2: VLOOKUP and Text Functions - Satellite Equipment Inventory

Scenario: You work at ISRO Satellite Production Unit in Thiruvananthapuram. Create an
equipment lookup system.
Data to Enter:
Table 1: Equipment Master Database

Equipment Code Equipment Name Manufacturer Price (₹)

SAT001 High-Resolution Camera Raytheon 8500000
SAT002 Solar Array System Boeing 12000000
SAT003 Communication Transponder Airbus 15000000
SAT004 Thermal Control System Lockheed Martin 4500000
SAT005 Attitude Control Thrusters Northrop 6800000
Grumman

Table 2: Purchase Order Entry

Equipment Equipment Manufacture GST

Code Name r Price (₹) (18%) Total Price
SAT002
SAT004
SAT001
SAT005

Tasks:
1. Use VLOOKUP to automatically fill Equipment Name, Manufacturer, and Price based
on Equipment Code
2. Calculate GST Amount = Price × 18%
3. Calculate Total Price = Price + GST
4. Use LEFT function to extract first 3 characters from Equipment Code in a new
column
5. Use CONCAT function to create full description: “Manufacturer - Equipment Name”
in a new column

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up Equipment Master Database
Goal: Create the reference table for VLOOKUP
The Clicks: - In a new sheet (click Sheet2 tab at bottom, or Insert → New Sheet) - A1:
Equipment Code, B1: Equipment Name, C1: Manufacturer, D1: Price (₹) - Enter all 5
equipment records (SAT001 to SAT005) in rows 2-6
Step 2: Set Up Purchase Order Entry Sheet
Goal: Create the main working table
The Clicks: - Go back to Sheet1 - A1: Equipment Code, B1: Equipment Name, C1:
Manufacturer, D1: Price (₹), E1: GST (18%), F1: Total Price, G1: Code Prefix, H1: Full
Description - A2: SAT002, A3: SAT004, A4: SAT001, A5: SAT005
Step 3: Use VLOOKUP to Fill Equipment Name
Goal: Lookup Equipment Name from Master Database based on Equipment Code
The Formula: =VLOOKUP(A2,Sheet2!$A$2:$D$6,2,FALSE)
Formula Breakdown: - A2 = Lookup value (Equipment Code in current row) - Sheet2!
$A$2:$D$6 = Table array (Master Database with absolute reference) - 2 = Column index
(Equipment Name is 2nd column in the table) - FALSE = Exact match
The Clicks: - Click on cell B2 - Type: =VLOOKUP(A2,Sheet2!$A$2:$D$6,2,FALSE) -
Press Enter - Result: Solar Array System
Copy Down: - Drag fill handle from B2 to B5 - Results: Solar Array System, Thermal
Control System, High-Resolution Camera, Attitude Control Thrusters
Step 4: Use VLOOKUP to Fill Manufacturer
Goal: Lookup Manufacturer (3rd column)
The Formula: =VLOOKUP(A2,Sheet2!$A$2:$D$6,3,FALSE)
The Clicks: - Click on C2 - Type: =VLOOKUP(A2,Sheet2!$A$2:$D$6,3,FALSE) - Press
Enter, drag down to C5 - Results: Boeing, Lockheed Martin, Raytheon, Northrop Grumman
Step 5: Use VLOOKUP to Fill Price
Goal: Lookup Price (4th column)
The Formula: =VLOOKUP(A2,Sheet2!$A$2:$D$6,4,FALSE)
The Clicks: - Click on D2 - Type: =VLOOKUP(A2,Sheet2!$A$2:$D$6,4,FALSE) - Press
Enter, drag down to D5 - Results: 12000000, 4500000, 8500000, 6800000
Step 6: Calculate GST Amount (18%)
Goal: Calculate GST = Price × 18%
The Formula: =D2*0.18 or =D2*18/100
The Clicks: - Click on E2 - Type: =D2*0.18 - Press Enter, drag down to E5 - Results:
2160000, 810000, 1530000, 1224000
Step 7: Calculate Total Price
Goal: Total = Price + GST
The Formula: =D2+E2
The Clicks: - Click on F2 - Type: =D2+E2 - Press Enter, drag down to F5 - Results:
14160000, 5310000, 10030000, 8024000
Step 8: Use LEFT Function to Extract Code Prefix
Goal: Extract first 3 characters “SAT” from Equipment Code
The Formula: =LEFT(A2,3)
The Clicks: - Click on G2 - Type: =LEFT(A2,3) - Press Enter, drag down to G5 - Results:
SAT, SAT, SAT, SAT (all show “SAT”)
Step 9: Use CONCAT Function for Full Description
Goal: Combine Manufacturer and Equipment Name
The Formula: =CONCAT(C2," - ",B2) or alternative: =C2&" - "&B2
The Clicks: - Click on H2 - Type: =CONCAT(C2," - ",B2) - Press Enter, drag down to H5
- Results: - Boeing - Solar Array System - Lockheed Martin - Thermal Control System -
Raytheon - High-Resolution Camera - Northrop Grumman - Attitude Control Thrusters
Step 10: Format and Save
The Clicks: - Select A1:H5 → Apply All Borders - Make row 1 Bold and Center-aligned -
For Price columns (D, E, F), select and format as Currency (Home → Number Format →
Currency or Accounting) - Save: Ctrl+S

Question 3: What-If Analysis - Goal Seek (Space Research Funding Loan)

Scenario: You are planning to secure a research grant loan from ISRO Development Bank
for establishing a Mars Research Laboratory in Gandhinagar.
Data to Enter:

Particulars Values
Loan Amount Required ₹2,50,00,00
0
Annual Interest Rate 8.5%
(%)
Particulars Values
Loan Tenure (Years) 15
Monthly Interest Rate
Total Months
Monthly EMI

Tasks:
1. Calculate Monthly Interest Rate = Annual Rate ÷ 12
2. Calculate Total Months = Years × 12
3. Use PMT function to calculate Monthly EMI
4. Use Goal Seek to find what Loan Amount you can afford if maximum EMI is
₹20,00,000 per month (keeping interest rate and tenure same)
5. Create a summary showing:
– Original EMI
– Target EMI (₹20,00,000)
– Revised Loan Amount (after Goal Seek)

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up the Loan Table
Goal: Create the structure
The Clicks: - A1: Particulars, B1: Values - A2: Loan Amount Required, B2: 25000000 - A3:
Annual Interest Rate (%), B3: 8.5 - A4: Loan Tenure (Years), B4: 15 - A5: Monthly Interest
Rate - A6: Total Months - A7: Monthly EMI
Step 2: Calculate Monthly Interest Rate
Goal: Convert annual rate to monthly
The Formula: =B3/12
The Clicks: - Click on B5 - Type: =B3/12 - Press Enter - Result: 0.708333 (or 0.71%
approximately)
Step 3: Calculate Total Months
Goal: Convert years to months
The Formula: =B4*12
The Clicks: - Click on B6 - Type: =B4*12 - Press Enter - Result: 180
Step 4: Calculate Monthly EMI Using PMT Function
Goal: Calculate the monthly loan payment
The Formula: =PMT(B5/100,B6,-B2)
Formula Breakdown: - B5/100 = Monthly interest rate in decimal (0.71% becomes
0.0071) - B6 = Number of months (180) - -B2 = Loan amount (negative because it’s money
borrowed)
The Clicks: - Click on B7 - Type: =PMT(B5/100,B6,-B2) - Press Enter - Result:
Approximately ₹246,109 (may vary slightly)
Important: PMT returns a negative value by default, so we use negative loan amount to get
positive EMI.
Step 5: Use Goal Seek to Find Affordable Loan Amount
Goal: Find what loan amount gives EMI of exactly ₹20,00,000
The Clicks: - Click on cell B7 (the cell containing EMI formula) - Go to Data tab in Ribbon -
Click on What-If Analysis button (in Forecast group) - Select Goal Seek from dropdown
In Goal Seek Dialog Box: - Set cell: B7 (should already be there) - To value: 2000000
(type this - target EMI) - By changing cell: Click on B2 (Loan Amount cell) - Click OK
Result: Excel will calculate and show: - New Loan Amount: Approximately ₹20,39,28,000
(or similar) - Click OK to accept the new value
Step 6: Create Summary Report
Goal: Document the before and after comparison
The Clicks: - In empty area (e.g., D2), create summary:

Amount
Description (₹)
Original EMI 246109
Target EMI 2000000
Revised Loan Amount 203928000

Type this manually: - D2: Description, E2: Amount (₹) - D3: Original EMI, E3: 246109
(type the original EMI you noted before Goal Seek) - D4: Target EMI, E4: 2000000 - D5:
Revised Loan Amount, E5: Type the value from B2 after Goal Seek
Alternative: Before running Goal Seek, copy B7 value to a separate cell to preserve original
EMI.
Step 7: Format and Save
The Clicks: - Select ranges and apply All Borders - Format B2, B7, E3:E5 as Currency -
Save: Ctrl+S
Question 4: Data Tables - Fixed Deposit Investment for Space Technology
Scenario: You want to invest in ISRO Fixed Deposit Scheme to fund future space
technology purchases.
Data to Enter:

Particulars Values
Initial Investment ₹1,00,00
0
Annual Interest Rate (%) 7%
Investment Period 5
(Years)
Future Value

Interest Rate Options to Compare: 6%, 6.5%, 7%, 7.5%, 8%, 8.5%, 9%
Tasks:
1. Use FV function to calculate Future Value
2. Create a One-Variable Data Table showing Future Value for different interest rates
(6% to 9%)
3. Format the data table with proper borders and headings
4. Identify which interest rate gives Future Value above ₹1,40,000
5. Apply conditional formatting to highlight the best interest rate option (highest FV)

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up Investment Details
Goal: Create the base calculation
The Clicks: - A1: Particulars, B1: Values - A2: Initial Investment, B2: 100000 - A3: Annual
Interest Rate (%), B3: 7 - A4: Investment Period (Years), B4: 5 - A5: Future Value
Step 2: Calculate Future Value Using FV Function
Goal: Calculate investment growth
The Formula: =FV(B3/100,B4,0,-B2)
Formula Breakdown: - B3/100 = Interest rate as decimal (7% becomes 0.07) - B4 =
Number of periods (5 years) - 0 = No periodic payments (lump sum investment) - -B2 =
Present value (negative because it’s money invested)
The Clicks: - Click on B5 - Type: =FV(B3/100,B4,0,-B2) - Press Enter - Result:
Approximately ₹1,40,255
Step 3: Set Up Data Table Structure
Goal: Create table for multiple interest rate scenarios
The Clicks: - In D2, type: Interest Rate (%) - In E2, type: Future Value (₹) - In D3:D9,
enter interest rates: 6, 6.5, 7, 7.5, 8, 8.5, 9 - In E2, reference the FV formula: Type =B5
Step 4: Create One-Variable Data Table
Goal: Calculate FV for all interest rate scenarios
The Clicks: - Select the range D2:E9 (includes headers and data) - Go to Data tab - Click
What-If Analysis button - Select Data Table
In Data Table Dialog: - Row input cell: Leave blank - Column input cell: Click on B3
(Annual Interest Rate cell) - Click OK
Result: Column E will populate with Future Values: - 6%: ₹1,33,823 - 6.5%: ₹1,37,009 -
7%: ₹1,40,255 - 7.5%: ₹1,43,563 - 8%: ₹1,46,933 - 8.5%: ₹1,50,366 - 9%: ₹1,53,862
Step 5: Format the Data Table
Goal: Make it professional-looking
The Clicks: - Select D2:E9 - Home tab → Borders → All Borders - Make D2:E2 (headers)
Bold and Center-aligned - Select E3:E9, format as Currency
Step 6: Identify Rates Above ₹1,40,000
Goal: Mark qualifying interest rates
The Clicks: - In F2, type: Above 1,40,000? - In F3, type: =IF(E3>140000,"YES","NO") -
Drag down to F9
Results: - 6%: NO - 6.5%: NO - 7%: YES - 7.5%: YES - 8%: YES - 8.5%: YES - 9%: YES
Step 7: Apply Conditional Formatting to Highlight Best Option
Goal: Automatically highlight highest FV
The Clicks: - Select E3:E9 (Future Value column) - Go to Home tab - Click Conditional
Formatting button - Select Top/Bottom Rules → Top 10 Items - Change “10” to “1” (to
highlight only the top value) - Select formatting (e.g., Green Fill with Dark Green Text) -
Click OK
Result: The highest Future Value (9% rate = ₹1,53,862) will be highlighted in green.
Step 8: Save Your Work
The Clicks: - Press Ctrl+S
PRACTICE SET 2: Antarctic Wildlife Research Station
Instructions: - Save your Excel file as: RollNo_Name_Set2.xlsx - Ensure all formulas are
properly calculated - Answer any TWO questions - Question 5 and Question 6

Question 5: Logical Functions and Data Validation - Medical Supply Inventory

Scenario: You manage the medical supply inventory for Bharti Antarctic Research Station
in Antarctica.
Data to Enter:

Medicine Stock Minimum Reorder Unit Price Stock

Name Quantity Stock Status (₹) Value
Frostbite 45 50 850
Treatment
Cream
Emergency 120 100 450
Thermal
Packs
Altitude 35 40 220
Sickness
Tablets
Surgical 25 30 1200
Suture Kits
Vitamin D 80 50 180
Supplements

Tasks:
1. Calculate Stock Value = Stock Quantity × Unit Price
2. Use IF function to fill Reorder Status: If Stock Quantity < Minimum Stock, display
“Reorder Required”, otherwise “Sufficient Stock”
3. Apply Data Validation to Stock Quantity column: allow whole numbers only,
between 0 and 500
4. Create a drop-down list for Medicine Name with 5 additional options: “Pain Relief
Injections”, “Antibiotic Capsules”, “Cardiac Emergency Kit”, “Oxygen Mask Supplies”,
“Blood Pressure Monitor Accessories”
5. Use AND function to create a new column “Critical Stock”: Show “YES” if Stock
Quantity < Minimum Stock AND Unit Price > 500, otherwise “NO”

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up Headers and Data
The Clicks: - A1: Medicine Name, B1: Stock Quantity, C1: Minimum Stock, D1: Reorder
Status, E1: Unit Price (₹), F1: Stock Value, G1: Critical Stock - Enter all 5 medicine records
in rows 2-6
Step 2: Calculate Stock Value
Goal: Multiply Stock Quantity × Unit Price
The Formula: =B2*E2
The Clicks: - Click on F2 - Type: =B2*E2 - Press Enter - Result: 38,250 (45 × 850)
Copy Down: - Drag fill handle from F2 to F6 - Results: 38250, 54000, 7700, 30000, 14400
Step 3: Use IF Function for Reorder Status
Goal: Check if stock is below minimum
The Formula: =IF(B2<C2,"Reorder Required","Sufficient Stock")
The Clicks: - Click on D2 - Type: =IF(B2<C2,"Reorder Required","Sufficient
Stock") - Press Enter - Result: Reorder Required (45 < 50)

Copy Down: - Drag to D6 - Results: - D2: Reorder Required (45 < 50) - D3: Sufficient Stock
(120 ≥ 100) - D4: Reorder Required (35 < 40) - D5: Reorder Required (25 < 30) - D6:
Sufficient Stock (80 ≥ 50)
Step 4: Apply Data Validation to Stock Quantity
Goal: Restrict input to whole numbers between 0 and 500
The Clicks: - Select B2:B6 - Data tab → Data Validation
In Dialog: - Allow: Whole number - Data: between - Minimum: 0 - Maximum: 500 - Click
OK
Step 5: Create Drop-Down for Medicine Name
Goal: Add product selection dropdown
The Clicks: - Select A2:A6 - Data tab → Data Validation
In Dialog: - Allow: List - Source: Type: Frostbite Treatment Cream,Emergency
Thermal Packs,Altitude Sickness Tablets,Surgical Suture Kits,Vitamin D
Supplements,Pain Relief Injections,Antibiotic Capsules,Cardiac
Emergency Kit,Oxygen Mask Supplies,Blood Pressure Monitor Accessories -
Click OK
Step 6: Use AND Function for Critical Stock
Goal: Identify items that are BOTH low stock AND expensive
The Formula: =IF(AND(B2<C2,E2>500),"YES","NO")
Formula Breakdown: - B2<C2 = Check if below minimum - E2>500 = Check if expensive -
AND = Both conditions must be TRUE - IF = Display YES or NO

The Clicks: - Click on G2 - Type: =IF(AND(B2<C2,E2>500),"YES","NO") - Press Enter -

Result: YES (45 < 50 AND 850 > 500, both TRUE)
Copy Down: - Drag to G6 - Results: - G2: YES (low stock + expensive) - G3: NO (sufficient
stock) - G4: NO (low stock but not expensive: 220 ≤ 500) - G5: YES (low stock + expensive:
1200 > 500) - G6: NO (sufficient stock)
Step 7: Format and Save
The Clicks: - Select A1:G6 → All Borders - Make row 1 Bold and Center-aligned - Format
F2:F6 as Currency - Save: Ctrl+S

Question 6: HLOOKUP and Date Functions - Arctic Wildlife Tracker Performance

Scenario: You work at Arctic Wildlife Conservation Center in Svalbard, Norway. Track field
researcher performance.
Data to Enter:
Table 1: Researcher Master (Horizontal Layout)

Researcher ID R101 R102 R103 R104 R105

Researcher Name Dr. Emma Dr. Vikra Dr. Sofia Dr. James Dr. Priya
Peterson m Singh Martinez Lee Sharma
Daily Allowance (₹) 4500 4800 5200 4500 4900
Years of Experience 5 8 12 4 10

Table 2: March 2026 Field Work Log

Research Researcher Daily Experience Days Total Bonus (if

er ID Name Allowance (Years) Worked Salary Exp ≥ 7)
R103 26
R101 24
R105 28
R102 25

Tasks:
1. Use HLOOKUP to fill Researcher Name, Daily Allowance, and Experience from
master table
2. Calculate Total Salary = Daily Allowance × Days Worked
 3. Calculate Bonus: If Experience ≥ 7 years, give ₹15,000 bonus, otherwise 0 (use IF)
4. Use TODAY function to display current date
5. Use TEXT function to display current date in format “DD-MMM-YYYY” (e.g., “07-
Mar-2026”)

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Create Researcher Master Table (Horizontal)
Goal: Set up lookup reference table
The Clicks: - In Sheet2, create horizontal table: - A1: Researcher ID, B1: R101, C1: R102,
D1: R103, E1: R104, F1: R105 - A2: Researcher Name, B2: Dr. Emma Peterson, C2:
Dr. Vikram Singh, etc. - A3: Daily Allowance (₹), B3: 4500, C3: 4800, D3: 5200, E3: 4500,
F3: 4900 - A4: Years of Experience, B4: 5, C4: 8, D4: 12, E4: 4, F4: 10
Step 2: Set Up Field Work Log (Sheet1)
Goal: Create main working table
The Clicks: - In Sheet1: - Headers in row 1: Researcher ID, Researcher Name, Daily
Allowance, Experience (Years), Days Worked, Total Salary, Bonus - A2: R103, E2: 26 - A3:
R101, E3: 24 - A4: R105, E4: 28 - A5: R102, E5: 25
Step 3: Use HLOOKUP to Fill Researcher Name
Goal: Lookup name from horizontal master table
The Formula: =HLOOKUP(A2,Sheet2!$A$1:$F$4,2,FALSE)
Formula Breakdown: - A2 = Lookup value (Researcher ID) - Sheet2!$A$1:$F$4 = Table
array (horizontal table with absolute reference) - 2 = Row index (Name is in 2nd row) -
FALSE = Exact match

The Clicks: - Click on B2 - Type: =HLOOKUP(A2,Sheet2!$A$1:$F$4,2,FALSE) - Press

Enter - Result: Dr. Sofia Martinez
Copy Down: - Drag to B5 - Results: Dr. Sofia Martinez, Dr. Emma Peterson, Dr. Priya
Sharma, Dr. Vikram Singh
Step 4: Use HLOOKUP to Fill Daily Allowance
Goal: Lookup daily allowance (3rd row)
The Formula: =HLOOKUP(A2,Sheet2!$A$1:$F$4,3,FALSE)
The Clicks: - Click on C2 - Type: =HLOOKUP(A2,Sheet2!$A$1:$F$4,3,FALSE) - Drag to
C5 - Results: 5200, 4500, 4900, 4800
Step 5: Use HLOOKUP to Fill Experience
Goal: Lookup experience (4th row)
The Formula: =HLOOKUP(A2,Sheet2!$A$1:$F$4,4,FALSE)
The Clicks: - Click on D2 - Type: =HLOOKUP(A2,Sheet2!$A$1:$F$4,4,FALSE) - Drag to
D5 - Results: 12, 5, 10, 8
Step 6: Calculate Total Salary
Goal: Daily Allowance × Days Worked
The Formula: =C2*E2
The Clicks: - Click on F2 - Type: =C2*E2 - Drag to F5 - Results: 135200, 108000, 137200,
120000
Step 7: Calculate Bonus Using IF Function
Goal: Give ₹15,000 if experience ≥ 7 years
The Formula: =IF(D2>=7,15000,0)
The Clicks: - Click on G2 - Type: =IF(D2>=7,15000,0) - Drag to G5 - Results: - G2: 15000
(12 ≥ 7) - G3: 0 (5 < 7) - G4: 15000 (10 ≥ 7) - G5: 15000 (8 ≥ 7)
Step 8: Use TODAY Function
Goal: Display current date
The Formula: =TODAY()
The Clicks: - In empty area (e.g., I2), type label: Current Date: - In J2, type: =TODAY() -
Press Enter - Result: 07-03-2026 (or current date in your default format)
Step 9: Use TEXT Function to Format Date
Goal: Display date as “DD-MMM-YYYY”
The Formula: =TEXT(TODAY(),"DD-MMM-YYYY")
The Clicks: - In I3, type: Formatted Date: - In J3, type: =TEXT(TODAY(),"DD-MMM-
YYYY") - Press Enter - Result: 07-Mar-2026

Step 10: Format and Save

The Clicks: - Apply All Borders - Format F2:G5 as Currency - Save: Ctrl+S

Question 7: Goal Seek for Break-Even Analysis - Wildlife Conservation Funding

Scenario: You run Arctic Wolf Conservation Project in Canada. Calculate funding break-
even point.
Data to Enter:

Particulars Values
Fixed Cost per Month (₹) 8,50,000
Variable Cost per Tracking Device 2,500
(₹)
Grant Amount per Device (₹) 4,200
Number of Devices to Deploy 1000
Total Revenue
Total Cost
Profit/Loss

Tasks:
1. Calculate Total Revenue = Number of Devices × Grant Amount per Device
2. Calculate Total Cost = Fixed Cost + (Number of Devices × Variable Cost)
3. Calculate Profit/Loss = Total Revenue - Total Cost
4. Use Goal Seek to find how many devices you need to deploy to break-even (Profit =
0)
5. Create a summary showing:
– Break-even Quantity (after Goal Seek)
– Revenue at Break-even
– Total Cost at Break-even

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up the Business Table
The Clicks: - A1: Particulars, B1: Values - A2: Fixed Cost per Month (₹), B2: 850000 - A3:
Variable Cost per Device (₹), B3: 2500 - A4: Grant Amount per Device (₹), B4: 4200 - A5:
Number of Devices to Deploy, B5: 1000 - A6: Total Revenue - A7: Total Cost - A8:
Profit/Loss
Step 2: Calculate Total Revenue
Goal: Number of Devices × Grant per Device
The Formula: =B5*B4
The Clicks: - Click on B6 - Type: =B5*B4 - Press Enter - Result: 4,200,000 (1000 × 4200)
Step 3: Calculate Total Cost
Goal: Fixed Cost + (Variable Cost × Devices)
The Formula: =B2+(B5*B3)
The Clicks: - Click on B7 - Type: =B2+(B5*B3) - Press Enter - Result: 3,350,000 (850000
+ 2,500,000)
Step 4: Calculate Profit/Loss
Goal: Revenue - Cost
The Formula: =B6-B7
The Clicks: - Click on B8 - Type: =B6-B7 - Press Enter - Result: 850,000 (profit)
Step 5: Use Goal Seek to Find Break-Even Point
Goal: Find number of devices where Profit = 0
The Clicks: - Click on B8 (Profit/Loss cell) - Data tab → What-If Analysis → Goal Seek
In Goal Seek Dialog: - Set cell: B8 (already selected) - To value: 0 (for break-even) - By
changing cell: Click on B5 (Number of Devices) - Click OK
Result: Excel calculates: - Number of Devices: 500 (approximately) - Click OK to accept
Step 6: Create Break-Even Summary
Goal: Document results
The Clicks: - In D2, create summary table:

Description Value
Break-even Quantity 500
Revenue at Break-even 2,100,000
Total Cost at Break- 2,100,000
even

Type: - D2: Description, E2: Value - D3: Break-even Quantity, E3: Copy value from B5
(500) - D4: Revenue at Break-even, E4: Copy value from B6 (2,100,000) - D5: Total Cost at
Break-even, E5: Copy value from B7 (2,100,000)

(500 × 2500) = 2,100,000 - Profit = 2,100,000 - 2,100,000 = 0 ✓

Verification: At 500 devices: - Revenue = 500 × 4200 = 2,100,000 - Total Cost = 850,000 +

Step 7: Format and Save

The Clicks: - Format all monetary values as Currency - Apply Borders - Save: Ctrl+S

Question 8: Scenario Manager - Wildlife Population Analysis

Scenario: You are analyzing monthly population data for Siberian Tiger Conservation
Project in Russia.
Data to Enter:

Particulars Current Situation

Adult Tigers Counted 45
Tiger Cubs Counted 85
Survival Rate - Adults (%) 92
Survival Rate - Cubs (%) 68
Total Estimated
Population
Conservation Budget (₹) 75,00,000
Net Surplus/Deficit

Scenarios to Create:
1. Best Case: Adults = 65, Cubs = 120
2. Worst Case: Adults = 30, Cubs = 50
3. Most Likely: Adults = 52, Cubs = 95
Tasks:
1. Calculate Total Estimated Population = (Adults × Adult Survival %) + (Cubs × Cubs
Survival %)
2. Calculate Net Surplus/Deficit = Total Estimated Population - (Budget ÷ 50000)
(Assuming ₹50,000 required per tiger)
3. Use Scenario Manager to create the three scenarios
4. Generate a Scenario Summary Report
5. Identify which scenario gives maximum estimated population

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up Current Situation Table
The Clicks: - A1: Particulars, B1: Current Situation - A2: Adult Tigers Counted, B2: 45 - A3:
Tiger Cubs Counted, B3: 85 - A4: Survival Rate - Adults (%), B4: 92 - A5: Survival Rate -
Cubs (%), B5: 68 - A6: Total Estimated Population - A7: Conservation Budget (₹), B7:
7500000 - A8: Net Surplus/Deficit
Step 2: Calculate Total Estimated Population
Goal: Apply survival rates to counts
The Formula: =(B2*B4/100)+(B3*B5/100)
The Clicks: - Click on B6 - Type: =(B2*B4/100)+(B3*B5/100) - Press Enter - Result:
99.2 tigers (41.4 adults + 57.8 cubs)
Step 3: Calculate Net Surplus/Deficit
Goal: Check if budget is sufficient
The Formula: =B6-(B7/50000)
Formula Logic: - Budget ÷ 50000 = Number of tigers budget can support - Population -
Budget capacity = Surplus (positive) or Deficit (negative)
The Clicks: - Click on B8 - Type: =B6-(B7/50000) - Press Enter - Result: -50.8 (deficit, as
99.2 - 150 = -50.8)
Step 4: Create Scenarios Using Scenario Manager
Goal: Define three different scenarios
The Clicks: - Data tab → What-If Analysis → Scenario Manager - Click Add button
Scenario 1 - Current Situation: - Scenario name: Current Situation - Changing cells:
Select B2:B3 (Adult and Cub counts) - Click OK - Scenario Values dialog: B2 = 45, B3 = 85
- Click OK
Scenario 2 - Best Case: - In Scenario Manager, click Add - Scenario name: Best Case -
Changing cells: B2:B3 - Click OK - Values: B2 = 65, B3 = 120 - Click OK
Scenario 3 - Worst Case: - Click Add - Scenario name: Worst Case - Changing cells:
B2:B3 - Click OK - Values: B2 = 30, B3 = 50 - Click OK
Scenario 4 - Most Likely: - Click Add - Scenario name: Most Likely - Changing cells:
B2:B3 - Click OK - Values: B2 = 52, B3 = 95 - Click OK
Step 5: Generate Scenario Summary Report
Goal: Create comparison report
The Clicks: - In Scenario Manager dialog, click Summary button - Report type: Select
Scenario summary - Result cells: Select B6:B8 (Population, Budget, Surplus/Deficit) -
Click OK
Result: Excel creates a new sheet named “Scenario Summary” showing:

Current Current
Values Situation Best Case Worst Case Most Likely
Changing
Cells:
Adult Tigers 45 45 65 30 52
Cubs 85 85 120 50 95
Result Cells:
Total 99.2 99.2 141.6 61.6 112.4
Population
 Current Current
Values Situation Best Case Worst Case Most Likely
Budget 7500000 7500000 7500000 7500000 7500000
Surplus/ -50.8 -50.8 -8.4 -88.4 -37.6
Deficit

Step 6: Identify Maximum Population Scenario

Goal: Document the best scenario
The Clicks: - In original sheet or new area, type: - D2: Best Population Scenario - E2: Best
Case - D3: Maximum Population - E3: 141.6 tigers
From Scenario Summary: Best Case gives highest population (141.6 tigers)
Step 7: Format and Save
The Clicks: - Format tables with Borders - Format B7 as Currency - Save: Ctrl+S

PRACTICE SET 3: Deep Ocean Research Expedition

Instructions: - Save your Excel file as: RollNo_Name_Set3.xlsx - Answer any TWO
questions - Question 9 and Question 10

Question 9: Data Validation and Financial Functions - Underwater Equipment Leasing

Scenario: You are managing equipment leasing records for National Institute of Ocean
Technology (NIOT) in Chennai.
Data to Enter:

Research Vessel Lease Lease Period Payment Balance Stat

Name Amount (₹) (Days) Received (₹) Amount us
Samudra 2,80,000 30 1,50,000
Ratnakar
Sagar Kanya 1,95,000 45 1,95,000
Sagar Nidhi 3,50,000 30 2,20,000
Sagar Manjusha 1,75,000 60 95,000
Sagar Purvi 4,10,000 45 4,10,000

Tasks:
1. Calculate Balance Amount = Lease Amount - Payment Received
 2. Use IF function to fill Status: If Balance Amount = 0, display “Paid”, otherwise
“Pending”
3. Apply Data Validation to Lease Period column: allow only 30, 45, 60 days
4. Create a drop-down list for Status with options: “Paid”, “Pending”, “Overdue”
5. Use IFERROR function to handle any error in Balance Amount calculation (display
“Error” if any error occurs)

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up Headers and Data
The Clicks: - A1: Research Vessel Name, B1: Lease Amount (₹), C1: Lease Period (Days),
D1: Payment Received (₹), E1: Balance Amount, F1: Status - Enter all 5 vessel records
Step 2: Calculate Balance Amount
Goal: Subtract payment from lease amount
The Formula: =B2-D2
The Clicks: - Click on E2 - Type: =B2-D2 - Drag to E6 - Results: 130000, 0, 130000, 80000,
0
Step 3: Use IF Function for Status
Goal: Check if fully paid
The Formula: =IF(E2=0,"Paid","Pending")
The Clicks: - Click on F2 - Type: =IF(E2=0,"Paid","Pending") - Drag to F6 - Results:
Pending, Paid, Pending, Pending, Paid
Step 4: Apply Data Validation to Lease Period
Goal: Restrict to 30, 45, or 60 days only
The Clicks: - Select C2:C6 - Data tab → Data Validation
In Dialog: - Allow: List - Source: Type: 30,45,60 - Click OK
Step 5: Create Drop-Down for Status
Goal: Allow manual status updates
The Clicks: - Select F2:F6 - Data tab → Data Validation
In Dialog: - Allow: List - Source: Type: Paid,Pending,Overdue - Click OK
Step 6: Use IFERROR Function for Balance Amount
Goal: Handle calculation errors gracefully
The Formula: =IFERROR(B2-D2,"Error")
The Clicks: - Click on E2 - Replace existing formula with: =IFERROR(B2-D2,"Error") -
Drag to E6
Result: Same values (130000, 0, 130000, 80000, 0) but if any cell has error (e.g., text
instead of number), it will display “Error”
Test: - Temporarily change D2 to text “ABC” - E2 will show “Error” - Change back to
number to see calculation work
Step 7: Format and Save
The Clicks: - Select A1:F6 → All Borders - Format B2:E6 as Currency - Make headers Bold
- Save: Ctrl+S

Question 10: INDEX-MATCH and Text Functions - Marine Equipment Catalog

Scenario: You work at Chennai Port Trust Marine Supply Division. Create advanced
equipment search system.
Data to Enter:
Table 1: Equipment Database

Unit Price
Category Equipment Code Equipment Name (₹)
Navigation NAV101 GPS Navigation 285000
System
Safety SAF102 Life Raft - 20 Person 180000
Communicatio COM103 Satellite Phone System 95000
n
Navigation NAV104 Radar System - X-Band 520000
Safety SAF105 Fire Suppression Unit 68000

Table 2: Search Query

Equipment Code to Equipment Categor Unit Price Code Full

Search Name y (₹) Numbers Code
COM103
NAV101
SAF105
NAV104

Tasks:
 1. Use INDEX-MATCH combination to fill Equipment Name based on Equipment Code
2. Use INDEX-MATCH to fill Category and Unit Price
3. Use RIGHT function to extract last 3 characters (numbers) from Equipment Code in
“Code Numbers” column
4. Use TRIM function to remove extra spaces (if any) from Equipment Name
5. Use MID function to extract Category code (first 3 letters) from Equipment Code

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Create Equipment Database
Goal: Set up reference table
The Clicks: - In Sheet2: - A1: Category, B1: Equipment Code, C1: Equipment Name, D1:
Unit Price (₹) - Enter all 5 equipment records
Step 2: Set Up Search Query Sheet
Goal: Create main working table
The Clicks: - In Sheet1: - Headers in row 1 - A2: COM103, A3: NAV101, A4: SAF105, A5:
NAV104
Step 3: Use INDEX-MATCH to Fill Equipment Name
Goal: Look up name using INDEX-MATCH (more flexible than VLOOKUP)
The Formula: =INDEX(Sheet2!$C$2:$C$6,MATCH(A2,Sheet2!$B$2:$B$6,0))
Formula Breakdown: - MATCH(A2,Sheet2!$B$2:$B$6,0) = Find position of A2 in
Equipment Code column - INDEX(Sheet2!$C$2:$C$6,...) = Return value from
Equipment Name column at that position - 0 in MATCH = Exact match
The Clicks: - Click on B2 - Type: =INDEX(Sheet2!$C$2:$C$6,MATCH(A2,Sheet2!
$B$2:$B$6,0)) - Press Enter - Result: Satellite Phone System

Copy Down: - Drag to B5 - Results: Satellite Phone System, GPS Navigation System, Fire
Suppression Unit, Radar System - X-Band
Step 4: Use INDEX-MATCH to Fill Category
Goal: Look up category (column A)
The Formula: =INDEX(Sheet2!$A$2:$A$6,MATCH(A2,Sheet2!$B$2:$B$6,0))
The Clicks: - Click on C2 - Type: =INDEX(Sheet2!$A$2:$A$6,MATCH(A2,Sheet2!
$B$2:$B$6,0)) - Drag to C5 - Results: Communication, Navigation, Safety, Navigation

Step 5: Use INDEX-MATCH to Fill Unit Price

Goal: Look up price (column D)
The Formula: =INDEX(Sheet2!$D$2:$D$6,MATCH(A2,Sheet2!$B$2:$B$6,0))
The Clicks: - Click on D2 - Type: =INDEX(Sheet2!$D$2:$D$6,MATCH(A2,Sheet2!
$B$2:$B$6,0)) - Drag to D5 - Results: 95000, 285000, 68000, 520000

Step 6: Use RIGHT Function to Extract Numbers

Goal: Extract last 3 characters (numbers) from Equipment Code
The Formula: =RIGHT(A2,3)
The Clicks: - Click on E2 - Type: =RIGHT(A2,3) - Drag to E5 - Results: 103, 101, 105, 104
Step 7: Use TRIM Function
Goal: Remove extra spaces from Equipment Name
The Formula: =TRIM(B2)
The Clicks: - Click on F2 (add new column header “Trimmed Name” if needed) - Type:
=TRIM(B2) - Drag to F5

Result: Same text (unless there were extra spaces in original data)
Purpose: TRIM removes leading/trailing spaces and reduces multiple spaces between
words to single space
Step 8: Use MID Function to Extract Category Code
Goal: Extract first 3 letters (NAV, SAF, COM) from Equipment Code
The Formula: =MID(A2,1,3)
Formula Breakdown: - A2 = Text string - 1 = Start at character position 1 - 3 = Extract 3
characters
The Clicks: - Click on G2 (add header “Category Code”) - Type: =MID(A2,1,3) - Drag to G5
- Results: COM, NAV, SAF, NAV
Alternative using LEFT: =LEFT(A2,3) gives same result
Step 9: Format and Save
The Clicks: - Apply All Borders - Format D2:D5 as Currency - Save: Ctrl+S

Question 11: Loan Amortization using PMT - Oceanographic Research Vessel Loan
Scenario: You are purchasing a research submarine for National Centre for Polar and
Ocean Research (NCPOR) in Goa and need a loan from Marine Development Bank.
Data to Enter:
Particulars Values
Submarine Purchase Price 12,00,00,000
(₹)
Annual Interest Rate (%) 9.5%
Loan Tenure (Years) 8
Monthly Interest Rate
Total Months
Monthly EMI
Total Amount Payable
Total Interest Paid

Tasks:
1. Calculate Monthly Interest Rate = Annual Rate ÷ 12
2. Calculate Total Months = Years × 12
3. Use PMT function to calculate Monthly EMI
4. Calculate Total Amount Payable = Monthly EMI × Total Months
5. Calculate Total Interest Paid = Total Amount Payable - Loan Amount
6. Use Goal Seek to find what tenure (in months) is needed if you want to pay
maximum EMI of ₹18,00,000

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up Loan Table
The Clicks: - A1: Particulars, B1: Values - A2: Submarine Purchase Price (₹), B2:
120000000 - A3: Annual Interest Rate (%), B3: 9.5 - A4: Loan Tenure (Years), B4: 8 - A5:
Monthly Interest Rate - A6: Total Months - A7: Monthly EMI - A8: Total Amount Payable -
A9: Total Interest Paid
Step 2: Calculate Monthly Interest Rate
Goal: Convert annual rate to monthly
The Formula: =B3/12
The Clicks: - Click on B5 - Type: =B3/12 - Press Enter - Result: 0.791667 (approximately
0.79%)
Step 3: Calculate Total Months
Goal: Convert years to months
The Formula: =B4*12
The Clicks: - Click on B6 - Type: =B4*12 - Press Enter - Result: 96
Step 4: Calculate Monthly EMI Using PMT
Goal: Calculate monthly payment
The Formula: =PMT(B5/100,B6,-B2)
The Clicks: - Click on B7 - Type: =PMT(B5/100,B6,-B2) - Press Enter - Result:
Approximately ₹15,33,789
Step 5: Calculate Total Amount Payable
Goal: EMI × Number of months
The Formula: =B7*B6
The Clicks: - Click on B8 - Type: =B7*B6 - Press Enter - Result: Approximately
₹14,72,43,744
Step 6: Calculate Total Interest Paid
Goal: Total payment - Loan amount
The Formula: =B8-B2
The Clicks: - Click on B9 - Type: =B8-B2 - Press Enter - Result: Approximately
₹4,72,43,744
This is the total interest paid over 8 years
Step 7: Use Goal Seek to Find Required Tenure
Goal: If maximum affordable EMI is ₹18,00,000, what tenure is needed?
The Clicks: - Click on B7 (Monthly EMI cell) - Data tab → What-If Analysis → Goal Seek
In Goal Seek Dialog: - Set cell: B7 - To value: 1800000 (₹18 lakh) - By changing cell:
Click on B6 (Total Months) - Click OK
Result: - New Total Months: Approximately 77 months (6.4 years) - Click OK to accept
Interpretation: By paying ₹18,00,000 per month (higher EMI), loan can be repaid in 77
months instead of 96 months.
Step 8: Create Summary
Goal: Document findings
In separate area: | Description | Value | |————-|——-| | Original EMI | ₹15,33,789 | |
Target EMI | ₹18,00,000 | | Original Tenure | 96 months | | Required Tenure for Target EMI
| 77 months |
Step 9: Format and Save
The Clicks: - Format all monetary values as Currency - Apply Borders - Save: Ctrl+S
Question 12: Investment Analysis using FV and Data Tables - Ocean Research Endowment
Fund
Scenario: You want to build an endowment fund for Marine Biology Research by investing
in recurring deposit scheme.
Data to Enter:

Particulars Values
Monthly Investment (₹) 25,00
0
Annual Interest Rate (%) 7.2%
Investment Period 12
(Years)
Monthly Interest Rate
Total Months
Future Value

Monthly Investment Options to Compare: ₹15,000, ₹20,000, ₹25,000, ₹30,000,

₹35,000, ₹40,000
Tasks:
1. Calculate Monthly Interest Rate = Annual Rate ÷ 12
2. Calculate Total Months = Years × 12
3. Use FV function to calculate Future Value
4. Create a One-Variable Data Table showing Future Value for different monthly
investment amounts
5. Use conditional formatting to highlight investments where Future Value exceeds
₹50,00,000

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up Investment Details
The Clicks: - A1: Particulars, B1: Values - A2: Monthly Investment (₹), B2: 25000 - A3:
Annual Interest Rate (%), B3: 7.2 - A4: Investment Period (Years), B4: 12 - A5: Monthly
Interest Rate - A6: Total Months - A7: Future Value
Step 2: Calculate Monthly Interest Rate
Goal: Convert annual to monthly
The Formula: =B3/12
The Clicks: - Click on B5 - Type: =B3/12 - Press Enter - Result: 0.6 (0.6% per month)
Step 3: Calculate Total Months
Goal: Years to months
The Formula: =B4*12
The Clicks: - Click on B6 - Type: =B4*12 - Press Enter - Result: 144
Step 4: Calculate Future Value Using FV Function
Goal: Calculate investment growth with monthly deposits
The Formula: =FV(B5/100,B6,-B2,0)
Formula Breakdown: - B5/100 = Monthly interest rate as decimal (0.6% = 0.006) - B6 =
Number of periods (144 months) - -B2 = Monthly payment (negative = money paid out) - 0
= No present value (starting from zero)
The Clicks: - Click on B7 - Type: =FV(B5/100,B6,-B2,0) - Press Enter - Result:
Approximately ₹51,23,445
Step 5: Set Up Data Table Structure
Goal: Compare different monthly investment amounts
The Clicks: - D2: Monthly Investment (₹), E2: Future Value (₹) - D3: 15000, D4: 20000,
D5: 25000, D6: 30000, D7: 35000, D8: 40000 - In E2, link to main calculation: Type =B7
Step 6: Create One-Variable Data Table
Goal: Calculate FV for all investment levels
The Clicks: - Select range D2:E8 - Data tab → What-If Analysis → Data Table
In Data Table Dialog: - Row input cell: Leave blank - Column input cell: Click on B2
(Monthly Investment) - Click OK
Result: E3:E8 populate with Future Values: - ₹15,000/month: ₹30,74,067 -
₹20,000/month: ₹40,98,756 - ₹25,000/month: ₹51,23,445 - ₹30,000/month: ₹61,48,134
- ₹35,000/month: ₹71,72,823 - ₹40,000/month: ₹81,97,512
Step 7: Apply Conditional Formatting
Goal: Highlight FV > ₹50,00,000
The Clicks: - Select E3:E8 - Home tab → Conditional Formatting → Highlight Cells Rules
→ Greater Than - Format cells that are GREATER THAN: Type 5000000 - Select
formatting: Light Red Fill with Dark Red Text (or your choice) - Click OK
Result: Cells with FV > ₹50 lakhs get highlighted: - ₹25,000/month: ₹51,23,445
(highlighted) - ₹30,000/month: ₹61,48,134 (highlighted) - ₹35,000/month: ₹71,72,823
(highlighted) - ₹40,000/month: ₹81,97,512 (highlighted)
Step 8: Add Analysis Column
Goal: Identify qualifying options
The Clicks: - F2: Exceeds Target? - F3: =IF(E3>5000000,"YES","NO") - Drag to F8
Step 9: Format and Save
The Clicks: - Apply All Borders - Format E3:E8 as Currency - Make headers Bold - Save:
Ctrl+S

PRACTICE SET 4: Himalayan Glacier Research (Simplified)

Instructions: - Save your Excel file as: RollNo_Name_Set4.xlsx - These are simplified
exercises for quick practice - Answer any TWO questions - Question 13 and Question 14

Question 13: Basic Data Validation and Simple Formulas - Field Research Supplies
Scenario: You manage daily supply costs for Himalayan Glaciology Research Camp in Leh,
Ladakh.
Data to Enter:

Discount Net
Supply Item Quantity Used Cost per Unit (₹) Total Cost (₹) Amount
Thermal 12 8500 0
Tents
Climbing 8 3200 500
Rope
(100m)
Oxygen 15 4500 0
Cylinders
GPS 6 12000 1200
Tracking
Devices
First Aid 20 2800 500
Kits

Tasks:
1. Calculate Total Cost = Quantity Used × Cost per Unit
 2. Calculate Net Amount = Total Cost - Discount
3. Apply Data Validation to Discount column: allow numbers only between 0 and
5000
4. Create a drop-down list for Supply Item with 3 more options: “Emergency Rations”,
“Communication Radio”, “Solar Chargers”
5. Use simple IF function: If Quantity Used ≥ 10, show “Bulk Order”, otherwise
“Standard Order”

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up Table
The Clicks: - Create headers in row 1 - Enter all 5 supply records - Add G1: Order Type
Step 2: Calculate Total Cost
Goal: Quantity × Cost per Unit
The Formula: =B2*C2
The Clicks: - D2: =B2*C2 - Drag to D6 - Results: 102000, 25600, 67500, 72000, 56000
Step 3: Calculate Net Amount
Goal: Total Cost - Discount
The Formula: =D2-E2
The Clicks: - F2: =D2-E2 - Drag to F6 - Results: 102000, 25100, 67500, 70800, 55500
Step 4: Apply Data Validation to Discount
Goal: Restrict discount to 0-5000
The Clicks: - Select E2:E6 - Data → Data Validation
In Dialog: - Allow: Whole number - Data: between - Minimum: 0 - Maximum: 5000 -
Click OK
Step 5: Create Drop-Down for Supply Item
Goal: Add product options
The Clicks: - Select A2:A6 - Data → Data Validation
In Dialog: - Allow: List - Source: Thermal Tents,Climbing Rope (100m),Oxygen
Cylinders,GPS Tracking Devices,First Aid Kits,Emergency
Rations,Communication Radio,Solar Chargers - Click OK

Step 6: Add IF Function for Order Type

Goal: Classify order size
The Formula: =IF(B2>=10,"Bulk Order","Standard Order")
The Clicks: - G2: =IF(B2>=10,"Bulk Order","Standard Order") - Drag to G6 -
Results: - Bulk Order (12 ≥ 10) - Standard Order (8 < 10) - Bulk Order (15 ≥ 10) - Standard
Order (6 < 10) - Bulk Order (20 ≥ 10)
Step 7: Format and Save
The Clicks: - All Borders - Format C2:F6 as Currency - Save: Ctrl+S

Question 14: Simple VLOOKUP - Mountain Expedition Equipment Catalog

Scenario: You work at Himalayan Mountaineering Institute Equipment Store in Darjeeling.
Data to Enter:
Table 1: Equipment Catalog

Equipment Price
Code Equipment Name Brand (₹)
HIME001 Ice Axe - Professional Petzl 8500
HIME002 Crampons - 12-Point Black Diamond 6200
HIME003 Avalanche Beacon BCA 15000
HIME004 Down Sleeping Bag -20°C The North Face 12500
HIME005 High-Altitude Boots La Sportiva 18000

Table 2: Customer Purchase Order

Equipment Code Equipment Name Brand Price (₹) Quantity Total Amount
HIME002 3
HIME004 2
HIME001 4
HIME005 1

Tasks:
1. Use VLOOKUP to fill Equipment Name from Equipment Code
2. Use VLOOKUP to fill Brand and Price
3. Calculate Total Amount = Price × Quantity
4. Use SUM function to calculate Grand Total of all purchases
5. Use CONCAT function to join Equipment Code and Equipment Name (e.g.,
“HIME002 - Crampons - 12-Point”)

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Create Equipment Catalog
The Clicks: - In Sheet2: Create catalog table with all 5 equipment items
Step 2: Set Up Purchase Order
The Clicks: - In Sheet1: Create headers and enter Equipment Codes + Quantities
Step 3: VLOOKUP for Equipment Name
Goal: Look up name from catalog
The Formula: =VLOOKUP(A2,Sheet2!$A$2:$D$6,2,FALSE)
The Clicks: - B2: =VLOOKUP(A2,Sheet2!$A$2:$D$6,2,FALSE) - Drag to B5 - Results:
Crampons - 12-Point, Down Sleeping Bag -20°C, Ice Axe - Professional, High-Altitude Boots
Step 4: VLOOKUP for Brand
Goal: Look up brand (3rd column)
The Formula: =VLOOKUP(A2,Sheet2!$A$2:$D$6,3,FALSE)
The Clicks: - C2: =VLOOKUP(A2,Sheet2!$A$2:$D$6,3,FALSE) - Drag to C5 - Results:
Black Diamond, The North Face, Petzl, La Sportiva
Step 5: VLOOKUP for Price
Goal: Look up price (4th column)
The Formula: =VLOOKUP(A2,Sheet2!$A$2:$D$6,4,FALSE)
The Clicks: - D2: =VLOOKUP(A2,Sheet2!$A$2:$D$6,4,FALSE) - Drag to D5 - Results:
6200, 12500, 8500, 18000
Step 6: Calculate Total Amount
Goal: Price × Quantity
The Formula: =D2*E2
The Clicks: - F2: =D2*E2 - Drag to F5 - Results: 18600, 25000, 34000, 18000
Step 7: Calculate Grand Total
Goal: Sum all purchases
The Formula: =SUM(F2:F5)
The Clicks: - A7: Grand Total - F7: =SUM(F2:F5) - Result: 95,600
Step 8: Use CONCAT for Full Description
Goal: Combine code and name
The Formula: =CONCAT(A2," - ",B2)
The Clicks: - Add new column G1: Full Description - G2: =CONCAT(A2," - ",B2) - Drag
to G5 - Results: - HIME002 - Crampons - 12-Point - HIME004 - Down Sleeping Bag -20°C -
HIME001 - Ice Axe - Professional - HIME005 - High-Altitude Boots
Step 9: Format and Save
The Clicks: - All Borders - Format D2:F7 as Currency - Make F7 (Grand Total) Bold -
Save: Ctrl+S

Question 15: Simple Goal Seek - Research Funding Target

Scenario: You want to save monthly to purchase advanced glacier monitoring equipment
from Norwegian supplier.
Data to Enter:

Particulars Values
Target Equipment Cost 45,00,000
(₹)
Monthly Savings (₹) 2,50,000
Number of Months 18
Total Savings
Shortfall/Surplus

Tasks:
1. Calculate Total Savings = Monthly Savings × Number of Months
2. Calculate Shortfall/Surplus = Total Savings - Target Equipment Cost
3. Use simple IF function: If Shortfall/Surplus ≥ 0, show “Target Achieved”, otherwise
“Target Not Met”
4. Use Goal Seek to find how much monthly savings is needed to reach exactly
₹45,00,000 in 18 months
5. Write the answer (new monthly savings amount after Goal Seek) in a separate cell
with label

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up Savings Plan
The Clicks: - A1: Particulars, B1: Values - Enter all particulars and values - A6: Status
Step 2: Calculate Total Savings
Goal: Monthly amount × Months
The Formula: =B2*B3
The Clicks: - B4: =B2*B3 - Result: 45,00,000 (2,50,000 × 18)
Step 3: Calculate Shortfall/Surplus
Goal: Total - Target
The Formula: =B4-B1
The Clicks: - B5: =B4-B1 - Result: 0 (exactly meets target)
Step 4: Add Status Using IF
Goal: Check if target met
The Formula: =IF(B5>=0,"Target Achieved","Target Not Met")
The Clicks: - B6: =IF(B5>=0,"Target Achieved","Target Not Met") - Result:
Target Achieved
Step 5: Use Goal Seek
Goal: Find exact monthly savings needed for target
The Clicks: - Click on B4 (Total Savings) - Data → What-If Analysis → Goal Seek
In Dialog: - Set cell: B4 - To value: 4500000 - By changing cell: B2 (Monthly Savings) -
Click OK
Result: B2 becomes 2,50,000 (already optimal in this case)
Alternative Scenario: If target was ₹50,00,000: - Goal Seek would calculate: Monthly
Savings = ₹2,77,778
Step 6: Document Answer
Goal: Record findings
The Clicks: - D2: Required Monthly Savings - E2: Link to B2 or type value: =B2 - D3: To
achieve target of: - E3: =B1 - D4: In months: - E4: =B3
Step 7: Format and Save
The Clicks: - Format B1:B5, E2:E4 as Currency - All Borders - Save: Ctrl+S

Question 16: Simple EMI Calculation using PMT - Research Equipment Financing
Scenario: Your research institute wants to purchase a Portable Glacier Core Drilling
System on EMI from Scientific Equipment Finance Ltd.
Data to Enter:

Particulars Values
Equipment Price (₹) 18,00,000
Down Payment (₹) 3,00,000
Loan Amount
Monthly Interest Rate (%) 1.2%
Tenure (Months) 12
Monthly EMI
Total Payment (EMI × Months)
Total Amount Paid (including Down
Payment)
Extra Amount Paid as Interest

Tasks:
1. Calculate Loan Amount = Equipment Price - Down Payment
2. Use PMT function to calculate Monthly EMI
– Interest rate: 1.2% (write as 0.012 or 1.2/100)
– Number of months: 12
– Present value: negative of loan amount
3. Calculate Total Payment = Monthly EMI × 12 months
4. Calculate Total Amount Paid (including Down Payment) = Total Payment + Down
Payment
5. Calculate Extra Amount Paid as Interest = Total Amount Paid - Equipment Price

COMPLETE STEP-BY-STEP SOLUTION:

Step 1: Set Up Purchase Details
The Clicks: - Create all particulars in column A with values in column B
Step 2: Calculate Loan Amount
Goal: Equipment Price - Down Payment
The Formula: =B2-B3
The Clicks: - B4: =B2-B3 - Result: 15,00,000 (18,00,000 - 3,00,000)
Step 3: Calculate Monthly EMI Using PMT
Goal: Calculate monthly payment
The Formula: =PMT(B5/100,B6,-B4)
Formula Breakdown: - B5/100 = 1.2% as decimal = 0.012 - B6 = 12 months - -B4 = Loan
amount (negative)
The Clicks: - B7: =PMT(B5/100,B6,-B4) - Result: Approximately ₹1,33,595
Step 4: Calculate Total Payment (EMI × Months)
Goal: Total paid through EMIs
The Formula: =B7*B6
The Clicks: - B8: =B7*B6 - Result: ₹16,03,140 (approximately)
Step 5: Calculate Total Amount Paid Including Down Payment
Goal: Add down payment to EMI total
The Formula: =B8+B3
The Clicks: - B9: =B8+B3 - Result: ₹19,03,140 (16,03,140 + 3,00,000)
Step 6: Calculate Extra Amount (Interest)
Goal: Find interest paid
The Formula: =B9-B2
The Clicks: - B10: =B9-B2 - Result: ₹1,03,140 (19,03,140 - 18,00,000)
This is the extra amount paid as interest
Step 7: Create Summary
Goal: Highlight key findings
In separate area: | Description | Amount (₹) | |————-|————| | Equipment Price |
18,00,000 | | Total Amount Paid | 19,03,140 | | Interest Component | 1,03,140 | | Effective
Interest Rate | 5.73% |
Calculate Effective Rate: =(B10/B2)*100 = 5.73% total interest on purchase price
Step 8: Format and Save
The Clicks: - Format all monetary values (B2:B10) as Currency - Apply Borders - Make
key cells (B7, B10) Bold to highlight - Save: Ctrl+S

IMPORTANT PRACTICE NOTES

General Excel Best Practices:
1. Always use formulas, never type calculated results
– ✅ =B2*C2 (correct)
 – ❌ Typing “250” (wrong - no formula)
2. **Absolute References ($) for Lookup Tables** - `$A2 :D$6` locks reference when
copying formulas
– Prevents “shifting” errors
3. Data Validation Benefits
– Prevents data entry errors
– Ensures consistency
– Provides user-friendly dropdowns
4. Goal Seek Usage
– Always note original value before running
– Click formula cell first, then run Goal Seek
– Verify result makes logical sense
5. Scenario Manager
– Save current situation as first scenario
– Give descriptive names to each scenario
– Always generate summary report
Common Mistakes to Avoid:
1. Forgetting absolute references ($) in VLOOKUP/HLOOKUP
– Use $A2 :D$6, not A2:D6
2. Using wrong column/row index
– VLOOKUP: Count columns from LEFT (1, 2, 3…)
– HLOOKUP: Count rows from TOP (1, 2, 3…)
3. Negative values in PMT/FV functions
– Loan amount: Use negative -B2
– Monthly payment: Use negative -B2
– This convention makes result positive
4. Data Table selection errors
– Must include formula cell in top-left
– Must select entire range including data
5. Conditional Formatting lost when copying
– Reapply after major changes
– Use Format Painter carefully
Formula Quick Reference:

Function Syntax Purpose

SUM =SUM(A1:A10) Add range
AVERAGE =AVERAGE(A1:A10) Average of range
MAX =MAX(A1:A10) Maximum value
MIN =MIN(A1:A10) Minimum value
Function Syntax Purpose
COUNT =COUNT(A1:A10) Count numbers
IF =IF(A1>10,"Yes","No Logical test
")
AND =AND(A1>10,B1<20) Multiple conditions (all
true)
OR =OR(A1>10,B1<20) Multiple conditions (any
true)
VLOOKUP =VLOOKUP(A1,$D$1:$G Vertical lookup
$10,3,FALSE)
HLOOKUP =HLOOKUP(A1,$D$1:$J Horizontal lookup
$5,2,FALSE)
INDEX-MATCH =INDEX($C$2:$C$10,M Flexible lookup
ATCH(A1,$B$2:$B$10,
0))
PMT =PMT(rate,nper,-pv) Loan payment
FV =FV(rate,nper,- Future value
pmt,0)
LEFT =LEFT(A1,3) First N characters
RIGHT =RIGHT(A1,3) Last N characters
MID =MID(A1,2,5) Extract from middle
CONCAT =CONCAT(A1," - Combine text
",B1)
TRIM =TRIM(A1) Remove extra spaces
TODAY =TODAY() Current date
TEXT =TEXT(TODAY(),"DD- Format date
MMM-YYYY")
IFERROR =IFERROR(A1/ Handle errors
B1,"Error")

Keyboard Shortcuts:

Shortcut Action
Ctrl+S Save
Ctrl+C Copy
Ctrl+V Paste
Ctrl+Z Undo
Ctrl+B Bold
Ctrl+Home Go to A1
Ctrl+Arrow Jump to edge of data
Shortcut Action
F2 Edit cell
F4 Toggle absolute reference
($)
Alt+= AutoSum

Time Management Tips:

1. Read all questions first - Choose the two you’re most comfortable with
2. Allocate time: 25 minutes per question, 10 minutes for review
3. Save every 5 minutes - Don’t lose your work
4. Check formulas - Click on result cells to verify formulas in formula bar
5. Test data validation - Click cells to ensure dropdowns work
6. Verify calculations - Manually check 1-2 results with calculator

END OF COMPREHENSIVE PRACTICE SETS - 16 QUESTIONS WITH COMPLETE

SOLUTIONS

File Naming Convention: - Excel: RollNo_Name_SetNumber.xlsx - Example:

001_Kunal_Shah_Set1.xlsx

Final Checklist Before Submission:

✓ All formulas entered (not typed results)
✓ Data validation applied correctly
✓ Formatting completed (borders, bold headers, currency format)
✓ File saved with correct name
✓ All tasks in selected questions completed
✓ Formulas copied to all required rows
✓ Absolute references used where needed
Good Luck with Your Practice!