Tag: excel

Update: You can find a downloadable version of the spreadsheet here.

The Super Bowl is right around the corner, and you know what that means: it’s time for some low-stakes office gambling! Yep, football squares. In case you aren’t familiar, the idea is that you blindly buy squares in a 10 by 10 grid. Once all the squares are sold, the rows and columns are randomly assigned values from 0 to 9. One team is assigned to the row values and the other to the column values. The last digit of each team’s score at the end of each quarter is then used to pick a square, and whoever owns that square wins some percentage of the proceeds.

What’s that, you say? Grids? Random numbers? This sounds like a job for Excel!

You can probably come up with a few different ways to do this, but I chose to generate two sets of ten random numbers. I then assign values to the rows and columns based on the row index of the 1st largest, 2nd largest, 3rd largest, etc. number in the list.

This is what my numbers grid looks like:

1	0.95708204	0	1	0.96026041	6
2	0.60117232	9	2	0.11468243	0
3	0.17740544	6	3	0.60526773	7
4	0.22298474	4	4	0.76106966	8
5	0.89427173	1	5	0.76476649	4
6	0.41933546	5	6	0.6840864	3
7	0.89535015	3	7	0.97857437	5
8	0.05144228	2	8	0.93037023	2
9	0.17105286	8	9	0.90558099	9
10	0.95489495	7	10	0.21003331	1

The first and fourth columns are just hand-entered values from 1 to 10. The second and fifth columns are just random numbers generated by =RAND(). The third and sixth columns are where the magic happens. The formula, shown below, finds the Nth largest value, where N is the hand-entered number from the first or fourth column, and then uses the MATCH function to return the index of that value in the grid. The indexes are 1-based, so I subtract 1 to ensure that my values range from 0 to 9. Here’s what the formula looks like:

=MATCH(LARGE(B$1:B$10,A1),B$1:B$10,0)-1

Now that I have two sets of random numbers, all I need to do is assign them to my squares grid. I did this just by adding a function to each cell (=C1, =C2, etc.). Bam, that’s all there is to it. No more drawing cards or pieces of paper out of a hat: just hit F9 to have Excel recalculate its formulas and generate the whole thing at once.

	bar
foo		6	0	7	8	4	3	5	2	9	1
	0
	9
	6
	4
	1
	5
	3
	2
	8
	7