VBA Simulation of Shuffling and Distributing a Deck of Cards

Here's a simple VBA program that simulates shuffling and distributing a standard deck of 52 cards to 4 players. The program outputs the results in cells A1 through D13 of the current Excel spreadsheet, with the 4 rows corresponding to the 4 players' cards.

Sub ShuffleCards()
'This program simulates shuffling of a standard deck of 52 cards to 4 players
'Results are outputted in 4 columns in cells A1:D13

'H: heart, S: spades, D: diamod, C: club
'2 thru 10, J for jack, Q for queen, K for king, A for ace
'2H would be 2 of hearts, KD would be king of diamonds, etc.

'Creating
suits = Array("H", "S", "D", "C")
num = Array("2", "3", "4", "5", "6", "7", "8", "9", "10", "J", "Q", "K", "A")

Dim cards(0 To 51) As String
For i = 0 To 3
For j = 0 To 12
cards(13 * i + j) = num(j) & suits(i)
Next j
Next i

'Shuffling
For i = 1 To 1000
c1 = Int(52 * Rnd)
c2 = Int(52 * Rnd)
temp = cards(c1)
cards(c1) = cards(c2)
cards(c2) = temp
Next i
'Distributing
For k = 0 To 51
Cells(k Mod 13 + 1, Int(k / 13) + 1) = cards(k)
Next k
End Sub

The shuffling algorithm is to run through 1000 iterations of swapping two randomly chosen cards.

Inverse Transform Demonstration with Excel VBA

Given F(x) = 1-e^(-λ*x) as the cumulative distribution function, the inverse transform gives -1/λ*ln(U) as the function that has F(x) as its cdf, where U is the uniform distribution from [0, 1]. Here, the following VBA codes allow users to visualize this transformation in Microsoft Excel.

Upon the execution of this procedure, the user inputs a value for lambda. Then 10,000 simulations are run, initially generating a random number from [0, 1] and then inputting that random number into -1/λ*ln(U), and outputting the result in column A. At the end of the execution, column C contains the different x values from 0 to the maximum, in increments of 0.001. Column D reflects the cdf by counting entries of A that are smaller than the corresponding x value. Finally, column E calculates the true value of 1-e^(-λ*x). The idea is that the outputs in columns D and E are similar.

Sub InverseTransform()
'Demonstrates inverse transform of the cdf F(x) = 1-e^(-lambda*x)
Columns("A:E").Clear
Range("B1").Value = 1# * InputBox("Enter a positive value for lambda: ", "Input", 2)

'10,000 simulation trials to be performed
'Transform of F(x) gives -1/lambda*ln(U), where U is uniform distribution [0,1]
For i = 1 To 10000
Cells(i, 1) = -1 / Range("B1").Value * Log(Rnd)
Next i

'Determine the maximum for the range of numbers to work with
Range("B2").FormulaR1C1 = "=MAX(C[-1])"

'To determine the cumulative probability density, use 0.001 gradient from 0 to the maximum value as the counter
i = 1
While i / 1000 <= Range("B2").Value
Cells(i, 3).Value = i / 1000
'In column D, count entries in column A that are smaller than the counter, then divide by the number of trials
Cells(i, 4).FormulaR1C1 = "=COUNTIF(C[-3],""<""&RC[-1])/10000"
'In column E, calculate the true value of 1-e^(-lambda*x)
Cells(i, 5).FormulaR1C1 = "=(1-EXP(0-R1C2*RC[-2]))"
i = i + 1
Wend
Range("B2").Clear
End Sub

After the execution of this procedure, the user can perform further analysis. Graphing columns C through E does reveal that values in columns D and E are similar, as the points almost completely overlap. Error calculations from those two columns illustrate a similar result that the inverse transform method takes a function F(x), exponential function in this case, to produce a function whose cdf is F(x).

Intricacies of VLOOKUP

In Microsoft Excel, VLOOKUP is a very useful function that "searches for a value in the first column of a table array and returns a value in the same row from another column in the table array." The basic syntax of the function is =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup), with the range_lookup being optional.

If range_lookup is FALSE, the function will look for only exact matches. In this case, the values in the first column of table_array do not need to be sorted. However, this is not the case if range_lookup is TRUE, which is the selection by default if omitted. Then the values in the first column must be in ascending order, as the function will use the largest value smaller than the lookup_value, if an exact match is not found.

Finally, two wildcard characters allow flexibility in the lookup_value when it is a text and the range_lookup is FALSE. A question mark (?) matches any single character, while asterisk (*) matches any sequence of characters. Use a tilde (~) before either the question mark or asterisk if they are the actual characters in the lookup_value. So as an example, the text value "abc" can be matched by both "a??" and "a*".

Source:

• http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

Making Box and Whisker Plot in Excel

There's a simple method to construct a box and whisker diagram in Excel, by taking advantage the fact that box and whisker diagram is just a scatter plot with values fixed on one of the axis to give height to the box. Given a sample of data in column A of an Excel worksheet, here are the parameters necessary, along with the formula needed to obtain those values:

• Minimum: =MIN(A:A)
• 1st quartile:  =QUARTILE(A:A,1) or =PERCENTILE(A:A,0.25)
• Median: =MEDIAN(A:A)
• 3rd quartile:  =QUARTILE(A:A,3) or =PERCENTILE(A:A,0.75)
• Maximum: =MAX(A:A)

Once those values are obtained, here is the most crucial component of the diagram: pasting the values into this table for a horizontal box and whisker diagram. Simply reverse the columns for vertical diagram:

Min	1
Min	3
Min	2
Q1	2
Q1	3
Q1	1
Median	1
Median	3
Q1	3
Q3	3
Q3	1
Median	1
Q3	1
Q3	2
Max	2
Max	1
Max	3

Once the numerical values are filled, this is simply a set of coordinate points. Select the table, and choose Scatter Plot > Scatter with Straight Lines and Markers. As imagined, the values 1, 2, 3 are arbitrarily chosen; since we are dealing with one-dimensional data, and just want the other axis to demonstrate some height to the boxes, the values 1, 2, 3 are the easiest to work with. In fact, as soon as the scatter plot is made, it's best to delete the values of that axis. Here's a box-whisker-plot of 50 randomly chosen numbers between 0 and 1:

To see that this is indeed the result of the table from above, trace the points on this plot, by going down the rows of the table. The labels on the plot should help to trace. Start from (min, 1) at the bottom left of the plot. The next row of the table is (min, 3) at the top left of the plot. In order to move onto the Q1 value on the plot, we must go down to (min, 2) and then to (Q1, 2) in order to preserve the straight edges of the box and whisker plot. And so on, the table lists the points that must be traced over to ultimately make the plot.

Introduction to Array Formulas in Excel: Transpose

Standard formulas in Excel begin at something as simple as =SUM(A1:A3), typed into a single cell. Suppose that formula was written in cell B1; in this case the sum of the numerical values from cell A1 to A3 is returned in cell B1. Among their many capabilities, array formulas can return multiple results, into multiple cells. Let's illustrate that with a simple case of transposing, or converting a row of array into a column, or vice versa.

Suppose that we have a table with the integers 1 through 5 listed in the first column, and the square of those integers in the second column, calculated through formula: for example, cell B1 contains =A1^2, and so forth. In the end, the table looks like this:

1	1
2	4
3	9
4	16
5	25

Now say that we want to transpose this table, so that instead of a 5x2 table, it'll be 2x5 table with the integers 1 through 5 in the top row, and the square of those numbers in the bottom row. We can actually do this easily by copying and instead of simply pasting, right-click the house and select Paste Special. When the option box comes up, check the Transpose box and hit OK. The table should look like this as envisioned:

1	2	3	4	5
1	4	9	16	25

However, these two tables are not linked by any formulas. Suppose that in the original table, we want to replace the value 5 with 10. Next to it, 100 will automatically appear since the calculation is based on the formula. However, the transposed table will not automatically change. This may be problematic if the data need to be linked.

Fortunately, there is a way to do transposing but through copy / paste special, but rather through formulas, just like =SUM() or A1^2. However, do realize that the output is going to be a 2x5 table, not a single cell. Here is where array formulas can perform this task. To begin, select and activate a range of 2 rows by 5 columns. While those cells are highlighted, begin typing =TRANSPOSE(A1:B5). As before, the range may be selected by the mouse. Now, if we hit enter right at this stage, we would get an error. The key to using array formulas is to press Control + Shift + Enter to enter the formula. Upon hitting those three keys, the transposed table will appear. Not only will it appear, it will be linked to the original table. Any change in the original table will automatically be reflected on the transposed table.

If we click on any cells that comprise the transposed table, we see that their formula all say {=TRANSPOSE(A1:B5)}. The curly brackets are indicative of the cell being part of an array. Another characteristic of an array is that its cells can't be modified. Try editing the content of any of the cells and an error message appears. The only way to exit this message is to press Esc.

In future posts, we will see how array formulas can perform multiple calculations on one or more items.

Processing List of Numbers into Excel

Suppose we have the a list of numbers separated by comma and space that we need to get into Excel sorted. What's the fastest way to do this? If the data is small, it might be faster to type each. But as the data gets large, that will be very inefficient. Preserving the accuracy will also be more difficult as the number of entries increase. So let's begin with a data like this in a string of text:

84, 49, 61, 40, 83, 67, 45, 66, 70, 69, 80, 58, 68, 60, 67, 72, 73, 70, 57, 63, 70, 78, 52, 67, 53, 67, 75, 61, 70, 81, 76, 79, 75, 76, 58, 31

Here are the steps to generated a sorted list out of those numbers into a column in Excel:

1. We want to get rid of the commas. Copy the string of text into Microsoft Word. Hit Control + F to bring Find and Replace menu. Click the Replace tab. We want to enter the comma "," under 'Find What' and nothing "" under 'Replace With'. Hit 'Replace All'.  The string should now be cleansed of commas.
2. With no commas now, it's ready to be used by Excel. Copy this cleansed text and pasted into an Excel cell. Let's call this cell A1. The entire cleansed string should be in A1.
3. We want to separate each number into its own cell. First highlight A1. At the 'Data' tab at the top of the Excel window, click on 'Text to Columns'. Choose the 'Delimited' option and hit 'Next'. Under the option of 'Delimiters', check the 'Space' box, and hit 'Finish'. Now each number will occupy its own cell.
4. The data are all on the same row. However most times, we want the data on the same column. To convert, first highlight all of the values on A row. Control C to copy. Now highlight cell A2. Right-click the mouse and choose 'Paste Special'. At the bottom of the menu, check the 'Transpose' box and click 'OK'. The values now will be pasted in a column.
5. Delete the top row by first clicking on the '1' row numbering. The entire top row should now be highlighted. Right-click and choose 'Delete'. We already have the data in the column, so the row of data is unnecessary.
6. Finally to sort, first click 'A' to highlight the column. From the 'Home' tab at the top, choose 'Sort & Filter' and select 'Sort Smallest to Largest'. If a menu pops up about whether or not to expand the selection, leave it the way it is and click 'Sort'. Since we only have one column of data, this is not relevant.

At the end of this process, all of the numbers will be sorted in a column in an Excel sheet. When there are many entries of data to input, this method of processing will be much efficient than simply typing each one out.