I am trying to create a UDF that returns a 1d array. The parameters of the UDF would be two numbers as two parameters and the output should be a 1d array of the result as following
Code: Select all
Function Distribute(ByVal n1 As Double, ByVal n2 As Double)
End Function
-----------
say the two numbers are 30 and 4
The calculation would be step by step 30/4 (=ROUNDUP(30/4,0)) > the result is [8] and the remaining would be 22 and the n2 would be 3
The second step is to divide 22/3 (=ROUNDUP(22/3,0)) > the result is [8] and the remaining would be 14 and the n2 would be 2
The third step is to divide 14/2 ((=ROUNDUP(14/2,0))) > the result is [7] and the remaining would be 7 and the n2 would be 1
if n1 equals 1 so this is the last step and the final result would be [7]
The final result would be a 1d array of the results across the steps: [8, 8, 7, 7]
Mr. Hans helped me before in similar issue
Code: Select all
Function Distribute(total As Long, num As Long)
Dim i As Long
ReDim amounts(1 To num) As Long
For i = 1 To num
amounts(i) = total \ num
Next i
amounts(num) = amounts(num) + total Mod num
Distribute = amounts
End Function