Split larger number into smaller numbers below 10.000

[PrinceNai]

Dear All,

I'd like to automatically split any number larger or equal to 10.000 into smaller random numbers, rounded to hundreds (the input never has tens or ones other than 0 and is never less than 10.000). So, if a user enters 11.100, to let's say 8.000 and 3.100. If they enter 20.000, into three smaller numbers, if 160.000 into 17 numbers and so on. Those smaller numbers would then be inserted into a table. It is also acceptable to have a small list of numbers (6.000,...,9.000) from which the function picks the first numbers, to speed things up. How would one do that?

Note that the point represents a thousands separator

With kindest regards,
Samo

 

[Josef P.]

X(1 to n-1) = ( (NumberToSplit \ 1000) \ n ) * 1000
Xn = NumberToSplit - X1 * (n-1) = NumberToSplit - ( (NumberToSplit \ 1000) \ n ) * 1000 * (n-1)

11.100 => 2 numbers (n = 2)
X1 = ( (11100 \ 1000) \ 2 ) * 1000 = 5000
X2 = 11100 - 5000 = 6100

20000, n = 3
X1,2 = ( (20000 \ 1000) \ 3 ) * 1000 = 6000
X3 = 20000 - 6000 * 2 = 8000

You can use this logic in VBA or SQL.

/edit:
However, these are not random numbers. I have only used the original (now changed) example data.

 

[CJ_London]

First decide what basis you are deciding the number of random numbers required

next the max size a random number can be for example would a random number of 15500 be ok for your 160000 number

and also you need to decide whether a random number can be repeated either within the same ‘set’ or group if processing more than one number at a time

 

[ebs17]

How would one do that?

1) First have a plan with reliable rules
2) work through this plan and implement it in programming

So, if a user enters 11.100, to let's say 5.000 and 6.100

What is the rule for this exactly?
There are probably hundreds of variations, what do you choose?

If they enter 20.000, into three smaller numbers, if 160.000 into 17 numbers and so on

RequiredNumbers = NumberToSplit \ 10000 + 1

That would be a first step. But what values should these numbers have?

 

[PrinceNai]

Thank you for the ideas so far. After reading them I was at least able to form a clearer picture of how the results should look like.

1. The numbers may repeat, but ideally they should not all be the same (the last one isn't, since it is a remainder)
2. The number of smaller numbers isn't limited or fixed by the input, but it should be reasonable, meaning that the smallest generated number should be say 5.000. Maybe a list (for example 5.000, 6.000, 7.000, 8.000, 9.000) and the function picks from those for as long as a remainder is bigger or equal to 10.000.

 

[jdraw]

Samo,

This seems to be an interesting calculation and you have some suggestions. I'd like to know what this is achieving in business terms. Perhaps you could describe what this/these numbers represent and how they will be used. Just curious.

 

[GPGeorge]

Samo,

This seems to be an interesting calculation and you have some suggestions. I'd like to know what this is achieving in business terms. Perhaps you could describe what this/these numbers represent and how they will be used. Just curious.

I probably watch too many reality shows on crime, but my mind immediately went to generating amounts of currency less than $10,000 US to avoid customs limits on currency travelers can carry without having to disclose it. It addresses the question: How many people are needed to transport each equal tranche?

But that's way too cynical, no doubt. There must be another reason, probably to do with manufacturing.

 

[ebs17]

Maybe a list (for example 5.000, 6.000, 7.000, 8.000, 9.000)

If this list is a table T with field X, one could take the following approach:
You create a query using VBA that combines the existing numbers using a CROSS JOIN. The number of table instances of T results from the calculation shown above. Below is example 3:

PARAMETERS
   NumberToSplit Long
;
SELECT TOP 1
   T1.X AS X1,
   T2.X AS X2,
   T3.X AS X3
FROM
   T AS T1,
   T AS T2,
   T AS T3
WHERE
   RowSum(T1.X, T2.X, T3.X) = NumberToSplit
ORDER BY
   Rnd(T1.X)

Public Function RowSum(ParamArray A() As Variant) As Long
    Dim i As Long
    Dim S As Long
    
    S = A(0)
    For i = 1 To UBound(A)
        S = S + A(i)
    Next
    RowSum = S
End Function

 

[MajP]

Public Function GetRandomToHundreds(BigNumber As Long, MinSize As Integer, MaxSize As Integer) As Collection
  Dim colOut As New Collection
  Dim RunningSum As Long
  Dim complete As Boolean
  Dim newNum As Integer
  Randomize
  If BigNumber > 10000 And (BigNumber \ 100 = BigNumber / 100) And MinSize < MaxSize
    Do
      newNum = GetRandomInRange(CDbl(MinSize), CDbl(MaxSize)) ' gives a number between the min and max size
      newNum = Round(newNum / 100) * 100  'rounds the number to 100s
      If BigNumber - (RunningSum + newNum) >= (MinSize) Then 'Have to be able to split the number twice
        colOut.Add (newNum)
        RunningSum = RunningSum + newNum
       If BigNumber - RunningSum < (2 * MinSize - 1) Then
          newNum = BigNumber - RunningSum
          RunningSum = RunningSum + newNum
          colOut.Add newNum
       End If
      End If
    Loop Until RunningSum = BigNumber
Else
   MsgBox "Input must be > 10,000 and rounded to 100", vbInformation, "Invalid Input"
End If
  Set GetRandomToHundreds = colOut
End Function

Public Function GetRandomInRange(ByVal MinRange As Double, ByVal MaxRange As Double) As Double
  If MinRange < MaxRange Then
    GetRandomInRange = Int((MaxRange - MinRange + 1) * Rnd) + MinRange
  End If
End Function

Public Sub Subtest()
  Dim mynum As Long
  Dim i As Integer
  Dim mycol As Collection
  Dim RunningSum As Long
  mynum = 125000
  Set mycol = GetRandomToHundreds(mynum, 500, 5000)
  For i = 1 To mycol.Count
    RunningSum = RunningSum + mycol(i)
    Debug.Print "(" & i & ") " & mycol(i) & " runningsum " & RunningSum
  Next i
End Sub

This code allows you to pass the min and max size of chunks (I passed 500, 5000).
I did not assume you wanted to do this in a query.

(1) 4200 runningsum 4200
(2) 1800 runningsum 6000
(3) 1000 runningsum 7000
(4) 1400 runningsum 8400
(5) 3400 runningsum 11800
(6) 4800 runningsum 16600
(7) 4900 runningsum 21500
(8) 1100 runningsum 22600
(9) 2200 runningsum 24800
(10) 1700 runningsum 26500
(11) 4100 runningsum 30600
(12) 4200 runningsum 34800
(13) 1600 runningsum 36400
(14) 1100 runningsum 37500
(15) 600 runningsum 38100
(16) 4500 runningsum 42600
(17) 1100 runningsum 43700
(18) 2400 runningsum 46100
(19) 2400 runningsum 48500
(20) 800 runningsum 49300
(21) 3600 runningsum 52900
(22) 4300 runningsum 57200
(23) 1600 runningsum 58800
(24) 3800 runningsum 62600
(25) 2800 runningsum 65400
(26) 1000 runningsum 66400
(27) 2900 runningsum 69300
(28) 4200 runningsum 73500
(29) 3600 runningsum 77100
(30) 2800 runningsum 79900
(31) 4100 runningsum 84000
(32) 4200 runningsum 88200
(33) 3100 runningsum 91300
(34) 2800 runningsum 94100
(35) 4800 runningsum 98900
(36) 900 runningsum 99800
(37) 4300 runningsum 104100
(38) 4000 runningsum 108100
(39) 2200 runningsum 110300
(40) 2900 runningsum 113200
(41) 3400 runningsum 116600
(42) 1700 runningsum 118300
(43) 2100 runningsum 120400
(44) 1900 runningsum 122300
(45) 1600 runningsum 123900
(46) 600 runningsum 124500
(47) 500 runningsum 125000

My original post had a parentheses in wrong place, so I updated.
newNum = Int((MaxSize - MinSize + 1) * Rnd) + MinSize
+ minsize should be outside the int to ensure you get the boundaries

 

[jdraw]

I probably watch too many reality shows on crime, but my mind immediately went to generating amounts of currency less than $10,000 US to avoid customs limits on currency travelers can carry without having to disclose it. It addresses the question: How many people are needed to transport each equal tranche?

But that's way too cynical, no doubt. There must be another reason, probably to do with manufacturing.

You could be on to something George. Perhaps newTranche = x mod 10000

 

[Gasman]

As each person is allowed 10k, why break it down?
In Australia is is 10k AUS$, so it appears just a number?
I wonder if it is 10k rupees for India?

 

[CJ_London]

function picks from those for as long as a remainder is bigger or equal to 10.000.

so a value of 11000 must be split into two or more values so you could have 500 and 10500? or 100 and 10900?

 

[MajP]

so a value of 11000 must be split into two or more values so you could have 500 and 10500? or 100 and 10900?

The OP did say

The number of smaller numbers isn't limited or fixed by the input, but it should be reasonable

So I assume they want some "reasonable" sizes. Thus I provide the ability to define the min chunk size and max chunk size.
However, could modify that you provide a percent of number size or number of chunks.

 

[CJ_London]

OP also said

as long as a remainder is bigger or equal to 10.000.