Sum of 15 random +/- integers always greater than or equal to zero

[MikeM]

Howdy:
What I'd like to do is the following:
Generate a column of 15 random integers from -99,999 to +99,999
The sum of these 15 random integers must be greater than or equal to zero.

Thanks.

Mike

 

[Jerry W. Lewis]

If they were independent identically distributed discrete uniform random
integers, then the probability of a negative sum would be nearly 0.5. How do
you want to constrain the "randomness" to ensure that the sum is not
negative? Some possibilities if the 15th value produces a negative sum would
include,
- replace the 15th value with -SUM(fourteenValues)
- throw it away and draw again until the sum is non-negative
....
Note that the 1st option can result in a value larger than 99,999, in which
case the 2nd option would never terminate.

Jerry

 

[MikeM]

Hi Jerry:
Thanks for responding.
I suppose I was thinking along the lines of generating the first random
integer in cell B4. Then, the next step would be to generate a random
integer in cell B5. The code would then SUM B4:B5 and ascertain if the
result were >= zero. If yes, it would proceed to the next cell in the range,
B6. If no, it would generate a new random value for B5 and apply the same
test again until it was successful. For each successive random value the SUM
test range would expand to include the new value.

I think that's what you were saying in your choice of constraints:
"- throw it away and draw again until the sum is non-negative"

Hope this helps. And thanks for your interest!

Mike

 

[Peter T]

Hi Mike,

If (?) this is right there's is a very small possibility the Do loop could
run many times

Sub test()
Dim i As Long, n As Long
Dim nArr(1 To 15, 1 To 1) As Long ' 2D to dump to cells maybe
Dim nTmpSum As Long, nSum As Long
Const cMinMaX As Long = 999999 * 2

Dim testCounter As Long

Do
For i = 1 To 15
nArr(i, 1) = cMinMaX * (Rnd() - 0.5)
nTmpSum = nTmpSum + nArr(i, 1)
Next
nSum = nTmpSum
nTmpSum = 0
testCounter = testCounter + 1
Loop Until nSum >= 0

Debug.Print nSum, testCounter & " Do-loop(s)"
'Range("A1:A15").Value = nArr

End Sub

Regards,
Peter T

 

[Gary Keramidas]

tom:

what is v1?

v1(v(i)) = v1(v(i)) + 1

 

[Peter T]

Typo -

change
Const cMinMaX As Long = 999999 * 2 to
Const cMinMaX As Long = 99999 * 2

Peter T

 

[Bernd P]

Hello Mike,

I suggest just to draw 15 integers, sum them up and take their
negative values if the sum is < 0:

Enter into A1:
=SUM(A2:A15)

Array-enter into A2:A15:
=INT(RAND()*199999-99999)

Array-enter into B1:B15:
=IF(A1<0,-A1:A15,A1:A15)

And if your random integers have to be unique look for my UDF
UniqRandInt.

Regards,
Bernd

 

[MikeM]

Hi Tom:
Thanks very much for your post.
When I run your code, I get a compile error: 'Sub or Function not defined'
for v1 in the following line:
v1(v(i)) = v1(v(i)) + 1

I apologize, but I don't have enough VBA experience to debug the code. I'm
guessing it's a simple fix...

Mike

 

[MikeM]

Peter:
Thanks for your taking the time to come up with an answer.
It works great!

Mike

 

[MikeM]

Bernd:
Thanks for an interesting formula solution to this question.
I know I was trying to come with one and couldn't quite get there.

The only thing is, the final array of integers should include some negative
values.
Thanks again for your interest.

Mike

 

[Bernd P]

Hello Mike,

Did you try?

It does.

Regards,
Bernd

 

[MikeM]

Hi Bernd:
Yes, I did try it; perhaps I am doing something wrong:
I'm entering the array formulas by CTRL+SHIFT+ENTER and dragging down to
fill the range. Is this correct?
My column B always consists of positive numbers.

Thanks again.

Mike

 

[Peter T]

I'm entering the array formulas by CTRL+SHIFT+ENTER and dragging down to

fill the range. Is this correct?

For this particular array formula you would start by selecting the entire
range, enter or paste the formula in the input bar, then array enter.
However, unless I'm missing something, Bernd's instructions are not correct.

Non-array method -

A1=INT(RAND()*199999-99999)
B2=IF($A$16>=0,A1,-A1)

Copy A1:B1 down to A15:B15
A16=SUM(A1:A15)
B16=SUM(B1:B15)

Array method -

Select A1:A15, in the input bar type or paste
=INT(RAND()*199999-99999)
array enter, ctrl-shift, enter

Select B1:B15, in the input bar type or paste
=IF($A$16>=0,A1:A15,-A1:A15)
array enter

In A16 & B16 sum the cells same as non array method above

Press F9 repeatedly, look at A16:B16

Regards,
Peter T

 

[Bernd P]

Hello Peter,

What does not work for you?

I thought it was clear if I say array-enter into A2:A15 that you
select A2:A15 (all 14 cells) and then enter the formula with CSE.

Regards,
Bernd

 

[Peter T]

Hi Bernd,

You are right, my apologies!

What threw me was your method as posted works for 14 numbers rather than the
15 per OP's request. I failed to look at B1 as being in effect the absolute
value of A1 and also the sum of the 14 cells B2:B15, in turn flipped as
necessary.

It was cleverer than I realized, in a confusing kind of way <g>

FWIW, a VBA approach based on the same method, I think better than the 'Loop
Until' posted previously -

Sub test2()
Dim i As Long
Dim nArr(1 To 15, 1 To 1) As Long ' 2D to dump to cells maybe
Dim nSum As Long
Const cMinMaX As Long = 99999 * 2

For i = 1 To 15
nArr(i, 1) = cMinMaX * (Rnd() - 0.5)
nSum = nSum + nArr(i, 1)
Next

If nSum < 0 Then
For i = 1 To 15
nArr(i, 1) = -nArr(i, 1)
Next
nSum = -nSum
End If

Range("A1:A15").Value = nArr
Range("A16") = nSum
End Sub

Regards,
Peter T

 

[Bernd P]

Hi Peter,

Np. Actually I think we should use your non-array approach.

BTW: To be precise I think we are loading the dice here somewhat:
Combinations which do not sum up to zero have double chance to come
true compared to those who sum up to zero. It doesn't matter to much
with 15 draws of +/-99999 but if we had 3 draws with +/-2 we could see
it...

Have fun,
Bernd

 

[Peter T]

BTW: To be precise I think we are loading the dice here somewhat:

Indeed, my VBA routines too - in particular due to the lazy way of coercing
(rounding vs Int) decimals to Longs, the extreme +/-99999 has a 50% lower
probability of coming up than any other number.

But no-one is loading more than the OP, kind of 'I don't like that set,
roll'em again'.

Time to get into the casino business!

Regards,
Peter T