Harlan,

I should have been a little more clear. This is for the year. So the first
$500 is reimbursed at 100% the next $1250 is reimbursed at 80% and as you
stated, the rest is not reimbursed. My spreadsheet needs to keep a record of
all of the expenses and payments for the year. I'm going to try your formula
and see what happens. I'll let you know. I forgot to email the spreadsheet
to myself from work so I can't do it until tomorrow. Thanks for the help.
Also, each sheet within the workbook is a different employee.


"Harlan Grove" wrote:

MrsGixxer wrote...
...
. . . We pay 100%
the first $500 of an expense. After the first $500 we pay 80% of
the next $1250. . . . I have a
formula that works for the very first time the 80% is used but am
stumped after that. This is the formuala I have that doesn't work
past the first time. =IF(G10+B11<=$C$5,(B11*G4),0)

G10 being the total of previous expenses
B11 being the new expense
C5 being the total expenses allowed for reimbursement ($1740)
G4 being 80%
...

Are you dealing with cumulative expenses? So the first $500 of
cumulative expenses are reimbursed 100%, the next $1250 of cumulative
expenses are reimbursed 80%, and any cumulative expenses in excess of
$1750 (500 + 1250 = 1750, not 1740 - your comment for the C5 cell
looks like a typo) isn't reimbursed at all?

If G10+B11 is cumulative expenses through row 11, then does column B
contain separate expenses and column G cumulative expenses? If so, why
not use G11 instead of G10+B11?

Anyway, cumulative reimbursement on cumulative expenses G10+B11 would
be

=MIN(G10+B11,500)+MAX(0,MIN(G10+B11-500,1250))*G4

If these cumulative reimbursement formulas were in, say, column X,
this formula would be in cell X11. If you wanted incremental
reimbursements as well as cumulative reimbursements, then you could
use column Y for incremental reimbursements, and the incremental
reimbursement in row 11 would be in cell Y11 and the formula would be

=X11-X10