Module: M1-R5: Information Technology Tools and Network Basics
Chapter: Spreadsheet
This section demonstrates **three practical spreadsheet examples** that combine various Excel and Calc formulas — including SUM, AVERAGE, IF, VLOOKUP, and nested logic — to automate calculations for everyday tasks:
Objective: To calculate Total, Average, Grade, and Final Result using formulas.
| Roll No | Name | Maths | Science | English | Total | Average | Grade | Result |
|---|---|---|---|---|---|---|---|---|
| 1 | Ravi | 78 | 85 | 90 | =SUM(C2:E2) | =AVERAGE(C2:E2) | =IF(F2>=80,"A",IF(F2>=60,"B","C")) | =IF(G2>=40,"Pass","Fail") |
| 2 | Neha | 65 | 72 | 58 | =SUM(C3:E3) | =AVERAGE(C3:E3) | =IF(F3>=80,"A",IF(F3>=60,"B","C")) | =IF(G3>=40,"Pass","Fail") |
| 3 | Arun | 35 | 45 | 39 | =SUM(C4:E4) | =AVERAGE(C4:E4) | =IF(F4>=80,"A",IF(F4>=60,"B","C")) | =IF(G4>=40,"Pass","Fail") |
Explanation:
=SUM(C2:E2) → Adds all subject marks.=AVERAGE(C2:E2) → Finds the average marks.=IF(F2>=80,"A",IF(F2>=60,"B","C")) → Grades based on marks.=IF(G2>=40,"Pass","Fail") → Declares final result.Objective: To calculate Gross Salary, Deductions, and Net Salary automatically.
| Emp ID | Name | Basic | HRA (20%) | DA (10%) | Gross | Deduction (PF 12%) | Net Salary |
|---|---|---|---|---|---|---|---|
| 101 | Ramesh | 20000 | =C2*20% | =C2*10% | =C2+D2+E2 | =C2*12% | =F2-G2 |
| 102 | Pooja | 25000 | =C3*20% | =C3*10% | =C3+D3+E3 | =C3*12% | =F3-G3 |
| 103 | Karan | 18000 | =C4*20% | =C4*10% | =C4+D4+E4 | =C4*12% | =F4-G4 |
Explanation:
=C2*20% → Calculates HRA as 20% of Basic.=C2*10% → Calculates DA as 10% of Basic.=C2+D2+E2 → Gross Salary = Basic + HRA + DA.=C2*12% → PF deduction at 12%.=F2-G2 → Net Salary = Gross − Deductions.Objective: To calculate total bill based on unit consumption using slab rates.
| Consumer No | Name | Units Used | Rate | Bill Amount | Remarks |
|---|---|---|---|---|---|
| 501 | Rajesh | 120 | =IF(C2<=100,5,IF(C2<=200,6,8)) | =C2*D2 | =IF(C2>150,"High Usage","Normal") |
| 502 | Meena | 90 | =IF(C3<=100,5,IF(C3<=200,6,8)) | =C3*D3 | =IF(C3>150,"High Usage","Normal") |
| 503 | Arjun | 220 | =IF(C4<=100,5,IF(C4<=200,6,8)) | =C4*D4 | =IF(C4>150,"High Usage","Normal") |
Explanation:
=IF(C2<=100,5,IF(C2<=200,6,8)) → Uses slab-based rate system.=C2*D2 → Calculates total bill amount.=IF(C2>150,"High Usage","Normal") → Adds usage status.These examples show how formulas, functions, and conditional logic in spreadsheets make tasks like result processing, payroll, and utility billing easy and automated. You can modify the formulas to fit real-world needs and expand them with charts or pivot tables for data visualization.
Login with Google