Formula for calculating a running balance up to a maximum limit

[kaw]

Could anyone tell me how to write a complex formula that will calculate a
running balance up to a certain value in a given column? We reimburse
eduation expenses up to a certain limit. What I need the formula to do is
calculate a running total until the limit it reached and then tell me what it
left to reimburse. Here's an example: each college course is $1300. The
limit for reimbursement is $3,000. If an individual takes 3 courses, I need
the cell to total the $1,300 for the first two courses but then tell me that
I can only pay $400 for the last course.

Thanks for any assistance.

 

[Ron Coderre]

Try something like this:

With
Employee Names in Col_A, with A1 as the column heading
Course Fees in Col_B, with B1 as the column heading

C1: Reimbursement
C2:
=MIN(SUMIF($A$2:$A2,$A2,$B$2:$B2),3000)-MIN(SUMIF($A$1:$A1,$A2,$B$1:$B1),3000)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[kaw]

Ron,

Thanks for responding. I'm not sure if the formula works because I've got
my sheet set up differently so let me show you how I have it done.
col A col B col C (submitted expense)
col D (reimbursed)
college name 1st course cost 1300
1300
2nd course cost 1300
1300
3rd course cost 1300
400
Total 3900
3000

Is it possible to write a formula for the cell that contains the 400 so that
it adds up cells d2 and d3; and then, upon entering the 1300 in c3, the
formula in d3 calculates the difference between what has already been paid
and the available balance to return the $400 value? Does that make sense? I
may be making it more complex than it should be.

 

[Ron Coderre]

Evidently your situation is less complicated that I anticipated. If your
sheet only contains data for one employee, try this:

Using
the structure you posted,
and assuming that column headings are in Row_1

This formula calculates the amount to be reimbursed for each submitted
expense listed
D2: =MIN(SUM($C$2:$C2),3000)-MIN(SUM($C$1:$C1),3000)
Copy that formula down as far as needed

Does that help?
***********
Regards,
Ron

XL2002, WinXP