Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Please-please-HELP!!! Need to resolve this - Allocation Formula
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.newusers
|
|||
|
|||
Please-please-HELP!!! Need to resolve this - Allocation Formula
I think you posted this before, you must not have liked my suggestions.
However, here is another, a macro. It assumes that you data starts in C6. You can type A-D in cells A1 to A4 Totals allocated for each are placed in column B, the order count for each in column D, and Column D of the data shows which sales-person is allocated the order. Sub TBonus() Dim sumA As Double, sumB As Double, sumC As Double, sumD As Double Dim countA As Long, countB As Long, countC As Long, countD As Long Dim nr As Long, c As Variant Dim rng As Range Count = 4 nr = Range("c6").CurrentRegion.Rows.Count Set rng = Range(Cells(10, 3), Cells(nr, 3)) 'set the 4 teams basic amounts sumA = CCur(Cells(6, 3)): sumB = CCur(Cells(7, 3)): sumC = CCur(Cells(8, 3)): sumD = Cells(9, 3) countA = 1: countB = 1: countC = 1: countD = 1 Cells(6, 4) = "A": Cells(7, 4) = "B": Cells(8, 4) = "C": Cells(9, 4) = "D" 'find the rest of the values For i = 10 To nr If CCur(sumA) < CCur(sumB) And CCur(sumA) < CCur(sumC) And CCur(sumA) < CCur(sumD) Then sumA = sumA + CCur(Cells(i, 3)) countA = countA + 1 Cells(i, 4).Value = "A" ElseIf sumB < sumA And sumB < sumC And sumB < sumD Then sumB = sumB + CCur(Cells(i, 3)) countB = countB + 1 Cells(i, 4).Value = "B" Debug.Print countB ElseIf sumC < sumA And sumC < sumB And sumC < sumD Then sumC = sumC + CCur(Cells(i, 3)) countC = countC + 1 Cells(i, 4).Value = "C" ElseIf sumD < sumA And sumD < sumB And sumD < sumC Then sumD = sumD + CCur(Cells(i, 3)) countD = countD + 1 Cells(i, 4).Value = "D" End If Next Cells(1, 2).Value = sumA: Cells(2, 2).Value = sumB: _ Cells(3, 2).Value = sumC: Cells(4, 2).Value = sumD Cells(1, 4).Value = countA: Cells(2, 4).Value = countB: _ Cells(3, 4).Value = countC: Cells(4, 4).Value = countD Cells(1, 1).Select End Sub Perhaps this will suit you better. 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 | |||
Find value in array | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions |