Adding numbers by limit

[Dubai Dave]

Hi

I need to carry out an energy audit and would like to use a spreadsheet to
find costs

eg
1 - 2000 kWh = 0.20
2001 - 4000 kWh = 0.22
4001 - 6000 kWh = 0.24 and so on

so that when I get a figure of say 4200 and enter in the first column it
will show the first 2000 units at 0.20 in the next column then 2001 - 4000 at
0.22 in the next column and the remaining 200 units at 0.24 in the last
column. There are other limits to add but I could add as required providing I
get the basic idea???

 

[Mike H]

Hi,

heres the first 3 I think you should be able to follow the logic

=MIN(A1*0.2,400)
=MAX(0,MIN((A1-2000)*0.22,440))
=MAX(0,MIN((A1-4000)*0.24,480))

The max bits in 2 and 3 stop it returning negative values.

Mike

 

[John C]

=CHOOSE(LOOKUP(A1,{0,1;2001,2;4001,3;6001,4}),A1*0.2,(A1-2000)*0.22+400,(A1-4000)*0.24+840,(A1-6000)*0.26+1320)

Flexibility to add more easily. Not sure what your topend clients would use
in kWh. First, set up a category in the lookup field based on usage
(0-2000=1, 2001-4000=2, 4001-6000=3, 6001+=4). Then with the CHOOSE function,
set formula for each category, with every category being separated by a comma
(up to 30 categories for CHOOSE function I believe). Each category beyond 1
has to add the charges for the category(ies) prior to it. Hence the +400,
+840, +1320, etc.

If this is too cumbersome for you, post back, and will help you set up a 3
column table that will work for you.

 

[Dubai Dave]

Hi John

I am amazed at such a quick response as this is the first time I have used
this facility
If you have a look at this website you will see exactly what I want to
recreate https://e-services.dewa.gov.ae/Tariff/NewTariff.aspx

I use commercial

Just need to know how to enter the details as I am a very basic user

Thanking you in advance

 

[John C]

If you are wanting a layout like you have given, that is beyond me. As far as
the formulas, you could follow my basic formula that I have given, and make
one for water as well as electrical. The format of the formula is the same,
but I don't think I could help setup a user form like that.