BINGO

=BINGO()

BINGO returns a 75 ball Bingo card

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,957
Office Version
  1. 365
Platform
  1. Windows
BINGO returns a 75 ball Bingo card.

Excel Formula:
=LAMBDA(
    VSTACK(
        {"B","I","N","G","O"},
        IF(SEQUENCE(5,5)=13,
            "FREE",
            FILTER(SORTBY(TRANSPOSE(SEQUENCE(5,15)),RANDARRAY(15)),SEQUENCE(15)<6)
        )
    )
)

BINGO
ABCDEFGHIJKLMNOPQ
1BINGOBINGOBINGO
211264156711025405570116314661
33183348639243954691025405570
4116FREE46611530FREE60751429FREE5974
58233853683183348631530456075
613284358737223752671126415671
7
8BINGOBINGOBINGO
97223752671429445974520355065
103183348638233853681126415671
11419FREE49641126FREE56711025FREE5570
1221732476215304560751429445974
135203550651025405570722375267
Data
Cell Formulas
RangeFormula
A1:E6,M8:Q13,G8:K13,A8:E13,M1:Q6A1=BINGO()
Dynamic array formulas.
 
Upvote 0
Thanks, lol. Got Bingo on the brain.
 
Your Bingo formula looks good, but I do see a problem with the randomness of numbers of across rows. You will notice that every number in the B-column is followed by the same exact numbers in the other columns for every example table you posted. For example: {11, 26, 41, 56, 71} and {3, 18, 33, 48, 63} are always only present together, and they are exactly 15 numbers apart. Here is a formula that randomly generates numbers for each column BEFORE combining them:

Excel Formula:
=LET(name, {"B","I","N","G","O"}, 
Gen, LAMBDA(x,TAKE(SORTBY(SEQUENCE(15,,x),RANDARRAY(15)),5)), b, Gen(1),  i, Gen(16),  nn, Gen(31),  g, Gen(46),  o, Gen(61),  n, VSTACK(TAKE(nn, 2), "Free", TAKE(nn, -2)), game, HSTACK(b,i,n,g,o), card, VSTACK(name, game), card )
 
LAMBDA skills improved, so I thought I would post this updated version. Many thanks to lrobbo314 for the IF formula to add "Free"!
Excel Formula:
=VSTACK(
    {"B", "I", "N", "G", "O"},
    IF(
        SEQUENCE(5, 5) = 13,
        "Free",
        DROP(
            REDUCE(
                "",
                {1; 16; 31; 46; 61},
                LAMBDA(a, v,
                    HSTACK(a, TAKE(SORTBY(SEQUENCE(15, , v), RANDARRAY(15)), 5))
                )
            ),
            ,
            1
        )
    )
)
 
This is a good formula!!✌️
One suggestion though, to not hardcode 1,16,31,....you could have used seq(5,,,15)
............REDUCE(
"",
SEQUENCE(5,,,15),
LAMBDA(a,v,......................

Remembered that I created a SHUFFLE function that I used to help me create large random data sets and I did not use HSTACK or VSTACK because they are too "slow" for big sets.
Maybe if there is interest, I will publish it.
It was fun to apply it for something like bingo. 😊
Here is a glimpse of the functionality
LH that spill.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1
2For BINGO main array will need to shuffle TRANSPOSE(SEQUENCE(5,15)) by column
3SHUFFLE by columnand TAKE only 5 rows
4ar=SHUFFLE(B5:D9,1)
51aA2aC=TRANSPOSE(SEQUENCE(5,15))=SHUFFLE(K6#,1)
62bB5dB116314661123365563
73cC3cA217324762327355861
84dD1bE318334863518435267
95eE4eD4193449641019314773
10520355065230325370
11SHUFFLE by row621365166429415064
12ar=SHUFFLE(B13:D17)722375267725454866
131aAa1A8233853681121445768
142bBbB29243954691322385962
153cCCc310254055701228424969
164dDDd411264156711420344672
175eEEe51227425772926406075
1813284358731524335665
19SHUFFLE by cell1429445974817395471
20ar=SHUFFLE(B21:D25,2)1530456075616375174
211aA5c1
222bBbCB=TAKE(Q6#,5)
233cCe34123365563
244dDdAD327355861
255eEE2a518435267
261019314773
27230325370
28
29single cell:
30=TAKE(SHUFFLE(TRANSPOSE(SEQUENCE(5,5)),1),5)
3139151724
3257111625
33110131822
3446141921
3528122023
36
Sheet3
Cell Formulas
RangeFormula
F4,K30,Q22,F20,F12,Q5,K5F4=FORMULATEXT(F5)
F5:H9F5=SHUFFLE(B5:D9,1)
K6:O20K6=TRANSPOSE(SEQUENCE(5,15))
Q6:U20Q6=SHUFFLE(K6#,1)
F13:H17F13=SHUFFLE(B13:D17)
F21:H25F21=SHUFFLE(B21:D25,2)
Q23:U27Q23=TAKE(Q6#,5)
K31:O35K31=TAKE(SHUFFLE(TRANSPOSE(SEQUENCE(5,5)),1),5)
Dynamic array formulas.
 
I looked through the LAMBDA forum and I couldn't find your SHUFFLE formula @Xlambda. Can you post the formula or a link? I'd be interested to see that one.
 
Cool, as I said: "Maybe if there is interest, I will publish it."
It must be on a new thread. There is a module group of simple functions interconnected that call each other. So needs a proper presentation.😉
 
This is something I've come back to a few times. The shuffle by rows and by columns. Seeing this yesterday gave me some motivation. Now I figured it out.

Just curious. You say that VSTACK and HSTACK are 'slow'. The solution I came up with uses TOCOL, TOROW, WRAPCOLS, and WRAPROWS. Are those 'slow' in your opinion? Or am I on the same track as you with this shuffle LAMBDA?
 

Forum statistics

Threads
1,223,246
Messages
6,170,999
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top