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.

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" wrote:

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,

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" wrote:

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" wrote:

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.

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


"kaw" wrote:

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" wrote:

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" wrote:

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.