@if question

[MrsGixxer]

I have a spreadsheet that I am creating that I would like to auto calculate
except for a few cells. This is the jist of what is happening. We pay 100%
the first $500 of an expense. After the first $500 we pay 80% of the next
$1250. I am tracking these expenses for each person. I would like to only
have to enter the expense amount and then nothing else. I have created most
of the spreadsheet but am having problems with the 80% part of it. 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%

I would like to add an "and" to the formula but am not sure if that is
possible.

Hopefully I made sense. Any help would be greatly appreciated.

 

[Harlan Grove]

. . . 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

 

[Billy Liddel]

I created a simple spreadsheet to test, perhaps you can change the ranges to
suit. The data is below:

Date Person Expences Repay
01/02/2008 Fred 500 500
02/02/2008 Sally 300 300
03/02/2008 Fred 700 1060
04/02/2008 Dave 1200 1060
05/02/2008 Fred 250 1260
06/02/2008 Fred 200 1420
07/02/2008 Fred 400 1500
08/02/2008 Fred 500 1500
09/02/2008 Sally 300 580
10/02/2008 Sally 1200 1500

The formula for repayment in D2 is:
=IF(SUMIF($B$2:B2,B2,$C$2:C$2)<=500,SUMIF($B$2:B2,B2,$C$2:C2)*1,IF(SUMIF($B$2:B2,B2,$C$2:C2)<=1750,(SUMIF($B$2:B2,B2,$C$2:C2)-500)*0.8+500,1500))

And this was copied down.

If the sheet layout is different, apart from the extra columns and you want
only one figure per person change $B$2:b2 to B2:B5000 and $C$2:C2 to C2:C5000
to increase the range

Regards
Peter

 

[MrsGixxer]

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.

 

[MrsGixxer]

Thanks for the response Billy. I'll have to see if your formula will help
when I get back to work tomorrow. The spreadsheet is a little more complex
than what you have but I get the idea of what you are doing. I'll let you
know.
Thanks!

 

[MrsGixxer]

OK - G10+B11 is cumulative expenses. I have to play around with your formula
and look at my work a little differently. Here is more info that might help.

My column headings that matter in the calculations are:

Expense
Current Amount Applied to 100% Reimb
YTD Amount applied to 100% Reimb
current amount applied to 80% reimb
YTD amount applied to 80% reimb
total YTD expenses
Balance of benefit available (this is what is left of the total $1750 that
is able to be expensed)
balance of expenses available to expense (this is what is left of the reimb
that can actually be received)

I need all the columns because the benefit could be used with the very first
expense or it can take several expenses to use it. Each line needs to be
able to calculate either scenario.