Modeling flip coin game multiple times

In row 2, you add 1.5 for a win but in all subsequent rows you only add 1 for a win. I corrected this in my solution.

Running 1000 games with 100 coin tosses each is mathematically the same as running one game with 100,000 coin tosses. Charting the outcome of 1000 games doesn't tell you anything; the results should be random with the EV being the average result, if your random number generator is valid.

Nevertheless, to answer you question, all you have to do is repeat your game 1000 times. I have transposed your data so that on one sheet I have a column for each coin toss, 100 columns. There are 1000 rows, one for each game.

Then on another sheet I used those coin tosses to determine the value outcome. Putting the tosses and value on two different sheets makes the formulas very easy.

A third sheet has the chart of the 1000 games.

Hit F9 to force a recalculate and generate a new set of games.

The expected value, as you probably know, is 0.25 per game, so the outcome will be 125 per 100 games with starting value of 100.

You have a mistake in your second formula:

	A	B	C
1		100.0	B1: Input
2	1	101.5	B2: =IF(A2=1,B1+1.5,B1-1)
3	0	100.5	B3: =IF(A3=1,B2+1,B2-1)

After the first game, win and lose are +1/-1, not +1.5/-1

EDIT: Too slow.