Blog - Eben Mishkin

HA!

Gaze upon my miracles!!

With just two helper columns and one nightmare of a function that will haunt the nightmares of excel jockey’s everywhere. I have taught excel to “draw” a variable hand of cards!!!

Probably useless for anyone but myself. Even then, probably only useful very rarely. But on the off chance anyone wants it, code is below the cut. With some minimal extra mansplaining braggadocio.

I swear, somehow, Excel is simply the best toy EVAR!!!!!

Explanation, then my original bodge-job.

Explanation:

I have two helper columns and one cell with with nested functions designed to spill into multiple rows of cells.

First Helper Column is an array of possible values. So X, X+1, X+2 … X+n.

I have two Arrays in one column, though there is no particular reason they have to share space except to keep things neat.

The first array is 3 to 18. To simulate rolling 3d6.

The second is 1 to 4. To simulate rolling a d4.

Second Helper column consists of the return arrays I want from looking at the first column.

I get the card details from an XLOOKUP function: XLOOKUP(thing I want to lookup, where I want it to start looking for that thing in the first array : where I want it to stop looking in the first array, where I want it to start looking for the return value in the second array : where I want it to stop looking in the second array)

The value I want it to look up in the first array is the result of a simulated roll of 3d6.

You can simulate any dice roll with the RANDBETWEEN function which gives a random number from a set of numbers. RANDBETWEEN( lowest number it can give you, highest number it can give you). 1d6 is RANDBETWEEN(1,6).

I don’t want to roll 1d6 * 3 or whatever you would label the actual die equivalent of RANDBETWEEN(3,18). Because there could be missed return values and the odds of what you would roll as a single die are different from the roll of multiple dice.

So I add together 3 of the d6 functions as a single value using the SUM function. Don’t technically have to use the SUM function but it makes sure that the result is treated as an absolutely singular value.

XLOOKUP looks in the first array to find that 3d6 value and returns the value’s place in that array. So if I got 3 from the sum, it would return array position 1. This is true of all positions.

XLOOKUP then looks at array 2 in the next column, counts that many spaces, and returns what is at that point in that second array. I have the first value set to 3. But I could just as easily have started from 1 instead, so 3 in column A would return 1 in column B.

There are odds differences based on what number you put in what place because the odds of particular numbers are different on 3d6:

It is very unlikely to return the first result - 3 - compared to the 8th and 9th results - 10 & Jack. This is an inherent problem with simulating dice rolls instead of the full capabilities of excel.

To get the correct odds of an actual playing card deck, you would use RANDBETWEEN(1,52). With 1 to 52 in the first lookup array, and every single card in the deck listed individually in the second return array.

So, for any two arrays, X…X+n and Y+0…Y+n, where we start at row Z for both arrays, I can get the random return I want from XLOOKUP(RANDBETWEEN(X,X+n),$A$[Z]:$A$[Z+n],$B$[Z]:$B$[Z+n]).

So, for me Z = 1, X = 3. Final entry of X+n is 18 (3*6). So n is 15. Making it $A$1:$A$16 & $B$1:$B$16 for the arrays. Looking for a random index number between 1 and [1+15].

If all I needed was the card value, I’d be done.

But I also want the suit. Which is the same thing again, just starting with Z = to X+n+2 so I’ll skip a line. $A$18:$A$21 (because I’m rolling a d4 and only need 4 entries) etc.

I put the two bits of information together with a simple text string. “The ”&[return from the first set of arrays]&“ of ”&[return from the second set of arrays]

Which would actually look like: “The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)

If I just wanted one card, I’d be done.

But I want multiple cards.

The problem is, if I just repeat the above the number of times that I want to “draw a card” it comes out as a single, long string of text.

So, I add one more part to the string of text that will only appear BETWEEN the sentences. &“ @” at the end of each draw statement.

Now I can use the TEXTSPLIT function to cut the string short and go on to the next string as a new string. TEXTSPLIT([the string of everything above],[what I want it to split at: “ @”, so it will end on a space, not the @ symbol itself])

Essentially: TEXTSPLIT(“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”,,“ @”)

The double comma in there is skipping telling the function where to break off for a new column, because I want each string on a new subsequent row, not in the next cell to the right.

IF I didn’t care about drawing multiple cards that are the same from the same deck, which would only have one of each, I’d be done. IF I were simulating some multiple number of decks I could draw from, then all I would have to do is limit the drawn cards to the number of decks in play, so it would be possible to draw that many copies of an identical card. I could also just re-deal if I got that. But I wanted to fix that and make sure everything spilled down into the rows below my function.

So I put all the above in a UNIQUE function. Which means it will only give the unique values. If the maximum number of strings are unique, I get that many cards. If there are repeats, I get less than that.

Which means I’m not just representing drawing that many cards, I’m simulating drawing some minimum draw and whether I ask for more cards or not. As long as I don’t put in more repetitions of the strings to be split than it is possible for me to draw in a game, I will always get that many cards or less.

The problem which I haven’t figured out how to fix, is that I can draw as little as zero cards if all the random cards are the same one. Then I get the #spill error. So, something for another time.

But since I’m specifying a certain number of cards in a draw, so long as I draw enough cards, I can adjust it to have a set number of cards if the limited amount is enough less. Say half the number of draws there could be. Isn’t perfect but it means the draw can’t go radically overboard.

Which gets us to the last function, TAKE.

TAKE([everything we’ve done so far], [Return only the first number Q instances to display])

TAKE(UNIQUE(TEXTSPLIT(“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”,,“ @”)),3)

Will give me the first three cards out of the set of all the unique cards that I could display.

Or I can put in one last roll of the dice to represent a minimum and maximum card draw and replace 3 with that.

And that’s it. That’s the Bodge-Job formula below:

=TAKE(UNIQUE(TEXTSPLIT(“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”&“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”&“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”&“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”&“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”&“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”&“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”&“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”&“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”&“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”&“The ”&XLOOKUP(SUM(RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)),$A$1:$A$16,$B$1:$B$16)&“ of ”&XLOOKUP(RANDBETWEEN(1,4),$A$18:$A$21,$B$18:$B$21)&“ @”,,“ @”)),RANDBETWEEN(3,8))

And I will shut up now.

Eben Mishkin

My favorite kind of Science! news

HA!

Explanation:

And that’s it. That’s the Bodge-Job formula below:

Welp, Only 163 text boxes to go.

I’m fiddling with a big Hex Engine. (Edit: Here it is without the information that probably falls…

That curve is just so visually satisfying.