|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 19th, 2010, 04:05 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Electricity Bill Sheet
Hi,
I posted a similar question 2 days back but seems like i was not clear enough with the question. The following table contains UNITS of electricity and corresponding rates. UNITS RATES 50 2.5 100 3.6 150 4.5 300 5.4 This table shows that for first 50 units consumed, rate will be 2.5 PER UNIT. For next 100 units, rate will be 3.6 per unit. Not 50 to 100, BUT rest 100 above 50. That would total to 150. After that for next 150 units 4.5 per unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be 5.4 per unit. If i enter 425 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 150 300 5.4 125 If i enter 260 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 110 300 5.4 0 If i enter 55 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 5 150 4.5 0 300 5.4 0 If i enter 35 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 35 100 3.6 0 150 4.5 0 300 5.4 0 
|
|
#2
March 19th, 2010, 04:56 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Electricity Bill Sheet
Apply this formula in cell C2 and copy down as required
=MIN(A2,$H$1-SUM($C$1:C1)) -- Jacob "XKruodo" wrote: Hi, I posted a similar question 2 days back but seems like i was not clear enough with the question. The following table contains UNITS of electricity and corresponding rates. UNITS RATES 50 2.5 100 3.6 150 4.5 300 5.4 This table shows that for first 50 units consumed, rate will be 2.5 PER UNIT. For next 100 units, rate will be 3.6 per unit. Not 50 to 100, BUT rest 100 above 50. That would total to 150. After that for next 150 units 4.5 per unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be 5.4 per unit. If i enter 425 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 150 300 5.4 125 If i enter 260 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 110 300 5.4 0 If i enter 55 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 5 150 4.5 0 300 5.4 0 If i enter 35 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 35 100 3.6 0 150 4.5 0 300 5.4 0
|
|
#3
March 19th, 2010, 05:04 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Electricity Bill Sheet
Your question still isn't very clear, mostly, in this case, because you
didn't ask one. You're not making it easy for people to help you when you make them guess what you are looking for. Assuming you want the formula which creates the results in column C, and assuming your data in this column starts in row 2, try this in C2: =IF(SUM(A$2:A2)$H$1,A2,MAX(0,$H$1-SUM(A$1:A1))) and copy down PS. What result do you want if you put 1000 in H1? Regards, Fred "XKruodo" wrote in message ... Hi, I posted a similar question 2 days back but seems like i was not clear enough with the question. The following table contains UNITS of electricity and corresponding rates. UNITS RATES 50 2.5 100 3.6 150 4.5 300 5.4 This table shows that for first 50 units consumed, rate will be 2.5 PER UNIT. For next 100 units, rate will be 3.6 per unit. Not 50 to 100, BUT rest 100 above 50. That would total to 150. After that for next 150 units 4.5 per unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be 5.4 per unit. If i enter 425 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 150 300 5.4 125 If i enter 260 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 110 300 5.4 0 If i enter 55 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 5 150 4.5 0 300 5.4 0 If i enter 35 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 35 100 3.6 0 150 4.5 0 300 5.4 0
|
|
#4
March 19th, 2010, 06:03 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Electricity Bill Sheet
Thanks Jacob, your formula worked.
Fred, if i enter 1000 in H1 i wanted this, If i enter 1000 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 150 300 5.4 700 "Fred Smith" wrote: Your question still isn't very clear, mostly, in this case, because you didn't ask one. You're not making it easy for people to help you when you make them guess what you are looking for. Assuming you want the formula which creates the results in column C, and assuming your data in this column starts in row 2, try this in C2: =IF(SUM(A$2:A2)$H$1,A2,MAX(0,$H$1-SUM(A$1:A1))) and copy down PS. What result do you want if you put 1000 in H1? Regards, Fred "XKruodo" wrote in message ... Hi, I posted a similar question 2 days back but seems like i was not clear enough with the question. The following table contains UNITS of electricity and corresponding rates. UNITS RATES 50 2.5 100 3.6 150 4.5 300 5.4 This table shows that for first 50 units consumed, rate will be 2.5 PER UNIT. For next 100 units, rate will be 3.6 per unit. Not 50 to 100, BUT rest 100 above 50. That would total to 150. After that for next 150 units 4.5 per unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be 5.4 per unit. If i enter 425 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 150 300 5.4 125 If i enter 260 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 110 300 5.4 0 If i enter 55 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 5 150 4.5 0 300 5.4 0 If i enter 35 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 35 100 3.6 0 150 4.5 0 300 5.4 0 .
|
|
#5
March 19th, 2010, 06:06 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Electricity Bill Sheet
Opps! I dint check for 1000 and it doesn't work either
 )It works fine till 600. Above 600 units, C5 doesn't change. It shows 300.. "XKruodo" wrote: Thanks Jacob, your formula worked. Fred, if i enter 1000 in H1 i wanted this, If i enter 1000 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 150 300 5.4 700 "Fred Smith" wrote: Your question still isn't very clear, mostly, in this case, because you didn't ask one. You're not making it easy for people to help you when you make them guess what you are looking for. Assuming you want the formula which creates the results in column C, and assuming your data in this column starts in row 2, try this in C2: =IF(SUM(A$2:A2)$H$1,A2,MAX(0,$H$1-SUM(A$1:A1))) and copy down PS. What result do you want if you put 1000 in H1? Regards, Fred "XKruodo" wrote in message ... Hi, I posted a similar question 2 days back but seems like i was not clear enough with the question. The following table contains UNITS of electricity and corresponding rates. UNITS RATES 50 2.5 100 3.6 150 4.5 300 5.4 This table shows that for first 50 units consumed, rate will be 2.5 PER UNIT. For next 100 units, rate will be 3.6 per unit. Not 50 to 100, BUT rest 100 above 50. That would total to 150. After that for next 150 units 4.5 per unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be 5.4 per unit. If i enter 425 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 150 300 5.4 125 If i enter 260 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 110 300 5.4 0 If i enter 55 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 5 150 4.5 0 300 5.4 0 If i enter 35 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 35 100 3.6 0 150 4.5 0 300 5.4 0 .
|
|
#6
March 19th, 2010, 06:12 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Electricity Bill Sheet
The formula which I suggested will not retrive the balance amount in the last
slot...Try th ebelow... =IF(AND(A3="",$H$1SUM($C$1:C1)),$H$1-SUM($C$1:C1), MIN(A2,$H$1-SUM($C$1:C1))) -- Jacob "XKruodo" wrote: Thanks Jacob, your formula worked. Fred, if i enter 1000 in H1 i wanted this, If i enter 1000 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 150 300 5.4 700 "Fred Smith" wrote: Your question still isn't very clear, mostly, in this case, because you didn't ask one. You're not making it easy for people to help you when you make them guess what you are looking for. Assuming you want the formula which creates the results in column C, and assuming your data in this column starts in row 2, try this in C2: =IF(SUM(A$2:A2)$H$1,A2,MAX(0,$H$1-SUM(A$1:A1))) and copy down PS. What result do you want if you put 1000 in H1? Regards, Fred "XKruodo" wrote in message ... Hi, I posted a similar question 2 days back but seems like i was not clear enough with the question. The following table contains UNITS of electricity and corresponding rates. UNITS RATES 50 2.5 100 3.6 150 4.5 300 5.4 This table shows that for first 50 units consumed, rate will be 2.5 PER UNIT. For next 100 units, rate will be 3.6 per unit. Not 50 to 100, BUT rest 100 above 50. That would total to 150. After that for next 150 units 4.5 per unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be 5.4 per unit. If i enter 425 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 150 300 5.4 125 If i enter 260 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 110 300 5.4 0 If i enter 55 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 5 150 4.5 0 300 5.4 0 If i enter 35 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 35 100 3.6 0 150 4.5 0 300 5.4 0 .
|
|
#7
March 19th, 2010, 06:22 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Electricity Bill Sheet
Worked..
Thanks. "Jacob Skaria" wrote: The formula which I suggested will not retrive the balance amount in the last slot...Try th ebelow... =IF(AND(A3="",$H$1SUM($C$1:C1)),$H$1-SUM($C$1:C1), MIN(A2,$H$1-SUM($C$1:C1))) -- Jacob "XKruodo" wrote: Thanks Jacob, your formula worked. Fred, if i enter 1000 in H1 i wanted this, If i enter 1000 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 150 300 5.4 700 "Fred Smith" wrote: Your question still isn't very clear, mostly, in this case, because you didn't ask one. You're not making it easy for people to help you when you make them guess what you are looking for. Assuming you want the formula which creates the results in column C, and assuming your data in this column starts in row 2, try this in C2: =IF(SUM(A$2:A2)$H$1,A2,MAX(0,$H$1-SUM(A$1:A1))) and copy down PS. What result do you want if you put 1000 in H1? Regards, Fred "XKruodo" wrote in message ... Hi, I posted a similar question 2 days back but seems like i was not clear enough with the question. The following table contains UNITS of electricity and corresponding rates. UNITS RATES 50 2.5 100 3.6 150 4.5 300 5.4 This table shows that for first 50 units consumed, rate will be 2.5 PER UNIT. For next 100 units, rate will be 3.6 per unit. Not 50 to 100, BUT rest 100 above 50. That would total to 150. After that for next 150 units 4.5 per unit. This totals up TOTAL UNITS to 300. For units above 300, rate will be 5.4 per unit. If i enter 425 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 150 300 5.4 125 If i enter 260 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 100 150 4.5 110 300 5.4 0 If i enter 55 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 50 100 3.6 5 150 4.5 0 300 5.4 0 If i enter 35 units in H1, i should get the following in column C as under. UNITS RATES TOTAL UNITS 50 2.5 35 100 3.6 0 150 4.5 0 300 5.4 0 .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
