Help making BINGO cards

[TomKC]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am quite the novice at using Excel. I'm trying to create a set of BINGO cards for a church group and I'm having trouble figuring it out. I saw a post about something called RAND function but I don't know what that means.

What I need is 56 unique cards with 5 columns and 5 rows each. The cards will be using the numbers 1-24 leaving the center square blank or "0".

Can anyone please assist?

 

[Mike Middleton]

TomKC -

Have you looked at any of the items returned when you use Google or Bing or some other search engine to look for "excel bingo cards" (without the quotes)?

You can get information about the RAND worksheet function using Excel's built-in Help.

- Mike
http://www.MikeMiddleton.com


Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am quite the novice at using Excel. I'm trying to create a set of BINGO cards for a church group and I'm having trouble figuring it out. I saw a post about something called RAND function but I don't know what that means.

What I need is 56 unique cards with 5 columns and 5 rows each. The cards will be using the numbers 1-24 leaving the center square blank or "0".

Can anyone please assist?

 

[TomKC]

Mike,

I found an example at http://blog.contextures.com/archives/2009/03/12/create-bingo-cards-in-excel/

I was able to cut it down to 25 in the first set but I'm getting an NUM error in the second two sets and I don't know how to fix it.

Also to clarify my needs more specifically, I don't need the BINGO letter/number combination. I just need 56 cards that are unique that contain 24 numbers.

FYI, I'm now told I can use 1-30 from my number selection if that helps.

(In fact they will be replacing the numbers with symbols before then print them and that's why the limit on the numbers used as they only have 30 symbols.)

Thanks,
TomKC

 

[John McGhie]

Hi Tom:

A "NUM" error means "The number being input to this formula is not
acceptable". Usually, because it's not a number at all...

See the Excel Help topic "Correct a #NUM! Error"

To get help with errors in here, you need to show us the formula you are
using, otherwise all we can say is "Sorry, don't know..."

Cheers

I found an example at
http://blog.contextures.com/archives/2009/03/12/create-bingo-cards-in-excel/

I was able to cut it down to 25 in the first set but I'm getting an NUM error
in the second two sets and I don't know how to fix it.

Also to clarify my needs more specifically, I don't need the BINGO
letter/number combination. I just need 56 cards that are unique that contain
24 numbers.

FYI, I'm now told I can use 1-30 from my number selection if that helps.

(In fact they will be replacing the numbers with symbols before then print
them and that's why the limit on the numbers used as they only have 30
symbols.)

Thanks,
TomKC

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[TomKC]

John,

That makes total sense. The first group is B2-F2 thru B6-F6 (D4 is open)

The next group is B9-F9 thru B13-F13

The last group is B16-F16 & B20-F20

In the second group I'm getting errors in these cell all the way thru the end of the third group.

When I select Trace error for example in B9 it shows a dotted line with a small table symbol to D7 and on C9 it shows a similar dotted line to A7. Both of which are blank cells.

Thanks for any help.

============================================

This is cell B9
=INDEX(Numbers!$P$1:$P$5,MATCH(LARGE(Numbers!$Q$1:$Q$5,ROW()-3),Numbers!$Q$1:$Q$5,0))

C9
=INDEX(Numbers!$S$1:$S$1,MATCH(LARGE(Numbers!$T$1:$T$5,ROW()-3),Numbers!$T$1:$T$5,0))

D9
=INDEX(Numbers!$V$1:$V$5,MATCH(LARGE(Numbers!$W$1:$W$5,ROW()-3),Numbers!$W$1:$W$5,0))

E9
=INDEX(Numbers!$Y$1:$Y$5,MATCH(LARGE(Numbers!$Z$1:$Z$5,ROW()-3),Numbers!$Z$1:$Z$5,0))

F9
=INDEX(Numbers!$AB$1:$AB$5,MATCH(LARGE(Numbers!$AC$1:$AC$5,ROW()-3),Numbers!$AC$1:$AC$5,0))

B10
=INDEX(Numbers!$P$1:$P$5,MATCH(LARGE(Numbers!$Q$1:$Q$5,ROW()-3),Numbers!$Q$1:$Q$5,0))

C10
=INDEX(Numbers!$S$1:$S$5,MATCH(LARGE(Numbers!$T$1:$T$5,ROW()-3),Numbers!$T$1:$T$5,0))

D10
=INDEX(Numbers!$V$1:$V$5,MATCH(LARGE(Numbers!$W$1:$W$5,ROW()-3),Numbers!$W$1:$W$5,0))

E10
=INDEX(Numbers!$Y$1:$Y$5,MATCH(LARGE(Numbers!$Z$1:$Z$5,ROW()-3),Numbers!$Z$1:$Z$5,0))

F10
=INDEX(Numbers!$AB$1:$AB$5,MATCH(LARGE(Numbers!$AC$1:$AC$5,ROW()-3),Numbers!$AC$1:$AC$5,0))

Row 7 is blank

Rows 1 & 8 has the letters that spell BINGO

These cells seem to be acting correctly

B2
=INDEX(Numbers!$A$1:$A$5,MATCH(LARGE(Numbers!$B$1:$B$5,ROW()-1),Numbers!$B$1:$B$5,0))

C2
=INDEX(Numbers!$D$1:$D$5,MATCH(LARGE(Numbers!$E$1:$E$5,ROW()-1),Numbers!$E$1:$E$5,0))

D2
=INDEX(Numbers!$G$1:$G$5,MATCH(LARGE(Numbers!$H$1:$H$5,ROW()-1),Numbers!$H$1:$H$5,0))

E2
=INDEX(Numbers!$J$1:$J$5,MATCH(LARGE(Numbers!$K$1:$K$5,ROW()-1),Numbers!$K$1:$K$5,0))

F2
=INDEX(Numbers!$M$1:$M$5,MATCH(LARGE(Numbers!$N$1:$N$5,ROW()-1),Numbers!$N$1:$N$5,0))

B3
=INDEX(Numbers!$A$1:$A$5,MATCH(LARGE(Numbers!$B$1:$B$5,ROW()-1),Numbers!$B$1:$B$5,0))

C3
=INDEX(Numbers!$D$1:$D$5,MATCH(LARGE(Numbers!$E$1:$E$5,ROW()-1),Numbers!$E$1:$E$5,0))

D3
=INDEX(Numbers!$J$1:$J$5,MATCH(LARGE(Numbers!$K$1:$K$5,ROW()-1),Numbers!$K$1:$K$5,0))

E3
=INDEX(Numbers!$J$1:$J$5,MATCH(LARGE(Numbers!$K$1:$K$5,ROW()-1),Numbers!$K$1:$K$5,0))

F3
=INDEX(Numbers!$M$1:$M$5,MATCH(LARGE(Numbers!$N$1:$N$5,ROW()-1),Numbers!$N$1:$N$5,0))

 

[TomKC]

This set up was originally for 75 numbers. In the second sheet there where (3) sets of 5 columns with 15 rows each.

I took and deleted the numbers from 26-75

I then made 5 column of 5 rows each by cutting and pasting:

1 0.941481906 16 0.372377533 6 0.364931608 11 0.502122056 21 0.984256048
2 0.536584858 17 0.361460471 7 0.555143076 12 0.076269993 22 0.705505836
3 0.57106262 18 0.225019925 8 0.599454419 13 0.171432136 23 0.55615215
4 0.894617963 19 0.782584441 9 0.646271311 14 0.89659554 24 0.161401461
5 0.695414685 20 0.092296653 10 0.492237897 15 0.616963681 25 0.157171595

1 0.193406672 6 0.958883039 11 0.318913906 16 0.545599863 21 0.667879587
2 0.803493655 7 0.581899116 12 0.542515461 17 0.682065422 22 0.483289631
3 0.708818348 8 0.221810767 13 0.799975414 18 0.084285534 23 0.104192029
4 0.830712719 9 0.675504324 14 0.33478638 19 0.932731846 24 0.991972791
5 0.544669395 10 0.694428477 15 0.112479015 20 0.691381364 25 0.965832338

1 0.694738042 6 0.86595987 11 0.903419695 16 0.262463426 21 0.008170542
2 0.083745427 7 0.044931691 12 0.430510665 17 0.547232687 22 0.669368166
3 0.680413716 8 0.815642955 13 0.519759512 18 0.09317679 23 0.821925664
4 0.574002915 9 0.848216665 14 0.225085772 19 0.319883073 24 0.886122491
5 0.214044215 10 0.264216104 15 0.562006026 20 0.817634512 25 0.157414113

 

[John McGhie]

Yep: None of the cells feeding values into the formula can be blank.

Change the formulas so they point to cells that contain valid values

If I were you, I would go back to the original and look to see which cells
those formulas are supposed to be pointing to. Given that you have a LARGE
within a Match within an Index, this is quite a complex formula. You may
find that it helps to pull it to pieces and get each piece working, then
plug them all together.

Cheers

John,

That makes total sense. The first group is B2-F2 thru B6-F6 (D4 is open)

The next group is B9-F9 thru B13-F13

The last group is B16-F16 & B20-F20

In the second group I'm getting errors in these cell all the way thru the end
of the third group.

When I select Trace error for example in B9 it shows a dotted line with a
small table symbol to D7 and on C9 it shows a similar dotted line to A7. Both
of which are blank cells.

Thanks for any help.

============================================

This is cell B9
=INDEX(Numbers!$P$1:$P$5,MATCH(LARGE(Numbers!$Q$1:$Q$5,ROW()-3),Numbers!$Q$1:$
Q$5,0))

C9
=INDEX(Numbers!$S$1:$S$1,MATCH(LARGE(Numbers!$T$1:$T$5,ROW()-3),Numbers!$T$1:$
T$5,0))

D9
=INDEX(Numbers!$V$1:$V$5,MATCH(LARGE(Numbers!$W$1:$W$5,ROW()-3),Numbers!$W$1:$
W$5,0))

E9
=INDEX(Numbers!$Y$1:$Y$5,MATCH(LARGE(Numbers!$Z$1:$Z$5,ROW()-3),Numbers!$Z$1:$
Z$5,0))

F9
=INDEX(Numbers!$AB$1:$AB$5,MATCH(LARGE(Numbers!$AC$1:$AC$5,ROW()-3),Numbers!$A
C$1:$AC$5,0))

B10
=INDEX(Numbers!$P$1:$P$5,MATCH(LARGE(Numbers!$Q$1:$Q$5,ROW()-3),Numbers!$Q$1:$
Q$5,0))

C10
=INDEX(Numbers!$S$1:$S$5,MATCH(LARGE(Numbers!$T$1:$T$5,ROW()-3),Numbers!$T$1:$
T$5,0))

D10
=INDEX(Numbers!$V$1:$V$5,MATCH(LARGE(Numbers!$W$1:$W$5,ROW()-3),Numbers!$W$1:$
W$5,0))

E10
=INDEX(Numbers!$Y$1:$Y$5,MATCH(LARGE(Numbers!$Z$1:$Z$5,ROW()-3),Numbers!$Z$1:$
Z$5,0))

F10
=INDEX(Numbers!$AB$1:$AB$5,MATCH(LARGE(Numbers!$AC$1:$AC$5,ROW()-3),Numbers!$A
C$1:$AC$5,0))

Row 7 is blank

Rows 1 & 8 has the letters that spell BINGO

These cells seem to be acting correctly

B2
=INDEX(Numbers!$A$1:$A$5,MATCH(LARGE(Numbers!$B$1:$B$5,ROW()-1),Numbers!$B$1:$
B$5,0))

C2
=INDEX(Numbers!$D$1:$D$5,MATCH(LARGE(Numbers!$E$1:$E$5,ROW()-1),Numbers!$E$1:$
E$5,0))

D2
=INDEX(Numbers!$G$1:$G$5,MATCH(LARGE(Numbers!$H$1:$H$5,ROW()-1),Numbers!$H$1:$
H$5,0))

E2
=INDEX(Numbers!$J$1:$J$5,MATCH(LARGE(Numbers!$K$1:$K$5,ROW()-1),Numbers!$K$1:$
K$5,0))

F2
=INDEX(Numbers!$M$1:$M$5,MATCH(LARGE(Numbers!$N$1:$N$5,ROW()-1),Numbers!$N$1:$
N$5,0))

B3
=INDEX(Numbers!$A$1:$A$5,MATCH(LARGE(Numbers!$B$1:$B$5,ROW()-1),Numbers!$B$1:$
B$5,0))

C3
=INDEX(Numbers!$D$1:$D$5,MATCH(LARGE(Numbers!$E$1:$E$5,ROW()-1),Numbers!$E$1:$
E$5,0))

D3
=INDEX(Numbers!$J$1:$J$5,MATCH(LARGE(Numbers!$K$1:$K$5,ROW()-1),Numbers!$K$1:$
K$5,0))

E3
=INDEX(Numbers!$J$1:$J$5,MATCH(LARGE(Numbers!$K$1:$K$5,ROW()-1),Numbers!$K$1:$
K$5,0))

F3
=INDEX(Numbers!$M$1:$M$5,MATCH(LARGE(Numbers!$N$1:$N$5,ROW()-1),Numbers!$N$1:$
N$5,0))

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]