Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to make a formula in 1 line to calculate the water bill

0

Hi

I really need help making a formula in 1 line to calculate water bill based on customer class, usage volume with progressive rates. 

*** the table is in the attachment ***

As an example :

Usage volume is 100 M3 

Customer class is Rumah Tangga I

the bill is 276,250 with the following details:

1-10 m3 : 10 x 1,500   = 15,000

11-15     : 5 x 1.850     = 9.250

16-20     : 5 x 2,400     = 12,000

> 20       :  80 x 3,000  = 240,000

GRAND TOTAL            = 276,250

How to make a formula in 1 line to calculate the  water bill ?

Thank you for the help

excel
MS Office Version: 2019
posted by: densoes on Sat Jun 11, 2022 at 3:49 am
Answer
Discuss

Discussion

Hi Densoes and welcome to the Forum.

Your table was NOT attached (you can only upload Excel files inj this Forum; you cannot paste images either).

Do you mean how do you calculate the bill in one row (showing breakdown of charges) or in a single cell (without detail)?   If possible, please edit your question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing data.and layout. Then we should be able to give specific help.
John_Ru (rep: 6142) Jun 11, '22 at 4:49 am
Add to Discussion

Answers

0

Hello Densoes,

Just like John has said, without a sample file it is not possible to provide specific help.

However, the following nested IF will calculate the bill as you indicate.

=IF(A2>20,((10*1500)+(5*1850)+(5*2400)+((A2-20)*3000)),IF(AND(A2>15,A2<21),((10*1500)+(5*1850)+((A2-20)*2400)),IF(AND(A2>10,A2<16),((10*1500)+((A2-20)*1850)),IF(AND(A2>0,A2<11),((A2*1500)),"Enter Volume"))))

Note: change the A2 reference to the cell where the volume will be entered.

If you need something more specific then attach a sample file as John has asked.        [ only Excel files can be attached/uploaded ]

Hope this helps.

posted by: WillieD24 (rep: 547) on Sat Jun 11, 2022 at 9:57 pm
Discuss

Discussion

Good suggestion Willy but I suspect that the user wants a formula (or row of cells) which will work for several tariffs, perhaps with different volume breakpoints. Once more details are given, you can probably use one of the the worksheet LOOKUP functions.
John_Ru (rep: 6142) Jun 13, '22 at 2:49 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login