Dynamic dropdowns are a handy way to get your users to make choices based on what they’ve previously chosen, while steering them away from making invalid choices. Today we’re going to look at one that easily handles multiple levels, and we’ll take a look at what could go wrong. Let’s see one in action, shall we?
Right, what’s on the (dropdown) menu?
Fruit, anyone?
*BING!*
Cool…check it out…as you can see from the above, the user gets prompted with “Choose…” whenever a subsequent choice must be made.
Ok, what kind of fruit should I have? Hmmm, let me see….eeny, meeny, miny, STRAWBERRIES!!!…MO!
*BING!*
Ok, so what delights does Sub Category 2 have in store for me?
Earliglow? Never heard of it. Sounds delicious…I’ll have those, please.
*BING!*
There, all done. Pretty nifty eh…users only get to see valid choices depending on what they chose last. So users simply can’t screw up! Or can they?
[Evil user, determined to prove me wrong]: Wait a minute…I just remembered that mother expects me to eat my vegetables first, before I move on to dessert. So I better change that initial selection:
*BING!*
What the…Strawberries are vegetables???
Damn…changing upstream dropdowns later on means those downstream choices can be flat out wrong! So how can we make this bulletproof?
Macros to the rescue
Yep, we’ll use some code to clear out any ‘downstream’ choices if anything ‘upstream’ changes. Let’s go back to that original strawberry fest:
Now watch what happens when our user subsequently decides they better vege out first:
*BING!*
Ahh…look at that: the code realized that all those downstream choices are no longer valid. So it deleted them, and prompted the user to choose again. There. Now that IS bulletproof.
So let’s see…hmmm…for an appetizer, I’ll have baby carrots:
And I already decided on Strawberries for pudding…
But what about my main course. Ah, yes, of course…
MEAT! Yummy. BURP!
What’s the recipe?
My approach draws on Roger Govier’s excellent sample file on the Contextures website. Be sure to check out that link to see Roger’s in-depth discussion of the formula magic behind this puppy…It’s genius.
In my Dynamic-Dependent-dropdowns-20140214, you’ll see that all the different categories used by the dropdowns are hosted in an Excel Table, that has the initial categories down the left hand side, and subsequent categories across the top:
So how do these categories get used by the data validation dropdowns? Roger’s approach uses two dynamic named ranges to feed the data validation lists, one called MainList and one called SubList:
Here’s the MainList formula:
=INDEX(Table1[[Choose…]],1):INDEX(Table1[[Choose…]],COUNTA(Table1[[Choose…]]))
…and here’s the SubList formula:
=IF(OR(Sheet1!B8="Choose…",Sheet1!B8=""),"",INDEX(Table1,1,MATCH(Sheet1!B8,Table1[#Headers],0)):INDEX(
Table1,COUNTA(INDEX(Table1,,MATCH(Sheet1!B8,Table1[#Headers],0))),MATCH(Sheet1!B8,Table1[#Headers],0)))
The SubList formula has a relative reference in it: whatever cell you use it in, it retrieves the value of the cell to the immediate left, and then it scans the column headers of our validations table (Table1) looking for the heading that matches that value. Once it’s found it, it simply uses the items listed underneath that heading.
Because this formula is relative, before you enter it into the Name Manager, you will need to first select cell C8, because the above relative formula refers to B8 – the cell to the left. (Note that it doesn’t matter what is in C8 or where your actual dropdown are…rather it’s just that the above formula happens to refer to B8, and because we want our formula to always reference the cell on the immediate left, then we’ve got to select the cell to the immediate right before we enter this relative formula into the Name Manager.
Also note that my version of Roger’s approach uses Excel Tables and the associated Structured References that Table functionality allows. My table is called Table1. Your validation lists MUST be held within an Excel Table (which requires Excel 2007 or greater) and you MUST change the Table1 references in the above formula to match the name of your table.
Excel Tables – known as ListObjects to VBA developers – were introduced in Excel 2007, and are a very powerful and simple way to store things like lists, chart data, and PivotTable data…especially if you might need to add more data to your spreadsheet at a later date, and want to avoid having to repoint all your formulas to include the additional data. If you’re not familiar with Excel Tables – or you don’t know what that Table1[#Headers] guff above means – then I strongly suggest you check out Chandoo’s Introduction to Structural References and this great video he did with MrExcel.
The way these two formulas work is very clever. That MainList named range only gets used by dropdowns in that very first ‘Main Category’ column:
…and all other ‘downstream’ dropdowns – no matter what level they are – are fed by the SubList named range:
The beauty of Roger’s approach is that it can handle any number of cascading levels, provided all the category names are unique. All you need to do is simply add the new subcategories to the right hand side of our validations table (Table1).
Let’s look at an example. If you look at the below screenshot, you’ll see that users can choose from a number of different kinds of meat:
Let’s add a further level that would give meat eaters some further choices relating to how their meat is prepared.
To set this up, all we need to do is take the individual items from that ‘Meat’ column and add each one as a new column header:
Then we simply list the new options for each type of meat below the relevant header:
Now here’s the magic: as soon as we add another column to our input table and set it up with data validation – which I did simply by clicking on the bottom right corner of the cell with the word ‘Human’ and dragging it across – then Excel picks up on the fact that there’s a sub-subcategory, and serves it up to us. *BING!* Order up!
Add code, and stir-fry for 10 milliseconds
As mentioned earlier, in addition to Roger’s great method, I’ve written some code that clears out any downstream entries in the event that an upstream entry is changed. It’s in the sample workbook already, all set to go. But here’s the actual code, for you VBA nerds. (Special thanks to Gabor Madacs for some enhancement suggestions)
Option Explicit
Const CHOOSE = "Choose…"
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Dim targetCell As Range
Dim nextCell As Range
Dim oldCalc As Excel.XlCalculation
If Not Intersect(Target, [DataEntryTable]) Is Nothing Then
If [Radio_Choice] = 1 Then
With Application
.EnableEvents = False
.ScreenUpdating = False
oldCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each targetCell In Target
'Clear any cells that use 'SubList' to the right of targetCell in the current table.
If targetCell.Column < (targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - 1) Then 'there are table cells to the right
For Each nextCell In targetCell.Offset(, 1).Resize(, targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - targetCell.Column - 1)
If HasValidationFormula(nextCell) Then
If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = ""
End If
Next nextCell
End If
'Perform different action depeding on whether we're dealing with a 'MainList' dropdown
' or a 'SubList' dropdown
If HasValidationFormula(targetCell) Then
Select Case targetCell.Validation.Formula1
Case "=MainList"
If targetCell.Value = "" Then
targetCell.Value = CHOOSE
ElseIf targetCell.Value = CHOOSE Then
'Do nothing.
Else
targetCell.Offset(, 1).Value = CHOOSE
End If
Case "=SubList"
If targetCell.Value = "" Then
targetCell.Value = CHOOSE
ElseIf targetCell.Offset(, -1).Value = CHOOSE Then
targetCell.Value = ""
ElseIf targetCell.Value = CHOOSE Then
'Do nothing
Else
Set nextCell = targetCell.Offset(, 1)
If HasValidationFormula(nextCell) Then
If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = CHOOSE
End If
End If
End Select
End If
Next targetCell
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = oldCalc
End With
End If
End If
Exit Sub
ErrorHandler:
With Application
.EnableEvents = True
.ScreenUpdating = True
If oldCalc <> 0 Then .Calculation = oldCalc
End With
MsgBox Err.Description, vbCritical, Name & ".Worksheet_Change()"
End Sub
Private Function HasValidationFormula(cell As Range) As Boolean
On Error GoTo ValidationNotExistsError
If cell.Validation.Formula1 <> "" Then
HasValidationFormula = True
Else
HasValidationFormula = False
End If
Exit Function
ValidationNotExistsError:
HasValidationFormula = False
End Function
Hungry for more?
Here’s some related Posts at Chandoo.org:
Download the file
To see how this is done, download this file and enable macros:
Dynamic-Dependent-dropdowns-20140214
About the Author.
Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this:
=NOT(EVEN(PROPER(OR(RIGHT(TODAY())))))
That’s right, pure #VALUE!
Find out more at http:www.heavydutydecisions.co.nz
20 Responses to “Simulating Dice throws – the correct way to do it in excel”
You have an interesting point, but the bell curve theory is nonsense. Certainly it is not what you would want, even if it were true.
Alpha Bravo - Although not a distribution curve in the strict sense, is does reflect the actual results of throwing two physical dice.
And reflects the following . .
There is 1 way of throwing a total of 2
There are 2 ways of throwing a total of 3
There are 3 ways of throwing a total of 4
There are 4 ways of throwing a total of 5
There are 5 ways of throwing a total of 6
There are 6 ways of throwing a total of 7
There are 5 ways of throwing a total of 8
There are 4 ways of throwing a total of 9
There are 3 ways of throwing a total of 10
There are 2 ways of throwing a total of 11
There is 1 way of throwing a total of 12
@alpha bravo ... welcome... 🙂
either your comment or your dice is loaded 😉
I am afraid the distribution shown in the right graph is what you get when you throw a pair of dice in real world. As Karl already explained, it is not random behavior you see when you try to combine 2 random events (individual dice throws), but more of order due to how things work.
@Karl, thanks 🙂
When simulating a coin toss, the ROUND function you used is appropriate. However, your die simulation formula should use INT instead of ROUND:
=INT(RAND()*6)+1
Otherwise, the rounding causes half of each number's predictions to be applied to the next higher number. Also, you'd get a count for 7, which isn't possible in a die.
To illustrate, I set up 1200 trials of each formula in a worksheet and counted the results. The image here shows the table and a histogram of results:
http://peltiertech.com/WordPress/wp-content/img200808/RandonDieTrials.png
@Jon: thanks for pointing this out. You are absolutely right. INT() is what I should I have used instead of ROUND() as it reduces the possibility of having either 1 or 6 by almost half that of having other numbers.
this is such a good thing to learn, helps me a lot in my future simulations.
Btw, the actual graphs I have shown were plotted based on randbetween() and not from rand()*6, so they still hold good.
Updating the post to include your comments as it helps everyone to know this.
By the way, the distribution is not a Gaussian distribution, as Karl points out. However, when you add the simulations of many dice together (i.e., ten throws), the overall results will approximate a Gaussian distribution. If my feeble memory serves me, this is the Central Limit Theorem.
@Jon, that is right, you have to nearly throw infinite number of dice and add their face counts to get a perfect bell curve or Gaussian distribution, but as the central limit theorem suggests, our curve should roughly look like a bell curve... 🙂
[...] posts on games & excel that you may enjoy: Simulating Dice throws in Excel Generate and Print Bingo / Housie tickets using this excel Understanding Monopoly Board [...]
[...] Correct way to simulate dice throws in excel [...]
[...] Simulate dice throws in excel [...]
I'm afraid to say that this is a badly stated and ambiguous post, which is likely to cause errors and misunderstanding.
Aside from the initial use of round() instead of int(),.. (you've since corrected), you made several crucial mistakes by not accurately and unambiguously stating the details.
Firstly, you said:
"this little function generates a random fraction between 0 and 1"
Correctly stated this should be:
"this little function generates a random fraction F where 0 <= F < 1".
Secondly, I guess because you were a little fuzzy about the exact range of values returned by rand(), you have then been just as ambiguous in stating:
"I usually write int(rand()*12)+1 if I need a random number between 0 to 12".
(that implies 13 integers, not 12)
Your formula, does not return 13 integers between 0 to 12.
It returns 12 integers between 1 and 12 (inclusive).
-- As rand() returns a random fraction F where 0 <= F < 1, you can obviously can only get integers between 1 and 12 (inclusive) from your formula as stated above, but clearly not zero.
If you had said either:
"I usually write int(rand()*12) if I need a random number between 0 to 11 (inclusive)",
or:
"I usually write int(rand()*12)+1 if I need a random number between 1 to 12 (inclusive)"
then you would have been correct.
Unfortunately, you FAIL! -- repeat 5th grade please!
Your Fifth Grade Maths Teacher
Idk if I'm on the right forum for this or how soon one can reply, but I'm working on a test using Excel and I have a table set up to get all my answers from BUT I need to generate 10,000 answers from this one table. Every time, I try to do this I get 10,000 duplicate answers. I know there has to be some simple command I have left out or not used at all, any help would be extremely helpful! (And I already have the dice figured out lol)
Roll 4Dice with 20Sides (4D20) if the total < 20 add the sum of a rerolled 2D20. What is the average total over 10,000 turns? (Short and sweet)
Like I said when I try to simulate 10,000turns I just get "67" 10,000times -_- help please! 😀
@Justin
This is a good example to use for basic simulation
have a look at the file I have posted at:
https://rapidshare.com/files/1257689536/4_Dice.xlsx
It uses a variable size dice which you set
Has 4 Dice
Throws them 10,000 times
If Total per roll < 20 uses the sum of 2 extra dice Adds up the scores Averages the results You can read more about how it was constructed by reading this post: http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
Oh derp, i fell for this trap too, thinking i was makeing a good dice roll simulation.. instead of just got an average of everything 😛
Noteably This dice trow simulate page is kinda important, as most roleplay dice games were hard.. i mean, a crit failure or crit hit (rolling double 1's or double 6's) in a a game for example dungeons and dragons, if you dont do the roll each induvidual dice, then theres a higher chance of scoreing a crit hit or a crit failure on attacking..
I've been working on this for awhile. So here's a few issues I've come across and solved.
#1. round() does work, but you add 0.5 as the constant, not 1.
trunc() and int() give you the same distributions as round() when you use the constant 1, so among the three functions they are all equally fair as long as you remember what you're doing when you use one rather than the other. I've proven it with a rough mathematical proof -- I say rough only because I'm not a proper mathematician.
In short, depending on the function (s is the number of sides, and R stands in for RAND() ):
round(f), where f = sR + 0.5
trunc(f), where f = sR + 1
int(f), where f = sR + 1
will all give you the same distribution, meaning that between the three functions they are fair and none favors something more than the others. However...
#2. None of the above gets you around the uneven distribution of possible outcomes of primes not found in the factorization of the base being used (base-10, since we're using decimal; and the prime factorization of 10 is 2 and 5).
With a 10-sided die, where your equation would be
=ROUND(6*RAND()+0.5)
Your distribution of possible values is even across all ten possibilities.
However, if you use the most basic die, a 6-sided die, the distributions favor some rolls over others. Let's assume your random number can only generate down to the thousandths (0.000 ? R ? 0.999). The distribution of possible outcomes of your function are:
1: 167
2: 167
3: 166
4: 167
5: 167
6: 166
So 4 and 6 are always under-represented in the distribution by 1 less than their compatriots. This is true no matter how many decimals you allow, though the distribution gets closer and closer to equal the further towards infinite decimal places you go.
This carries over to all die whose numbers of sides do not factor down to a prime factorization of some exponential values of 2 and 5.
So, then, how can we fix this one, tiny issue in a practical manner that doesn't make our heads hurt or put unnecessary strain on the computer?
Real quick addendum to the above:
Obviously when I put the equation after the example of the 10-sided die, I meant to put a 10*RAND() instead of a 6*RAND(). Oops!
Also, where I have 0.000 ? R ? 0.999, the ?'s are supposed to be less-than-or-equal-to signs but the comments didn't like that. Oh well.
How do you keep adding up the total? I would like to have a cell which keeps adding up the total sum of the two dices, even after a new number is generated in the cells when you refresh or generate new numbers.
So, how do you simulate rolling 12 dice? Do you write int(rand()*6) 12 times?
Is there a simpler way of simulating n dice in Excel?
I've run this code in VBA
Sub generate()
Application.ScreenUpdating = False
Application.Calculation = False
Dim app, i As Long
Set app = Application.WorksheetFunction
For i = 3 To 10002
Cells(i, 3).Value = i - 2
Cells(i, 4).Value = app.RandBetween(2, 12)
Cells(i, 5).Value = app.RandBetween(1, 6) + app.RandBetween(1, 6)
Next
Application.ScreenUpdating = True
Application.Calculation = True
End Sub
But I get the same distribution for both columns 4 and 5
Why ?
@Mohammed
I would expect to get the same distribution as you have effectively used the same function