Ask Your Question

I Want to generate randomnly 9 numbers in ExcelSheet cells 3X3 out of numbers

asked 2020-08-10 07:43:52 +0200

JPMAKWANA gravatar image

updated 2020-08-11 02:34:10 +0200


edit retag flag offensive close merge delete


Please don't use capitals, it's like shouting in the web.

And I think there are some threads about this. Like How to generate random non-repeating cells from a starting range?

m.a.riosv gravatar imagem.a.riosv ( 2020-08-10 09:17:24 +0200 )edit

without and duplicate are not tags, but essential parts of the question.

LeroyG gravatar imageLeroyG ( 2020-08-10 19:34:27 +0200 )edit

5 Answers

Sort by » oldest newest most voted

answered 2020-08-11 00:20:30 +0200

keme gravatar image

updated 2020-08-11 01:06:40 +0200

You can simulate the process of such a "multiple draw" by removing the drawn elements in succession, as demonstrated in the excellent example by @Mike Kaganski. This ensures a random "even distribution" probability, but makes for a fairly complex formula structure. Easily done in a procedural language, but in the functional language of spreadsheets it gets fairly bulky and/or convoluted. Kudos to MK for laying this out in a coherent and comprehensible manner. It is not easily accomplished.

A different approach which, as far as I can tell, yields the same end result with a fairly simple structure, and which is easily extendable:

  • Generate a list of 36 RAND() numbers.
    Due to the nature of the pseudorandom numbers generated, there will not be identical numbers in the list (like there might be, though highly unlikely, with truly random numbers).
  • Extract the RANK() of the first 9 (or any 9) numbers in the list, and use those ranks as your drawn numbers
    When the distribution of pseudorandom numbers is even, the probability distribution of those ranks should also be even. As long as the numbers are all different, the ranks within the range will list every number from 1 to 36 exactly once, in random order.

See attached file, where the extensibility is also shown.

edit flag offensive delete link more

answered 2020-08-10 21:53:25 +0200

updated 2020-08-10 23:41:14 +0200

You need first 9 elements of a sequence generated by sequentially picking and removing a random number out of 1..36.

Put this to A1 (use Ctrl+Shift+Enter to finish entering the formula, to create an array formula):


Put this to A2 (use Ctrl+Shift+Enter to finish entering the formula, to create an array formula):


Then select A2, copy to clipboard, select A3:A9, and paste.

What does the formulas do? The first one simply picks a random number between 1 and 36. It could of course be simplified to just


but I made it to resemble the rest of formulas in the column, for uniformity (as possible).

The second and following formulas generate a sequence of numbers from 1 to 36 (using ROW(A$1:A$36)), and make those elements in this sequence that already appear above to be 0 (multiplying the number by boolean COUNTIF(A$1:A1;ROW(A$1:A$36))=0 in array mode). So let's imagine A1 was 12. The sequence from ROW(A$1:A$36)*(COUNTIF(A$1:A1;ROW(A$1:A$36))=0) would be

1 2 3 4 5 6 7 8 9 10 11 0 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

(because for 12, COUNTIF(A$1:A1;12)=0 would return FALSE, i.e. 0). The sequence has exactly 35 distinct values greater than 0; and given that we are calculating 2nd value, and only 1 value is above, the upper argument to RANDBETWEEN would be 35. Now we get a number of random rank varying from 1 to 35 from this sequence using LARGE, never picking zero values.

Now you have these nine numbers. If you need to put them into a 3x3 frame, just put this to B1:


and drag-copy to B3; put this to C1:


and drag-copy to C3. Now you may hide rows 4-9.

This method might be extended when you have some specified values, not necessarily 1..36, and not even necessarily contiguous. In that case, the numbers returned by the formulas should be considered indices into the array of the explicitly given numbers.

edit flag offensive delete link more


A10:A36 must remain empty?

LeroyG gravatar imageLeroyG ( 2020-08-10 22:51:58 +0200 )edit

@LeroyG: Why? you may extend the formula down to row 36 (e.g., to check correctness 😉). It's just unnecessary for the task of generation of 9 numbers :-)

Mike Kaganski gravatar imageMike Kaganski ( 2020-08-10 23:00:43 +0200 )edit

@Mike Kaganski, I just tried with your answer, and wrote some data in A10:A36 with no adverse results. I asked, because the formula references until A36.

LeroyG gravatar imageLeroyG ( 2020-08-10 23:42:54 +0200 )edit

Formula only references those cells in context of ROW function, which returns the row number of the referenced cell irrespective of the cell's content.

Mike Kaganski gravatar imageMike Kaganski ( 2020-08-10 23:46:55 +0200 )edit

answered 2020-08-10 08:46:17 +0200

JohnSUN gravatar image

updated 2020-08-11 13:20:30 +0200

This is one of the many possible options.

Since we need to distribute 36 values ​​between 9 cells, it is enough to select for each cell its own range no longer than 36/9 = 4 and we have to take care not to duplicate values.

From 1 to 36.png

With this method, the value 36 will appear very, very rarely.

You can use another method. Now for the first cell there are almost no restrictions, it can take any value from 1 to 28. And for the rest of the cells there are fewer and fewer acceptable values.

From 1 to 36 _ 2.png

With this method, you can get the sequence 28,29, .. 35,36 much more often than 1,2,3,4,5,6

There are other ways to generate random values. The choice of the best one depends on the task that is presented to the random number generator.

Update Since additional questions and solutions arose, I conducted a small analysis of the proposed algorithms.

The two solutions proposed above have the following distribution


If we start filling in 9 desired values ​​from the middle of the list (from the fifth element) and change the step of changing the parameters, then we can achieve such results with the same RANDBETWEEN() function


The last diagram compares the @Mike Kaganski, @LeroyG and macro variants - all three are nearly identical.



Function getShuffled(iStart As Integer, iFinish As Integer, _
                    hSize As Integer, vSize  As Integer) As Variant 
Dim aTemp As Variant, aRez As Variant
Dim  i As Integer, j As Integer, k As Integer
Dim iSh As Integer, jSh As Integer, kSh As Integer
Rem Create index array
    ReDim aTemp(iStart To iFinish)
    For i = iStart To iFinish
        aTemp(i) = i
    Next i
Rem Shuffle it
    For iSh = iStart To iFinish
        jSh = Rnd() * (iFinish - iStart) + iStart
        kSh = aTemp(iSh)
        aTemp(iSh) = aTemp(jSh)
        aTemp(jSh) = kSh
    Next iSh
Rem And shuffle again, backward
    For iSh = iFinish To iStart Step -1
        jSh = Rnd() * (iFinish - iStart) + iStart
        kSh = aTemp(iSh)
        aTemp(iSh) = aTemp(jSh)
        aTemp(jSh) = kSh
    Next iSh
Rem Array for result
    ReDim aRez(1 To vSize, 1 To hSize)
    k = iStart
    For j = 1 To vSize
        For i = 1 To hSize
            If k > iFinish Then
Rem For the resulting array, more numbers were needed than
Rem are in the original set - reshuffle additionally            
                For iSh = iStart To iFinish
                    jSh = Rnd() * (iFinish - iStart) + iStart
                    kSh = aTemp(iSh)
                    aTemp(iSh) = aTemp(jSh)
                    aTemp(jSh) = kSh
                Next iSh
                For iSh = iFinish To iStart Step -1
                    jSh = Rnd() * (iFinish - iStart) + iStart
                    kSh = aTemp(iSh)
                    aTemp(iSh) = aTemp(jSh)
                    aTemp(jSh) = kSh
                Next iSh
                k = iStart
            aRez(j, i) = aTemp(k)
            k = k + 1
        Next i
    Next j
    getShuffled = aRez
End Function

Call it like as

edit flag offensive delete link more


hello @JohnSUN: imho none of your receipts produces random numbers (despite using rand functionality), you mentioned yourself that 36 (and other high numbers) will be under-represented in 1. ver., in second ver. there will be too few small numbers as each subsequent number is limited to the range above the max of the former numbers,

and ... your numbers will be sorted, what is normally not the case for random numbers ...

newbie-02 gravatar imagenewbie-02 ( 2020-08-10 13:03:19 +0200 )edit

The thing is, @JPMAKWANA spent too few words on the task description. Therefore, it seems to me that it makes no sense to require a random order of numbers in the resulting table. As I mentioned in my answer, there are many different solutions for similar tasks. We can achieve very different distributions of numbers in the matrix simply by changing the parameters of the RANDBETWEEN() function. We can get almost any result with a macro. But we will not be able to accurately answer the incompletely asked question.

(I don't mind your statement "none of your receipts produces random numbers" because I don't understand what you are writing about.)

JohnSUN gravatar imageJohnSUN ( 2020-08-10 13:31:14 +0200 )edit

@JohnSUN: the values generated according to your recipe are not 'randomly evenly' distributed over the number range from 1 to 36, but accumulate in the lower part of this range in one version and in the upper part in the other version, this doesn't have to be immediately noticeable, but if you repeat the attempt more often you will notice it, and someone who - e.g. in a lottery - prefers to bet on the 'more frequentet numbers' would have better chances of winning, thats what i meant with 'not random' ...

newbie-02 gravatar imagenewbie-02 ( 2020-08-10 14:48:50 +0200 )edit

Oh, you didn't mean not "not random" but "not normally distributed"? Didn't I mention this feature of the proposed options? Can you argue that the author of the question is not looking for just such a solution - shifted to one of the edges? Are you sure this is a lottery game, not a fireworks design?

JohnSUN gravatar imageJohnSUN ( 2020-08-10 14:57:55 +0200 )edit

@JohnSUN: no, sorry, careful, 'normally distributed' is a special spreading around a median with a 'belly' curve to both sides - afaik, that's also 'not random',

really 'random numbers' as for a lottery should be ... random ... not all the same, distributed in the desired range, and - for large quantities - so evenly distributed that a prediction is not possible, also no prediction like 'probably higher numbers are more likely to occur', this is not fulfilled by your methods,

a random number between 1 and 36 should have the same 1/36 chance for every draw, your samples have smaller restrictions which numbers are possible or 'likely' for each cell,

the special lottery case - where a drawn number is not given back to the pool - (no duplicates) looks like having only 1/35 of each leftover chance for the second draw, but as you have to consider 36 different cases for the ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-08-10 16:42:28 +0200 )edit

@JohnSUN: very nice work!, :-) :-) :-) , yes, that's it, a 'random functionality' should hold in a test of the distribution of the results ... have seen a test shortly that nearly no! OS passes this test with it's rand funcion if you go deeper and evaluate predictability, linux was among the best!

would like upvoting to honour your efforts, but the other answers are easier for new questioners searching a solution,

newbie-02 gravatar imagenewbie-02 ( 2020-08-12 09:27:19 +0200 )edit

answered 2020-08-10 21:20:00 +0200

LeroyG gravatar image

My answer based on how I understand the question.

Given 36 whatever numbers in a list, select randomly 9 numbers, and show them in a 3×3 cells range.

See sample file.

  • A2:A37 contains an ordered list from 1 to 36.
  • B2:B37 contains all 36 numbers to choose from.
  • D2:F4 is the 3×3 cells range in which to show the 9 numbers chosen: =VLOOKUP(INT(RAND()*36+1);$A$2:$B$37;2;0).
  • use =MODE(D2:F4) to visually evaluate if there are repetitions, press F9 until formula show #VALUE!.
  • copy D2:F4 and paste as unformatted text (choose menu Edit - Paste Special - Paste Unformatted Text or Ctrl+Shift+Alt+V) in a separate range to not lose the formula.

More LibreOffice Help on RAND, INT, VLOOKUP, and MODE.

If you are working with LibreOffice since version 7, instead of RAND, use RAND.NV. You will need to select D2:F4 to recalculate the values (with F9), but will no more need to do the last step; NV stand for non-volatile.

Press edit below your question if you want to add more information; also can comment an answer (Add Answer is reserved for solutions).

Check the mark (Correct answer mark) to the left of the answer that solves your question.

edit flag offensive delete link more

answered 2020-08-10 09:04:16 +0200

newbie-02 gravatar image

you need not shout / cry (capitals) for that, save it for important news like EX$EL SPREADS CORONA or similar,


  1. produce some random values with "=INT(RAND()*36+1)",
  2. copy and paste their values (not formula) with strg-c shift-strg-v,
  3. apply advanced filter to that range, the range itself as filter criteria, options 'no duplicates', 'copy output to',
  4. for a 3x3 array copy first 9 cells from that output with "=",
  5. looks you are done,
  6. left: check mathematical correctness,
  7. left: automate steps,
  8. left: transfer to ex$el,
  9. left: care to produce enough input in step 1.,
  10. limitation: as there is no limit how many identical valus the rand() function may produce in a row the procedure may fail with any amount of input,

alternatively: watch the lottery of your choice with at least 9 pulls from at least 36 chances and note the first 9 fallen numbers below 37 into a sheet, if you are unlucky and get too few numbers (too many values above 36) continue next week and manually eliminate duplicates,

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-08-10 07:43:52 +0200

Seen: 387 times

Last updated: Aug 11 '20