Create a Data Table from a Blank Cell

Wednesday, January 01, 2020 Mr King Excel Tips,

Create a Data Table from a Blank Cell

Simon Benninga tells a story of a game called Penny Pitching. You and another student would each flip a penny. If you get one head and one tail, you win the penny. If the coins match (heads/heads or tails/tails), the other student gets the penny.

It is simple to model this game in Excel. If RAND()>.5, you win a penny. Otherwise, you lose a penny. Do that for 25 rows and chart the result. Press F9 to play 25 more rounds.

This is known as a Random Walk Down Wall Street. Simon would point out a result like the one below, where a hot young stock analyst is on fire with a series of wins, but then a series of losses wipe out the gain. This is why they say that past results are not a guarantee of future returns.

25 rounds of penny pitching. A formula =IF(RAND()>0.5,1,-1)+B3 keeps track of the cumulative winnings or losses.

Instead of 25 trials, extend your table in columns A and B to run 250 trials. This would be like playing one round of penny pitching every work day for a year. Build a row of statistics about that year, as shown below.

In G14:L14, calculate statistics from the current 250 rounds of Penny Pitching. What was the Max, Min, Average, Win Streak, Lose Streak, and Final result.

Create an odd data table where the blank cell in column F is the corner cell. Leave Row Input Cell blank. Specify any blank cell for Column Input Cell.

The blank cell in F14 becomes the top left corner cell of the analysis. Select F14:K34. In the Data Table dialog, leave the Row Input Cell blank and choose any blank cell as the Column Input Cell. This only works because of the RAND() in the model.

When you create the table, Excel runs the 250 coin flips, once per row. This 30-row table models the entire career of a stock analyst. Every time you press F9, Excel runs the 250-row model for each of 30 years. You can watch an entire 30-year career be modeled with the simple press of F9.

You've now modeled 250 flips times 30 iterations. You can see the stats over all and get a picture for the range of possibilities.

Thanks to Professor Simon Benninga for showing me this technique.

Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1
#evba #etipfree #kingexcel