Electricity Bill Sheet

[XKruodo]

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

 

[Jacob Skaria]

Apply this formula in cell C2 and copy down as required

=MIN(A2,$H$1-SUM($C$1:C1))

 

[Fred Smith]

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]

Thanks Jacob, your formula worked.
Fred, if i enter 1000 in H1 i wanted this,

 

[XKruodo]

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..

 

[Jacob Skaria]

The formula which I suggested will not retrive the balance amount in the last
slot...Try th ebelow...

=IF(AND(A3="",$H$1>SUM($C$1:C1)),$H$1-SUM($C$1:C1),
MIN(A2,$H$1-SUM($C$1:C1)))

 

[XKruodo]

Worked..
Thanks.

The formula which I suggested will not retrive the balance amount in the last
slot...Try th ebelow...

=IF(AND(A3="",$H$1>SUM($C$1:C1)),$H$1-SUM($C$1:C1),
MIN(A2,$H$1-SUM($C$1:C1)))