Notes on Creating an Excel File for Four 7-steps Random Walk
1. Go to file -Page set up - Fit to:1 (That will make your whole screen your working page)
2. Go to View- Click Normal
3. Enter A in cell A10, B in B10, C in C10, D in D10 to label the four columns where you will input data. (Leave rows 1-9 blank in case you have to do your heading on the file)
4. Go to cell A11, enter the following formula =(-1)^(Int(2*Rand()))
(-1) will ensure that you have negative and positive values to represent the steps
Rand () function only generates numbers that are equal to or greater than 0, but less than1; 0n<1
Int() function tells Excel to drop the decimal part of the product generated by 2*Rand(); thus the exponent will always be either 0 or 1. Excel reads 0 as even exponent, and 1 as odd exponent.
Hint: Negative values raised to even exponents have positive answers; but negative values raised to odd exponents have negative answers.
5. Copy the formula in cell A11 to B11, C11, D11 by clicking on A11 and dragging it to D11 (the values should be either 1 or -1)
6. Go to cell A12, enter =A11+ the formula written in step 4 (the difference between cell A11 and A12 should either be 1 or -1)
7. Hit apple key+C to copy the formula in A12, then press and hold the Shift key and click on cell D17
8. Hit apple key+V to copy the formula in all the highlighted cells that will represent the 7-steps of the random walk by four different people.
9. Highlight the four values in row A17 to D17 to be formatted- (Format – cell-border-style (use thick line)-color-outline-OK).
10. Go to E17, write the two words final values.
11. Go to F14, write the two words seed value.
12. Go to F15 enter any value. That is where your seed value is. Every time you want to see change in your table or graph, go to cell F15 and change it (every time something is changed in the spreadsheet the random function re-evaluates itself and gives new values; therefore, the final walk and the graph change accordingly). You can format cell F15 as in step 9; use a different color.
Now you are ready to do your graph of the 7-steps of four Random Walkers.
1. Highlight the entire table, from A10 to D17.
2. Go to the chart icon or (Insert-chart)
3. Click on Line, then click on the first chart sub-type (the explanation window below will say: Line. Displays trend over time or categories)
4. Click next twice to write the title of the graph: Seven Random Steps of Four Walkers
5. Click finish. You will see that the 4 colored lines representing the paths are not starting at the Y-axis.
6. Put your mouse on top of one of the numbers on the x-axis and hit control-click. Select format axis.
7. Go to scale and de-select the check on “Value (Y) axis crosses between categories - OK.
8. Click anywhere on the chart area to select it.
9. Control-click on it to select chart options.
10. On chart options, go to axes and de-select Category (x) axis. Click OK.
11. Control-click on y-axis, select format axis, scale. Deselect major unit (enter 1).
Homework:
1. Explain why all the possible final values for a 7-step random walk will be odd integers from -7 to 7.
2. Enter an arbitrary seed value into the highlighted cell. Record the final values. Repeat this process 32 times so as to generate a record of the final values from 128 7-step random walks.
3. Count the number of times that each of the possible final values actually occurred. Compare these values to those predicted by Pascal’s Triangle. Explain any discrepancies.
R. Valcin, Presenter