Electricity Bill - variable rate formula

[positivemind]

Hi all,

Please help me with a formula to calculate electricity bill which is based on variable rate from two slabs mentioned in the sheet. I need the formula in cell G5, where the first 100 units price is always 0 and should calculate remaining units based on the slabs. Please note if the value in F5 is below 500 then it should consider slab A, for value above 500 then consider Slab B.
I have attached sheet. Thank you!

Electricity Bill Calc - Variable Rate.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Monthly Electric Bill of Johnson H. Housing Society (Building 1) (Variable Unit Price)							Electricity Price Per Unit
3									below 500 units consumption			above 500 units consumption
4		Flat No.	Owner's Name	Current Meter Reading	Past Meter Reading	Consumed Unit	Total Bill ($)		Slab A	Rate		Slab B	Rate
5		1/A	John	1950	1100	850			1-100	₹0.00		1-100	₹0.00
6		1/B	Ted	2160	1895	265			101-200	₹2.35		101-400	₹4.70
7		2/A	Marshall						201-400	₹4.70		401-500	₹6.30
8		2/B	Barney						401-500	₹6.30		501-600	₹8.40
9		3/A	Lily						501-600	₹8.40		601-800	₹9.45
10		3/B	Robin						601-800	₹9.45		801-1000	₹10.50
11		4/A	Thomas						801-1000	₹10.50		1001-1500	₹11.55
12		4/B	Arthur						1001-1500	₹11.55
13		5/A	Ross
14		5/B	Rachel
Variable Unit Price

Cell Formulas
Range	Formula
F5:F6	F5	=D5-E5

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Electricity Bill - Variable Rate Formula [SOLVED]
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[positivemind]

yes, but there is a slight deviation when compared to the other post. Here there are two slab rates and the calculation differs for two different scenarios. That's why.
Please suggest me the correct formula for this scenario.