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!

[TABLE="width: 542"]
<colgroup><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]base rate[/TD]
[TD="align: right"]14.61[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-2ccf[/TD]
[TD="align: right"]3.76[/TD]
[TD][/TD]
[TD="colspan: 2"]0-1496 gal[/TD]
[/TR]
[TR]
[TD]2-5 ccf[/TD]
[TD="align: right"]8.49[/TD]
[TD][/TD]
[TD="colspan: 2"]1496-3740 gal[/TD]
[/TR]
[TR]
[TD]5-8ccf[/TD]
[TD="align: right"]9.3[/TD]
[TD][/TD]
[TD="colspan: 2"]3740-5984 gal[/TD]
[/TR]
[TR]
[TD]8-15ccf[/TD]
[TD="align: right"]28.42[/TD]
[TD][/TD]
[TD="colspan: 2"]5984-11220 gal[/TD]
[/TR]
[TR]
[TD]>15ccf [/TD]
[TD="align: right"]71.24[/TD]
[TD][/TD]
[TD="colspan: 2"]>11220 gal[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
	gallons	Price/100 gal		ccf used	100s of gallons
0-1496 gal
1496-3740 gal
						Total =

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: left"]base rate (ccf)
[/TD]

[TD="align: right"] $14.61
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"]1.5
[/TD]
[TD="align: right"]11.22078
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: left"]0-2
[/TD]

[TD="align: right"]3.76
[/TD]

[TD="align: right"] $ 42.19
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: left"]2-5
[/TD]

[TD="align: right"]8.49
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: left"]5-8
[/TD]
[TD="align: left"]3740-5984 gal
[/TD]
[TD="align: right"]9.3
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: left"]8-15
[/TD]
[TD="align: left"]5984-11220 gal
[/TD]
[TD="align: right"]28.42
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: left"]>15
[/TD]
[TD="align: left"]>11220 gal
[/TD]
[TD="align: right"]71.24
[/TD]

[TD="align: center"]9
[/TD]

[TD="align: center"]10
[/TD]

[TD="align: right"] $ 56.80
[/TD]

</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?