Formula Function

[Ron Rosenfeld]

I am trying to calculate a progressive rate for water use charges:
<8500=$40,
8501 up to [email protected]/1000
21000 up to [email protected]/1000

Col.A lists consumtion in gallons
is there a formula I can use for this?

You have some undefined amounts and inconsistencies.

For example, 8500-8501
20999-21000

But the following seems to do what you want. You can modify some of the
breakpoints if necessary.

Set up a 3 column table someplace on your sheet as follows and NAME it
RateTable:

0 $0.00000 $0.00475
8500 $40.37500 $0.00475
21000 $99.75000 $0.00525
50000 $252.00000 $0.00600


Then use this formula:

=IF(gals<=8500,40,VLOOKUP(gals,RateTbl,2)+
VLOOKUP(gals,RateTbl,3)*(gals-VLOOKUP(gals,RateTbl,1)))

--ron

 

[Ron Rosenfeld]

I am trying to calculate a progressive rate for water use charges:
<8500=$40,
8501 up to [email protected]/1000
21000 up to [email protected]/1000

Col.A lists consumtion in gallons
is there a formula I can use for this?

That table should have been NAME'd RateTbl
--ron

 

[SM_NCSW]

Ron,
Thank you--this works!
However, I am trying to figure out how the formula is using the table in the
calculation (rows or columns)--I may be able to use this type of calculation
for other functions. I am new to more than just your basic calculations,
your insite is most appreciated.

 

[Ron Rosenfeld]

Ron,
Thank you--this works!
However, I am trying to figure out how the formula is using the table in the
calculation (rows or columns)--I may be able to use this type of calculation
for other functions. I am new to more than just your basic calculations,
your insite is most appreciated.

Take a look at HELP for VLOOKUP (and HLOOKUP for completeness).

Then use the Formula Evaluation tool to see what is going on, step-by-step.

Basically, in this table, column 1 contains the "break points", column 2 is the
total for up to that break point, and column 3 is the multiplier for charges
over that breakpoint.

0 $0.00000 $0.00475
8500 $40.37500 $0.00475
21000 $99.75000 $0.00525
50000 $252.00000 $0.00600


In your case, a special case needs to be made for up to 8500 since you later
indicated that amounts over 8500 would have the rate of 4.75 applied to the
entire amount, but 8500 would be charged only $40, instead of $40.375

So, if you have 22000 gallons:

=IF(gals<=8500,40,
This evaluates TO False, so go to next step

VLOOKUP(gals,RateTbl,2)
Get charges for up to 21000 = 99.75

+VLOOKUP(gals,RateTbl,3)
Get multiplier for charges above 21000 = $0.00525/gal

*(gals-VLOOKUP(gals,RateTbl,1)))
Get number of gallons above 21000 = 1000
Multiplied by $0.00525 = $5.25

Add to the $99.75 from step 2 = $105




--ron