Function macro

[Skinman]

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.

 

[Rick Rothstein \(MVP - VB\)]

Unless I am not understanding what you want to do at all, I think there is
something seriously flawed about the disbursement model you have proposed.
Let's examine two examples... 3 people and 5 people... the disbursement
percentage you want are easy to calculate for them... 0.25,01

3 people - $100 disbursement amount
===========================
Rank % $
1 0.25 25
2 0.15 15
3 0.05 5
Total Disbursement = $45

3 people - $100 disbursement amount
===========================
Rank % $
1 0.25 25
2 0.20 20
3 0.15 15
4 0.10 10
5 0.05 5
Total Disbursement = $75

Trust me when I say that it will not take too many additional people before
you are distributing your whole disbursement amount and more. If I am not
mistaken, with 7 people your Total Disbursements will be $105 from your
total amount of $100.

So, given the above, have I misunderstood your intent or not?

Rick

 

[Rick Rothstein \(MVP - VB\)]

Let's try that again, fixing the typos...

Unless I am not understanding what you want to do at all, I think there is
something seriously flawed about the disbursement model you have proposed.
Let's examine two examples... 3 people and 5 people... the disbursement
percentage you want are easy to calculate for them... 0.25, 0.15, 0.05 for 3
people, 0.25, 0.20, 0.15, 0.10, 0.05 for 5 people. Here is what I get for
distributing $100 for each example...

3 people - $100 disbursement amount
===========================
Rank % $
1 0.25 25
2 0.15 15
3 0.05 5
Total Disbursement = $45

5 people - $100 disbursement amount
===========================
Rank % $
1 0.25 25
2 0.20 20
3 0.15 15
4 0.10 10
5 0.05 5
Total Disbursement = $75

Trust me when I say that it will not take too many additional people before
you are distributing your whole disbursement amount and more. If I am not
mistaken, with 7 people your Total Disbursements will be $105 from your
total amount of $100.

So, given the above, have I misunderstood your intent or not?

Rick

 

[Lars-Åke Aspelin]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[Lars-Åke Aspelin]

An alternative function as follows.

In this function I disregard the statement "leaving 70% to be
distributed" and just focus on the "linear mode".
The difference between each rank is constant and the sum of the
disbursements becomes what it becomes, and probably not the given
amount.

Function disbursements2(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 > 1) Or (c > 1)) Then
n = r * c
ReDim d(n)
d(0) = amount * lowest
d(n - 1) = amount * highest
a = (highest - lowest) / (n - 1)
For i = 1 To n - 2
d(i) = amount * (lowest + a * i)
Next i
If c > 1 Then
disbursements2 = d
Else
disbursements2 = WorksheetFunction.Transpose(d)
End If
Else
disbursements2 = CVErr(xlErrNA)
End If
End Function

Select a number of cells and enter the array formula

=disbursements2(100, 0.05, 0.25)

Maybe that is closer to what the OP expects.

Lars-Åke

 

[Rick Rothstein \(MVP - VB\)]

I think the basic problem is with the OP's model... he appears to want to
force a minimum disbursement of 0.05 which increases to a maximum of 0.25
without regard to how these will sum up as the total number of disbursements
increase. I mean, consider the completely degenerated case... if he gave
**everyone** the minimum disbursement of 0.05 (forget about the maximum and
the sliding scale completely), it would take only 20 disbursements to
exhaust the total amount being distributed... the OP said there could be up
to 22 disbursements, so, if he had that many, he would give out more than he
had just using 0.05 for everyone! Add the requirement for a sliding scale to
a maximum of 0.25 and it will simply break down sooner. It just looks like
the model is seriously flawed to begin with and I don't think, given the 8
to 22 disbursement requirement, there is anything that can "save" it.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Our recent postings crossed...

This function is more uniform in its output (it matches what I assumed the
OP wanted in my first posting), but there is always going to be the problem
of the total amount being disbursed. After just 6 disbursements, he has
given out 5 more than the total amount of 100 he had to distribute. The
model the OP wants to follow is simply flawed... and I don't think there is
any way around it.

Rick

 

[Skinman]

Thanks for all the replies, I am working on it
Will get back once I have figured it all out
Skinman.

 

[Skinman]

I'm feeling very foolish now, someone throw a bucket of cold water over me.
My brief was this.
A friend I do some excel work for, owns a synthetic grass manufacturing
and installation business. He wanted to slow staff turnover so he came up
with a quarterly
bonus system. He has 8 full time staff and in peak periods the casuals make
it up to around
the 20 mark. The ranking is on value they provide for the business. He asked
me for a formula
to pay the highest value worker no higher than 5 times the lowest ranked and
suggested a
start point of 5%, incrementing to highest rank. The bonus was a percentage
of his nett quartarly profit.
In a slow quarter this amount would be about 4,000 and peak quarter about
15,000.
I now see that it is not feesable in a linear mode to use the entire bonus
equitably along those lines.
Sorry to put you all out. Once again thanks for all the input.
Skinman

 

[Lars-Åke Aspelin]

Well, for this problem you have to choose which of the restrictions
that are to be obeyed strictly and which that are just
"recommendations"

Here is another function that might suit your conditions better,

Function disbursements4(amount As Double, lowest As Double, maxq 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 > 1) Or (c > 1)) Then
n = r * c
ReDim d(n)
d(0) = amount * lowest
a = (amount / d(0) - n) * 2 / (n * (n - 1)) * d(0)
If (amount / d(0) - n) * 2 / n > (maxq - 1) Then
a = (maxq - 1) / (n - 1) * d(0)
End If
For i = 1 To n - 1
d(i) = d(0) + a * i
Next i
If c > 1 Then
disbursements4 = d
Else
disbursements4 = WorksheetFunction.Transpose(d)
End If
Else
disbursements4 = CVErr(xlErrNA)
End If
End Function

Select a number of cells and enter the formula as an array formula:

=disbursements4(100, 0.05, 5)

It has the following three inputs

1) amount: the maximum amount to disburse
2) lowest: the fraction of amount that goes to the lowest rank
3) maxq: the maxmum times the highest rank is allowed to get compared
to the lowest rank

In this case there will always be a linear increase over the ranks and
the lowest rank will always get "lowest". Those are the hard
conditions.
However, if the number of ranks are very few, not all of the amout
will be disbursed because that would be in conflict with the maxq
condition.
You can play with these inputs and see if this is something that can
be used.
You will find that the total amount is not aways disbursed because
that would be in conflict with maxq.
You will also find that the highest rank will not always get maxq
times the lowest rank because that would be in conflict with the
maximum total amount to disburse.

Some examples:
With amount = 100, lowest = 0.05 (5%), and highest no more than 5
times the lowest, the following will be disbursed for different
number of ranks:

2 ranks: 5, 25 (total 30)
3 ranks: 5, 15, 25 (total 45)
4 ranks: 5, 11.7, 18.3, 25 (total 60)
5 ranks: 5, 10, 15, 20, 25 (total 75)
6 ranks: 5, 8.1, 11.2, 14.3, 17.4, 20.5, 23.6 (total 100)
7 ranks: 5, 7.1, 9.3, 11.4, 13.6, 15.7, 17.9, 20 (total 100)
....
20 ranks: all of them get 5 (total 100)

Above 20 ranks this formula also gives meaningless results if lowest
is 5%.

Hope this helps / Lars-Åke

 

[Skinman]

Thanks Lars-Åke
I really appreciate your time and effort
It does help, I have entered it, and have learnt much from your skill.
Hell ther's a lot to learn though!
Skinman.