Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding numbers by limit
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??? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding numbers by limit
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 "Dubai Dave" wrote: 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??? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding numbers by limit
=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. -- ** John C ** "Dubai Dave" wrote: 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??? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding numbers by limit
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" wrote: =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. -- ** John C ** "Dubai Dave" wrote: 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??? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding numbers by limit
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. -- ** John C ** "Dubai Dave" wrote: 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" wrote: =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. -- ** John C ** "Dubai Dave" wrote: 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??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to limit amount of numbers in cells in excel | Excel Worksheet Functions | |||
Formula to count numbers past a specified limit | Excel Worksheet Functions | |||
Adding a limit line | Charts and Charting in Excel | |||
in excel, how to limit digits of negative hexadecimal numbers? | Excel Discussion (Misc queries) | |||
Can I change the 15-digit limit in Excel for numbers ? | Excel Worksheet Functions |