Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 527
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find value in array Brook6 Excel Worksheet Functions 26 January 30th 07 10:40 PM
Reusing formula Tony29 Excel Discussion (Misc queries) 7 September 7th 06 03:34 AM
Dynamic Range with unused formula messing up x axis on dynamic graph [email protected] Charts and Charting in Excel 2 February 2nd 06 09:02 PM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM


All times are GMT +1. The time now is 07:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"