Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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 "Skinman" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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 "Lars-Åke Aspelin" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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 "Lars-Åke Aspelin" wrote in message ... 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 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
Thanks for all the replies, I am working on it
Will get back once I have figured it all out Skinman. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sub Macro vrs Function Macro Auto Start | Excel Discussion (Misc queries) | |||
Macro For If Function | Excel Programming | |||
How Do I (Macro Function)? | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Function or macro help | Excel Programming |