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.


"Lars-Åke Aspelin" wrote in message
...
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

On Sat, 9 Aug 2008 08:26:00 +1000, "Skinman"
wrote:

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