Practical 10 – Gas Bill Calculation
Objective:
- Calculate unit consumption using the `IF` function.
- Compute the gas charges based on the unit consumption (1.25 per
unit if consumption is <= 200, otherwise 1.80 per unit).
- Calculate the sales tax as 10% of the gas charges.
- Compute the amount due as gas charges + sales tax.
Instructions:
- Calculate unit consumed:
Use the formula: =C2-B2
- Calculate gas charges:
Use the formula: =IF(D2<200,D2*1.25,D2*1.8)
- Calculate sales tax:
Use the formula: =(10/100) * E2
- Calculate amount payable:
Use the formula: = F2+E2
Procedure:
- Open Microsoft Excel:
Click Start → All Programs → MS Office → MS Excel
- Open a new workbook (the workbook contains 3 sheets by default).
- Write the data in the cells (use rows and columns).
- Calculate unit consumed:
Use the formula: =C2-B2
- Calculate gas charges:
Use the formula: =IF(D2<200,D2*1.25,D2*1.8)
- Calculate sales tax:
Use the formula: =(10/100) * E2
- Calculate the amount payable:
Use the formula: = F2+E2
- Save the document:
Click File → Save As and name the file.
- Print the document:
Click File → Print → Select Printer → OK
- Close the document:
Click File → Close
Output Example:
| Customer Name |
Previous Reading |
Current Reading |
Unit Consumed |
Gas Charges |
Sales Tax |
Amount Payable |
| John Doe |
1200 |
1400 |
=C2-B2 |
=IF(D2<200,D2*1.25,D2*1.8) |
=(10/100)*E2 |
=F2+E2 |
| Jane Smith |
800 |
1100 |
=C3-B3 |
=IF(D3<200,D3*1.25,D3*1.8) |
=(10/100)*E3 |
=F3+E3 |
| Michael Ray |
2000 |
2300 |
=C4-B4 |
=IF(D4<200,D4*1.25,D4*1.8) |
=(10/100)*E4 |
=F4+E4 |
| Total |
– |
– |
=SUM(D2:D4) |
=SUM(E2:E4) |
=SUM(F2:F4) |
=SUM(G2:G4) |
The formulas above automatically calculate unit consumption, gas charges, sales tax, and the total payable amount for each customer.