(probably simple) excel random number question

  pookie 02 Aug 12

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 02 Aug 12

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 02 Aug 12

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

  Woolwell 02 Aug 12

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 03 Aug 12

Thank you for all the replies!

  Picklefactory 04 Aug 12

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 08 Aug 12

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


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

Windows 10 Home vs Windows 10 Pro comparison: Here are the extra features you get in the Pro version

Behind the scenes at Silverstone, the 70th British Grand Prix: Hisense starts UK push with Red…

How Found created this skateboard, bike and parkour stunts-laden, single-take car ad

Apple Pay UK launch date, UK user guide and participating UK banks | Apple Pay to launch in UK on 1?…

We use cookies to provide you with a better experience. If you continue to use this site, we'll assume you're happy with this. Alternatively, click here to find out how to manage these cookies

hide cookie message