Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please-please-Help - Need to Resolve this - Allocation problem
I have a very pressing need to find a solution to this problem below. Any
help towards this is very much appreciated. I am having trouble finding a formula that will allocate a number to various €śbuckets€ť based on the seniority of these buckets. Let me exemplify: I have operating cash flows of $200 Most senior bucket - 1 - Obligation A: $70 Second senior bucket - 2 - Obligation B: $180 Second senior bucket - 2 - Obiigation C: $60 So, the formula should allocate $200 by order of seniority -- allocating $70 to Obligation A since it is most senior. Then the remainder $130 (i.e $200 - $70) should get allocated to second most senior. Since both Oblig B and C are equally senior, remainder gets allocated to both equally or to the max of the obligation. Which means that B will get $70 and C will get $60. I should also mention that the seniority of obligation is not cell dependent - i.e. the most senior obligation will not always occur in the first cell or any one particular cell. All obligations will be in entered and then user can assign 1 through "n" against the obligation to define which is senior - 1 being the most senior. The following may clarify as well (note B, C, and D are all equally senior). Any help will be much appreciated! Thanks Amount Available for distribution $200 Rank* Name Obligation Amt Paid Formula 1 A $70 $70 ? 2 B $40 $40 ? 2 C $55 $55 ? 3 D $30 $5 ? 2 E $30 $30 ? * User input, random sequence, not dependent on cell €“ i.e. most senior obligation can occur at the very end of table, for example. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please-please-Help - Need to Resolve this - Allocation problem
In your scenario all priority 1's are paid first, then 2's and finally 3's;
in a case with 2-1's the most senior gets 100% with the remaining funds going to the other #1; or if more than one #1 the allocation is divided equally if there are not enough funds to pay off completely? Are/could priorities be set-up by date(s)? "Chunkey Pandey" wrote: I have a very pressing need to find a solution to this problem below. Any help towards this is very much appreciated. I am having trouble finding a formula that will allocate a number to various €śbuckets€ť based on the seniority of these buckets. Let me exemplify: I have operating cash flows of $200 Most senior bucket - 1 - Obligation A: $70 Second senior bucket - 2 - Obligation B: $180 Second senior bucket - 2 - Obiigation C: $60 So, the formula should allocate $200 by order of seniority -- allocating $70 to Obligation A since it is most senior. Then the remainder $130 (i.e $200 - $70) should get allocated to second most senior. Since both Oblig B and C are equally senior, remainder gets allocated to both equally or to the max of the obligation. Which means that B will get $70 and C will get $60. I should also mention that the seniority of obligation is not cell dependent - i.e. the most senior obligation will not always occur in the first cell or any one particular cell. All obligations will be in entered and then user can assign 1 through "n" against the obligation to define which is senior - 1 being the most senior. The following may clarify as well (note B, C, and D are all equally senior). Any help will be much appreciated! Thanks Amount Available for distribution $200 Rank* Name Obligation Amt Paid Formula 1 A $70 $70 ? 2 B $40 $40 ? 2 C $55 $55 ? 3 D $30 $5 ? 2 E $30 $30 ? * User input, random sequence, not dependent on cell €“ i.e. most senior obligation can occur at the very end of table, for example. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please-please-Help - Need to Resolve this - Allocation problem
I set up you data on row 3. in A1 entered Disbursement and C1 entered 200
I sorted the ranks ascending and entered the following formulas d4 =IF(AND(A4=1,C1C4),C4) d5 =IF(AND(A5=2,E4C5),C5) e4 =C1-D4 e5 =E4-D5 and copied formulas in line 5 down. You'll probably need to add more if statements if you need to pay rank 3. If itas more than 7 then use SUMPRODUCT regards Peter "Chunkey Pandey" wrote: I have a very pressing need to find a solution to this problem below. Any help towards this is very much appreciated. I am having trouble finding a formula that will allocate a number to various €śbuckets€ť based on the seniority of these buckets. Let me exemplify: I have operating cash flows of $200 Most senior bucket - 1 - Obligation A: $70 Second senior bucket - 2 - Obligation B: $180 Second senior bucket - 2 - Obiigation C: $60 So, the formula should allocate $200 by order of seniority -- allocating $70 to Obligation A since it is most senior. Then the remainder $130 (i.e $200 - $70) should get allocated to second most senior. Since both Oblig B and C are equally senior, remainder gets allocated to both equally or to the max of the obligation. Which means that B will get $70 and C will get $60. I should also mention that the seniority of obligation is not cell dependent - i.e. the most senior obligation will not always occur in the first cell or any one particular cell. All obligations will be in entered and then user can assign 1 through "n" against the obligation to define which is senior - 1 being the most senior. The following may clarify as well (note B, C, and D are all equally senior). Any help will be much appreciated! Thanks Amount Available for distribution $200 Rank* Name Obligation Amt Paid Formula 1 A $70 $70 ? 2 B $40 $40 ? 2 C $55 $55 ? 3 D $30 $5 ? 2 E $30 $30 ? * User input, random sequence, not dependent on cell €“ i.e. most senior obligation can occur at the very end of table, for example. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please-please-Help - Need to Resolve this - Allocation problem
Ignore my last post, this is done better with a macro. Copy this formula into
a VB Module, ALT + F11, Insert, Module and run the macro from Excel's tool menu Sub T() Dim rnk As Range, lo As Integer, hi As Integer Dim i As Integer, nr As Integer, j As Integer, y As String amnt = 200 y = ActiveCell.Address Set rnk = Application.InputBox("Enter the rank range", " Range of Ranks", y, , , , , 8) y = ActiveCell.Row nr = rnk.Rows.Count + y lo = Application.WorksheetFunction.Min(rnk) hi = Application.WorksheetFunction.Max(rnk) For i = lo To hi For j = y To nr If Cells(j, 1) = i And Cells(j, 3) < amnt Then Cells(j, 4) = Cells(j, 3) Cells(j, 5) = amnt - Cells(j, 4) amnt = Cells(j, 5) End If Next Next End Sub No sorting is required. see below Rank Name Obligation Amt Paid Disbusement 3 D 30 2 B 40 40 90 2 C 55 55 35 2 E 30 30 5 1 A 70.00 70 130 regards Peter "Chunkey Pandey" wrote: I have a very pressing need to find a solution to this problem below. Any help towards this is very much appreciated. I am having trouble finding a formula that will allocate a number to various €śbuckets€ť based on the seniority of these buckets. Let me exemplify: I have operating cash flows of $200 Most senior bucket - 1 - Obligation A: $70 Second senior bucket - 2 - Obligation B: $180 Second senior bucket - 2 - Obiigation C: $60 So, the formula should allocate $200 by order of seniority -- allocating $70 to Obligation A since it is most senior. Then the remainder $130 (i.e $200 - $70) should get allocated to second most senior. Since both Oblig B and C are equally senior, remainder gets allocated to both equally or to the max of the obligation. Which means that B will get $70 and C will get $60. I should also mention that the seniority of obligation is not cell dependent - i.e. the most senior obligation will not always occur in the first cell or any one particular cell. All obligations will be in entered and then user can assign 1 through "n" against the obligation to define which is senior - 1 being the most senior. The following may clarify as well (note B, C, and D are all equally senior). Any help will be much appreciated! Thanks Amount Available for distribution $200 Rank* Name Obligation Amt Paid Formula 1 A $70 $70 ? 2 B $40 $40 ? 2 C $55 $55 ? 3 D $30 $5 ? 2 E $30 $30 ? * User input, random sequence, not dependent on cell €“ i.e. most senior obligation can occur at the very end of table, for example. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please-please-Help - Need to Resolve this - Allocation problem
Either one of Billy's suggestions are excellent...exactly what I was going to
suggest. "Billy Liddel" wrote: Ignore my last post, this is done better with a macro. Copy this formula into a VB Module, ALT + F11, Insert, Module and run the macro from Excel's tool menu Sub T() Dim rnk As Range, lo As Integer, hi As Integer Dim i As Integer, nr As Integer, j As Integer, y As String amnt = 200 y = ActiveCell.Address Set rnk = Application.InputBox("Enter the rank range", " Range of Ranks", y, , , , , 8) y = ActiveCell.Row nr = rnk.Rows.Count + y lo = Application.WorksheetFunction.Min(rnk) hi = Application.WorksheetFunction.Max(rnk) For i = lo To hi For j = y To nr If Cells(j, 1) = i And Cells(j, 3) < amnt Then Cells(j, 4) = Cells(j, 3) Cells(j, 5) = amnt - Cells(j, 4) amnt = Cells(j, 5) End If Next Next End Sub No sorting is required. see below Rank Name Obligation Amt Paid Disbusement 3 D 30 2 B 40 40 90 2 C 55 55 35 2 E 30 30 5 1 A 70.00 70 130 regards Peter "Chunkey Pandey" wrote: I have a very pressing need to find a solution to this problem below. Any help towards this is very much appreciated. I am having trouble finding a formula that will allocate a number to various €śbuckets€ť based on the seniority of these buckets. Let me exemplify: I have operating cash flows of $200 Most senior bucket - 1 - Obligation A: $70 Second senior bucket - 2 - Obligation B: $180 Second senior bucket - 2 - Obiigation C: $60 So, the formula should allocate $200 by order of seniority -- allocating $70 to Obligation A since it is most senior. Then the remainder $130 (i.e $200 - $70) should get allocated to second most senior. Since both Oblig B and C are equally senior, remainder gets allocated to both equally or to the max of the obligation. Which means that B will get $70 and C will get $60. I should also mention that the seniority of obligation is not cell dependent - i.e. the most senior obligation will not always occur in the first cell or any one particular cell. All obligations will be in entered and then user can assign 1 through "n" against the obligation to define which is senior - 1 being the most senior. The following may clarify as well (note B, C, and D are all equally senior). Any help will be much appreciated! Thanks Amount Available for distribution $200 Rank* Name Obligation Amt Paid Formula 1 A $70 $70 ? 2 B $40 $40 ? 2 C $55 $55 ? 3 D $30 $5 ? 2 E $30 $30 ? * User input, random sequence, not dependent on cell €“ i.e. most senior obligation can occur at the very end of table, for example. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down Box problem | New Users to Excel | |||
Importing from Excel Sheet Problem | Excel Discussion (Misc queries) | |||
How do I resolve a problem with sharing a workbook | Excel Worksheet Functions | |||
Creating a dynamic asset allocation chart | Charts and Charting in Excel | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |