Tiered pricing

Cross-posted at
https://www.msofficeforums.com/excel...need-help.html
https://www.mrexcel.com/board/thread...array.1207505/
--6SJ


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 like this:


Excel Forum 2.jpg


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

Hi. Forum Rule 1 is very short. It states: "Title must briefly summarize your request."

A GOOD thread title is the sort of thing you would use as a search term on Google*. Your chosen title would produce a squillion useless hits. In future, please take a bit more time to think of a descriptive title that would be "Google-friendly". Many people search the forum answers looking for help and your very general title wouldn't help them at all.

Since you are a new user of this site, on this occasion, I have changed it for you.

However, if you continue to use weak titles, you can expect to have your thread BLOCKED until you change it yourself. If that happens, to edit the thread title, open the original post, click "Edit Post" (bottom right) to access the area where you can edit your title.

*Other search engines are available!!!

Regards,

Glenn.

ALSO>>>> Please read the yellow banner (top) and upload an excel file... not a non-editable picture of one.

See attached

Cost

=SUMPRODUCT(($F$2>=$C$2:$C$5)*($F$2-$C$2:$C$5),$E$2:$E$5)

Create table for each Customer Class

Densoes, As requested previously, please upload the file that YOU have in front of you.

I have added a sample table for all Customer types, using named ranges

=SUMPRODUCT((Usage>=Tiers)*(Usage-Tiers),INDIRECT(SUBSTITUTE(D$10," ","_")))

Originally Posted by densoes

Like this Glenn ??

Cell F20 formula , Drag down

Formula:

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I have added for you since you are new but please read all the rules.

The attachment you provided has changed slightly, you can see if this means it, if you have any questions, please come back and ask again

Total cost = 1168250 Correct ?

Cell D19 formula , Drag right

Formula:

Please Login or Register  to view this content.

Cell I23 formula

Formula:

Please Login or Register  to view this content.