Small FAQ about download Book files are stored on servers owned by you? We do not store files, because it is prohibited. Our site uses the API of third-party sites that store files. That is, we are doing the same thing as Google, only within the framework of one subject.

Author:Samugore Zukasa
Language:English (Spanish)
Published (Last):6 May 2013
PDF File Size:18.29 Mb
ePub File Size:4.19 Mb
Price:Free* [*Free Regsitration Required]

In cell A1 is a formula that generates a random number between 1 and 6. According to that outcome, the colored die shows the appropriate number of eyes at their proper locations. Each time the random number changes, the die adjusts accordingly. On each recalculation, this function generates a new random number between 0 and 1. Because we want numbers between 1 and 6, we need to multiply by 6, round the number down by using the INT function, and then add 1 to the end result.

I decided not to use that function, because in pre Excel versions this function was only available through the Analysis Toolpak. To generate a new random number, you either hit the key F9 or the combination of the Shift key and the F9 key. In this file, I would recommend the latter option Shift F9 , since that would only recalculate the current sheet — otherwise you would recalculate all sheets in this file, which may take lots of calculating time.

Finally, we need to regulate which eyes should pop up for each new random number. This is done inside some of the die cells by using the IF function.

This function is a "decision maker," which determines whether a specific eye should be on or off. What you need to do 1. Nested functions are very common in Excel; for more information, see Appendix 2. The two double quotes in the last argument return an empty string, showing up as nothing. In this case, the function OR is nested inside IF.

The function OR returns "true" if any of the enclosed arguments is "true. This shortcut toggles the sheet, back and forth, between value-view and formula-view. Each die "listens" to a random number above it, to its left.

The settings for each die are similar to what we did in Simulation 1. The number of eyes for each die is plotted in a column chart below the dice. A die that shows six eyes gets marked with a color. When there are at least 2 dice in a row with six eyes, all dice get marked at the same time.

What you need to know There is not much new on this sheet. The main difference is that we need 6 different cells with a RAND function in order to control the six die displays. Each die has the same structure as the one used in Simulation 1.

In addition, we use conditional formatting to change colors of the dice when they show six eyes, or contain at least two dice with six eyes. Make sure all six dice are set up as was done in Simulation 1, but each die is connected to the random cell just above it. Do something similar for the other five dice. By using Sh F9, you may hit a situation like below where at least two dice have six eyes F9 recalculates all the sheets of the entire file, whereas Sh F9 only does so for the current sheet and may take less time.

Frequencies What the simulation does Open file 1-Gambling. In column F, we calculate how often we had a hit of 2 eyes in total, 3 eyes, and so on, up to 12 eyes. The frequencies are plotted in a graph. Cell F14 calculates the average of column C. It turns color for extreme values. The average is also plotted in the graph as a vertical line — based on the two sets of coordinates shown in EF The curve keeps changing each time we hit Shift F9.

Very rarely does it come close to a normal distribution with a mean somewhere in the center. The chance for this to happen would increase if we would have used more dice and more repeats.

This is a so-called array function more on this in Simulations 62 and Such functions return an array or require an array for intermediate calculations. All array functions have to be implemented with three keys at the same time: Ctr Sh Enter. To make this function work, you need to select all the cells that are going to hold the frequency values all at once, before you use the array function. Do not type the braces; they come automatically with Ctr Sh Enter. Changing colors of cells under certain conditions is done with so-called conditional formatting located under the Home tab.

When the specified conditions kick in, the cell will be formatted according to certain settings. In our case, we want to flag averages under 5. Column C sums the eyes of both dice. Notice the braces in the formula bar. The average line in the graph is based on a new series of values with two sets of coordinates: EE17 for the X-values, and FF17 for the Y-values.

Hit Sh F9 for new simulations. As they say, "Results may vary" see below. Roulette Machine What the simulation does Open file 1-Gambling. In column B, you type 1 if you expect the next number to be odd — otherwise 2 for even.

Column E keeps the score: it adds 1, when your prediction was correct — otherwise it subtracts 1. Once you are finished, you can just empty your predictions in B2:B — and start all over you may need Sh F9, though.

What you need to know Most people believe that if they keep consistently betting "odd," the ball will most certainly land on an odd number sometime soon. This is called "the law of averages" which says, the longer you wait for a certain random event, the more likely it becomes.

Do not believe it! Try it out in this "real life" simulation and find out how the casino makes money on people who think that way. You may initially gain but eventually lose. Column A has 1, random numbers. They were once generated and then changed into values. You will not see all of them because of them are hidden through conditional formatting. Copy the formula down to E The MOD function divides a number by 2 and returns the remainder. The remainder is either 0 or 1 here. If what the user had predicted in column B is correct, the score goes up by 1 — otherwise down by 1.

Notice that B is locked but 2 is not see Appendix 1 on this issue. Format the entire range to a white font — which means you will not see this number if the cell in the next column of the previous row is still empty, but it will show its value once that cell has been filled with either 1 odd or 2 even.

So the cell accepts only 1s or 2s. Now you can make your predictions for every next roulette outcome — either 1 or 2. You may have to hit Sh F9 each time if the file has been set to manual calculation or change that. To start all over, just clear the colored cells in column B and again you may have to manually recalculate the sheet.

Notice how easily you can lose by going for the "law of averages" by repeating constantly 1 for "odd" or 2 for "even. The player has chances in column A to go for odd or even.

If the choice was correct, the count in column A goes up by 1, otherwise it goes down by 1. Next we simulate that this addicted player repeats the game for some twenty more times. For each game, we calculate average, minimum, maximum, standard deviation, and the final score in column H. At the end, we calculate how often the player had a positive final score, and how often a negative one. This looks like much more work than it actually is Then we simulate doing this 20 more times in the right table.

I consider this an ideal tool for what-if analysis. How does it work? Usually Data Tables have a formula in the first cell — which would be cell C1 in our case. Based on that formula, a Data Table typically uses a row input of variables and a column input of variables to recalculate the formula placed at its origin. In this case we use a what-if table merely to trick Excel into simulating 20 or many more iterations of column A.

We do so by not placing a formula at the origin, by leaving the row-input argument empty, and having the col-input argument refer to an empty cell somewhere outside the table.

Yes, that does the trick! Copy this formula down for rows. Place in cell H2 a reference to the last cell in column A. Now select C2:H22 yes, the empty cell C2, not D2. Set the row input to nothing and the column input to an empty cell outside the table say, J2.

Do not type this formula or the braces — both kick in automatically. Notice how the Data Table runs 20 x choices each time you hit the keys Sh F9. Random Walk What the simulation does Open file 1-Gambling. We keep doing this many times and then check how far we end up being from home. We will simulate first 50 steps for one dimension north-south, in column B, plotted in the top graph as up and down.

Then we will do this for two dimensions north-east-south-west, in columns B and C, plotted in the bottom graph. As it turns out, we could make big "gains" and drift far away from where we started.


Gerard Verschuuren

After that, he became a participant of the six-member Human Adaptability Project team led by professor John Huizinga , M. Verschuuren also studied philosophy at Leiden University and wrote, under supervision of professor Marius Jeuken , a thesis on the impact of the Harvard philosopher and mathematician Alfred North Whitehead on research in biology. He further specialized in philosophy of science , in particular in philosophy of biology , at VU University Amsterdam. Verschuuren concluded his post-graduate studies with a doctoral thesis on the use of models in the sciences.


100 Excel Simulations

Financial Modeling in Excel For Dummies. Excel In Depth. Please review your cart. Refresh and try again. Excel Simulations : Gerard M.


Excel Simulations by Gerard M. Verschuuren - PDF free download eBook



Product View Status


Related Articles