Yes, that's exactly what I tried to say, in the first three lines as
well as in the three last lines of my post.
Maybe I did not understand the problem correctly.
What my formula does is to distribute what is left after the lowest
rank and the highest rank have got their share.
In the example there is 70% to distribute amongst the mid ranks.
I also try to implement an "linear increase" of the amount given
to the different ranks, That works fairly well if the number of ranks
is small enough, but it breaks down when the number of ranks is over a
certain limit. In the example the limit is 16 (14+2) ranks.
Lars-Åke
On Fri, 8 Aug 2008 13:53:47 -0400, "Rick Rothstein \(MVP -
VB\)"
wrote:
Your function gets kind of "strange" at 16 or more selected cells. For 16
selected cells, every cell is 5 except the last one which is 25.... at 17 or
more selected cells, it looks like the amounts decrease as the row number
increases (whereas with 15 or less cells selected, the amounts always
increased with increasing row numbers).
Rick
"Lars-Åke Aspelin" wrote in message
.. .
On Fri, 8 Aug 2008 22:49:49 +1000, "Skinman"
wrote:
Hi All,
Using excel 2007 on Vista and would like to make a macro function
to do this.
A= Amount for disbursement
R= Number of rank numbers (usually between 8 to 22)
B = A*5% to goto the lowest rank
C = A*25% to goto the highest rank
The balance of A to be distributed in a linear trend over the remaining
Ranked numbers.
In a simplified explanation say "A" = 100, "B" = 5 (5% of 100) "C" = 25
(25%
of 100)
So that takes 30 of the 100 leaving 70 (70%) to be distributed in a linear
mode.
The second lowest ranked number would be slightly larger then 5% whilst
the
second highest ranked number would be slightly lower than 25%
I would round down to 1 decimal place.
I was thinking along the line of an array function but can't get my head
around it.
On my spreadsheet I would like to enter in D2 the amount and in
E2 the number of ranked numbers, then for the function macro to be
addressed
from
another macro for placement of results.
Any pointers please. I can't find a built in function that will do this.
Skinman.
If you have 70% to be divided amongst more than 14 ranks, besides the
lowest and the highest rank, at least one of them will get less than
5% which is less than what goes to the lowest rank.
That means that there is a limitation on the number of ranks given the
disbursement to the lowest and highest ranks.
Lets give
X1 to the lowest rank
XN to the highest rank
Xi = X1 + a * (i-1) for the middle ranks where i = 2 to N-1
a is the linear constant to be calculated.
The sum of the disbursement to all ranks is then
X1 + XN + " sum where i goes from 2 to N-1 of ( X1 + a*(i-1) ) "
This should be equal to 1 (or 100%) (A can be applied later)
"sum..." = 1 - X1 - XN
X1 * (N-2) + a * (N-1) * (N-2) / 2 = (1 - X1 - XN)
a = 2*(1 - X1 - XN - X1 * (N-2)) / ( (N-1) * (N-2) )
If N = 8 this becomes
a = ( 1- XN - 7*X1 ) / 21
With you example values of XN = 25% and X1 = 5% we get
a = 0.4/21 = 0.019047619...
and finally you get
X2 = 6.9%
X3 = 8.8%
X4 = 10.7%
X5 = 12.6%
X6 = 14.5%
X7 = 16.4%
Here is a UDF that you can try to get this result in adjacent cells
Function disbursements(amount As Double, lowest As Double, highest As
Double) As Variant
Dim d() As Double
r = Application.Caller.Rows.Count
c = Application.Caller.Columns.Count
If ((r - 1) * (c - 1) = 0) And ((r 2) Or (c 2)) Then
n = r * c
ReDim d(n)
d(0) = amount * lowest
d(n - 1) = amount * highest
a = 2 * (1 - lowest - highest - lowest * (n - 2)) / ((n - 1) * (n -
2))
For i = 1 To n - 2
d(i) = amount * (lowest + a * i)
Next i
If c 1 Then
disbursements = d
Else
disbursements = WorksheetFunction.Transpose(d)
End If
Else
disbursements = CVErr(xlErrNA)
End If
End Function
To get the result on the worksheet select some cells (more than two)
in a row or in a column and
enter the following as an array formula. i.e with CTRL+SHIFT+ENTER
rather than just ENTER
=disbursements(100, 0.05, 0.25)
Note that you will get strange results if the are too many ranks
compared to what is left after the highest and lowest ranks have got
their share.
Hope this helps / Lars-Åke