(probably simple) excel random number question

  pookie 18:04 02 Aug 2012

Good afternoon,

Excel 2010. Do you know a formula I can use for generating bingo numbers? I want it to randomly draw numbers from 1-89 but it can't randomly select numbers already drawn.

I hope that makes sense.

Thank you.

  Simsy 20:27 02 Aug 2012

this formula; =ROUNDUP(RAND()*(89-1)+1,0)

will provide the number(s) you seek, but Excel will generate a new random number each time the sheet recalculates, so I'm not sure how you'd keep track of them, on the sheet, without a macro.

The RAND() function generates a random number between 0 and 1.

Info here, for assistance;


I've incorporated the "roundup" function into what I've suggested to give whole numbers.

Hope this helps.

I'm a bit rusty with macros... see if you can manage with what's above, to be going on with.



  Woolwell 22:12 02 Aug 2012

The problem is excluding duplicate numbers. That isn't "probably simple". You may well need a macro.

  Woolwell 22:48 02 Aug 2012

To get a list of 89 numbers in random order:

A1 to A89 Insert function =RAND() B1 to B89 insert function ==RANK(A1,$A$1:$A$89)

Column B should contain 89 numbers in random order without any duplicates.

Copied from and adapted enter link description here

  pookie 17:47 03 Aug 2012

Thank you for all the replies!

  Picklefactory 11:47 04 Aug 2012

I have found some code that will do this (Thanks to Nimrod on MrExcel), do you know anything about how to insert VBA? I'll try and explain, apologies if I over simplify, but I don't know if this is new to you.

I've set up a grid of numbers on the worksheet (Sheet1) to choose from (1-89) in cell range D20:L29 with the number 1 entered in cell D20 then going left to right with 9 in L20 and then consecutive rows continuing down, so you have a solid grid 9 columns x 10 rows with the numbers 1-89 (I left the final cell blank)

It's important you use the specified range for the code to work.

Next, copy the following code to your clipboard. This is tricky to display properly on this forum, as it doesn't display single lines as they are typed, it lumps everything together, so I've had to add a space between each line which you can either ignore or clean up in Excel if you want, but it won't hurt. Ensure you don't miss anything, copy everything from and including Public Sub BingoV2() to the last End Sub.

Public Sub BingoV2()



Rw = Int(((29 - 19) * Rnd) + 20)

Col = Int(((12 - 3) * Rnd) + 4)

With Cells(Rw, Col)

If .Interior.ColorIndex = xlNone Then

Cells(Cells(65536, 1).End(xlUp).Row + 1, 1).Value = .Value

.Interior.ColorIndex = 6


If StillUnusedNumbersV2("D20:L29") Then GoTo TryAgain

End If

End With

End Sub

Public Function StillUnusedNumbersV2(Rng) As Boolean

For Each C In Range(Rng)

If C.Interior.ColorIndex = xlNone Then

StillUnusedNumbersV2 = True

Exit Function

End If

Next C

StillUnusedNumbersV2 = False

MsgBox "All Numbers used", vbInformation, "Game Over"

End Function

Sub Restart()


' Restart Macro




With Selection.Interior

    .Pattern = xlNone

    .TintAndShade = 0

    .PatternTintAndShade = 0

End With




End Sub

Open up your VBA window in Excel (Alt+F11). In the left hand explorer window the look for your workbook name in bold (Probably VBA Projects (Book1)). Double click on Sheet1 to open up the main window for that sheet, and in the main window paste the code from above.

Nearly there now, all we have to do is insert a couple of buttons on the worksheet.

So, click back onto your main worksheet where your number grid is, or just close down the VBA window if you want.

On the ribbon, select the Developer tab. (If it is not there, you need to open Excel options via Office button top left corner, select Excel Options, and on the Popular tab, tick the Show Developer tab in the Ribbon check box)

On the Developer tab, Controls section, pull down the menu for Insert and select the top left 'Button' command and click and drag a button to what size and position you want on your sheet, it will immediately open the Macro window to select which macro this button will activate, select Sheet1.BingoV2

Repeat this again for the Reset button, but obviously selecting Sheet1.Restart for the desired Macro for the 2nd button, and Hey Presto, hopefully you should be done.

  pookie 19:00 08 Aug 2012

Thank you ever so much! I've give that a go shortly

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?