EXCEL Formula

[UnicornTart]

Practical 4: Mastering IF and use LOOKUP Function

Objectives:
1. Use if statement (catch up /review and mastering if statement)
2. Use Lookup to locate data
3. Create an application that calculate and issue electrical bills.

The problem

Suppose you are an IT consultant. You are asked by Jabatan Perkhidmatan Elektrik,
Kementrian Pembangunan (Ministry of Development) to develop a spreadsheet
application that can automatically calculate and issue an electrical bill for every
customer.

Jabatan Elektrik has several tariff structure (Tariff A, I and O). To simplify the
problem, you need only to deal with tariff A (household). The structure of the tariff is
as follows:

• If the use is 8 kwh or less, the minimum charge ($2.00) is applied.
• Between 8 to 10 kwh is 25c/kwh
• Between 11 to 60 kwh is 15c/kwh
• Between 61 to 100 kwh is 10 c/kwh
• Every unit over 100 kwh is 5 c/kwh
The minimum charge is $2.00

Analysis/design

The calculation of bill:
Suppose if a customer uses 65 kwh. The amount due is 10*.25 + 50*.15 + 5*.1 =
$10.50.
You are asked to develop general calculation to solve the above problem.

Layout Design
There will be two worksheets:
1. The data, calculation and amount due worksheet
2. The electrical bill worksheet The layout design of data, calculation and amount due is as follows:
Account
Number
Customer’s
Name
Previous
Reading
Current
Reading
Kwh Usage Amount
Due
X(10) X(50) 99,999,999 99,999,999 See note 1 See note 2

Note 1: You have to develop how to calculate Kwh Usage.
Note 2: You have to develop how to calculate Amount Due according to the tariff A
above.

The Layout design for an electrical bill (will be sent to each customer):
TUTUTAN BIL ELEKTRIK
Account Number Use LOOKUP function to find
customer's name, current and
previous reading, usage and bill
this month based on a user
supplied account number.
Customer’s Name:
Current Reading (kwh):
Previous Reading (kwh):
Usage (kwh)
Bill this month
TODAY’S DATE
Note that: a user needs to input only the Account Number. Other details (Customer’s
Name, Current Reading, Previous Reading, Usage and Bill) will be taken from the
previous sheet. All cells must be protected, except the account number. For assigning
today's data use TODAY function.
Test your application using the following data:
Account
Number ///Customer’s Name ///Previous Reading///Current Reading
131564 Abd Rahman bin Amien 178801 178958
446466 Hjh Faridah Yussof 390 399
211144 Hjh Hasinah Kamaludin 321345 321414
123131 Lamat b Khamis 112213 112260
123112 Lien Kon Sieng 31213 32003
423131 Syamsul Bahri 2144676 2145073
211312 Hj Jum'at b Hj Khamis 563212 563219
563311 Mahri b Hj Tengah 563143 563182
121211 Huzaimi b Matasan 4321654 4321759
941314 Matusin b Ibrahim 3215434 3216634
Demonstrate the application to your lecturer/tutor to get a mark.


I am stuck at calculating the amount due im supposed to use the IF function?

[Richard Buttrey]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

All I'd add is that when you've changed the title you'll also need to upload the workbook.