How to generate n random numbers between -100 to 100 in Excel which sum to zero?

Question

How to generate n random numbers between -100 to 100 in Excel which sum to zero? Like I want 100 -50 -50 30 -30

basically random numbers that net to 0

Then they're not really random, are they? Is `n` pre-determined or also random? Are you looking for a formula or will VBA be OK? — cybernetic.nomad, Apr 05 '19 at 18:30
Possible duplicate of [Generate N random numbers whose sum is a constant K - Excel](https://stackoverflow.com/questions/21782329/generate-n-random-numbers-whose-sum-is-a-constant-k-excel) — JvdV, Apr 05 '19 at 18:42

David · Accepted Answer · 2019-04-06T08:46:29.563

1

Razvan's answer in the linked thread is excellent. Otherwise I'm not sure there is a literal answer, but for fun, try this...

Enter this formula in cells A1:T50:

=RANDBETWEEN(-100,100)

Enter this sum formula into cell U51:

=SUM(A1:T50)

Now make sure you can see cell U51. Save the file with the below macro (and save all other work) and call the macro, making sure you leave the sheet active (don't switch to other software):

Sub sumZero()
    Application.Calculation = xlCalculationAutomatic
    Do While Range("U51").Value <> 0
        Range("U51").Formula = "=SUM(A1:T50)"
    Loop
End Sub

My low power laptop takes between 5 seconds to 3 minutes to arrive at a set of randomly picked data that sums to zero.

edited Apr 06 '19 at 08:46

answered Apr 05 '19 at 18:55

David

1,222
1
8
18

I'll give a vote :), like the shorter code. Arguments about relative speed to follow... – Solar Mike Apr 06 '19 at 08:48
1

Thanks :) ha ha yes... but sometimes the slowest trees bear the sweetest fruit, right?!... – David Apr 06 '19 at 08:58

score 1 · Answer 2 · answered Apr 05 '19 at 20:00

In cell B1 enter the formula:

=SUM(A:A)

The run this short macro:

Sub dural()
    Dim N As Long, A As Range
    Set A = Range("A:A")

    N = Application.InputBox(Prompt:="Enter number of samples", Type:=1)
    A.Clear
    Range("A1:A" & N - 1).Formula = "=RandBetween(-100, 100)"

    Do While Abs(Range("B1")) > 100
        Calculate
    Loop

    A.Value = A.Value
    Range("A" & N).Value = -Range("B1").Value
End Sub

The macro will ask you for the number of samples you need and then fill column A with N-1 samples. The N^th is calculated to drive the sum to zero.

How to generate n random numbers between -100 to 100 in Excel which sum to zero?

2 Answers2