Help with IF Statement for Billing Tiered Water Rates

[rsisk3365]

I have a problem I cannot work out. I need to show billing numbers according to the following table but cannot get my IF statement right. It is beyond my comprehension and I need help! Thanks!

base rate	14.61
0-2ccf	3.76		0-1496 gal
2-5 ccf	8.49		1496-3740 gal
5-8ccf	9.3		3740-5984 gal
8-15ccf	28.42		5984-11220 gal
>15ccf	71.24		>11220 gal

<colgroup><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>

 

[Dryver14]

if someone is less than 2 what is the return you want to see? 3.76?

 

[rsisk3365]

I am converting ccf to gallons, on the right, and am billing per gallon. All customers get charged the $14.61 base fee, then less than 2 ccf, or 1496 gallons would be 3.76 per 100 gallons.

 

[Perpa]

See if this does what you want...

Excel Workbook
	A	B	C	D	E	F	G	H
1	base rate (ccf)							$14.61
2		gallons	Price/100 gal		ccf used	100s of gallons
3					1.5	11.22078
4	0-2	0-1496 gal	3.76					$ 42.19
5	2-5	1496-3740 gal	8.49
6	5-8	3740-5984 gal	9.3
7	8-15	5984-11220 gal	28.42
8	>15	>11220 gal	71.24
9
10							Total =	$ 56.80
Sheet1

Formulae:
H4 =IF(E3<=2,C4*F3,C4*2*7.48052)
H5=IF(AND(E3>2,E3<=5),(F3-14.48052)*C5,IF(E3>5,C5*3*7.48052,""))
H6=IF(AND(E3>5,E3<=8),(F3-37.4026)*C6,IF(E3>8,C6*3*7.48052,""))
H7=IF(AND(E3>8,E3<=15),(F3-59.84416)*C7,IF(E3>8,C7*7*7.48052,""))
H8=IF((E3>15),(F3-112.2078)*C8,"")

 

[Perpa]

See if this does what you want...

Sheet1

	A	B	C	D	E	F	G	H
1	base rate (ccf)							$14.61
2		gallons	Price/100 gal		ccf used	100s of gallons
3					1.5	11.22078
4	0-2	0-1496 gal	3.76					$ 42.19
5	2-5	1496-3740 gal	8.49
6	5-8	3740-5984 gal	9.3
7	8-15	5984-11220 gal	28.42
8	>15	>11220 gal	71.24
9
10							Total =	$ 56.80

<tbody>
</tbody>

Excel tables to the web >> <a target="_blank" index.php?f='1"' www.excel-jeanie-html.de="" *******="_gaq.push(['_trackEvent', 'Outgoing', '', '<a href=">http://www.excel-jeanie-html.de/index.php?f=1</a>']);" style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Formulae:
H4 =IF(E3<=2,C4*F3,C4*2*7.48052)
H5=IF(AND(E3>2,E3<=5),(F3-14.48052)*C5,IF(E3>5,C5*3*7.48052,""))
H6=IF(AND(E3>5,E3<=8),(F3-37.4026)*C6,IF(E3>8,C6*3*7.48052,""))
H7=IF(AND(E3>8,E3<=15),(F3-59.84416)*C7,IF(E3>8,C7*7*7.48052,""))
H8=IF((E3>15),(F3-112.2078)*C8,"")

Ooops! Small correction to the formula in cell H7:

H7=IF(AND(E3>8,E3<=15),(F3-59.84416)*C7,IF(E3>15,C7*7*7.48052,""))

Also...the formula in cell F3 is:

F3=E3*748.052/100

 

[jtakw]

Hi,

To clarify, are you billing Per Gallon, or Per 100 Gallons?
So if 1500 Gallons were used, what is the correct result?
The 4 Gallons that exceeds Tier 1 of 1496, is that charged as 4 Gallons, or rounded up to 100 Gallons?