On Tue, 28 Oct 2008 08:31:04 -0700, SM_NCSW
wrote:

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