Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
@if question
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
@if question
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
@if question
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" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
@if question
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
@if question
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! "Billy Liddel" wrote: 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" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
@if question
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 a 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. "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
VBA Question | Excel Discussion (Misc queries) | |||
question | Excel Worksheet Functions |