Help In Calculating My Electricity Bill With Excel

[manas_zafar]

Hi all, hope you all are doing great! I'm having trouble calculating my electricity bill as there is a complex calculation involved when it comes to calculating no of units consumed as there are different slabs at which electricity charges is calculated.
I am trying presenting here what should I calculate to find a payable amount of any given month based on unit consumed on any given month.

Here it is:
Bill Cal Excel Help.png

Now the real problem is that the bill is calculated as per the criteria under which a user will get benefit of only one previous slab, Have a look at this criteria below:
K electric Tarrif.png



i.e if I consume say 150 Units the I will be charged first 100 units at Slab 2 i.e (1-100 Units @ Rs 5.79) and other 50 Units at Slab 3 rates i.e (100-200 Units @ Rs 8.11).

Similarly if I consume 800 units in a month I will be charged 700 Units at Slab 4 rates ignoring all the previous slab i,e (700 Units * 16 @ Slab 4 rates) and other 100 Units will be charged at Slab 5 rates i.e (100* * 18 @ Slab 5 rates).

My question is how can I calculate this in related columns based on units consumes, is there any formula to work around this. I have tried using IF function but it's only good up to 2nd slab. When it comes to breaking up as per unit consumed I am stuck.

Here is my excel sheet link for download:
https://drive.google.com/file/d/0B2I...ew?usp=sharing

Any Help from anyone will be highly appreciated.


Thanks,

[oeldere]

I think it can be done with Vlookup.

But since you don't add an excel file, I can't show it to you.

[manas_zafar]

I think it can be done with Vlookup.

But since you don't add an excel file, I can't show it to you.

I have uploaded it now. Please show me how.

[samba_ravi]

e3=(D3<=50)*D3
f3=(D3>50)*MEDIAN(0,100,$D3)
g3=MEDIAN(0,100,$D3-100)
h3=MEDIAN(0,100,$D3-200)
i3=MEDIAN(0,400,$D3-300)
j3=(D3>700)*($D3-700)
Try this and copy towards down

[samba_ravi]

or can try
K3=IF(D3<50,D3*2,SUMPRODUCT((D3-{0,100,200,300,700}>0)*(D3-{0,100,200,300,700})*({5.79,8.11,10.2,16,18}-{0,5.79,8.11,10.2,16})))
TO DIRECTLY CALCULATE POWER BILL