Dynamically Changing Sum Ranges

1

1543mikep

New Member
Joined
Dec 7, 2006
Messages
4
I have a problem that involves summing a range of cells that is conditional on past events (or so I think).

I'm working out gaming theory to give as a class assignment and want them to show the maximum profitable gaming model dependent on probability of wins.

The first scenario is easy. Toss a coin and if you win, get a $1, if lose give up $1.
Based on using fair coin the expected outcome is $0 over any proper sampling size.

Now this is the part I am having difficulty with.
Using a different model, let's say that you are using the same coin are looking to win 2 times out of 6 times and will increase the bet by a factor with each loss until you win. Once you win any 2 times out of 6 you'd start the series over again.

So you'd bet $1 on the first toss.
If you lose, bet $2 on the second toss.
If you win, then the series would end, you'd be up by $1 and start over again betting $1 - (Toss 1: -$1; Toss 2: +$2 --> series ends and net is $1). If you were only doing straight bets then the net result would be $0 - (Toss 1: -$1; Toss 2: +$1 --> series ends and net is $0)

Let's say the series for incremental from Bet1 to Bet6 is: $1-$2-$4-$8-$12-$16

I was using a rudimentary setup of randomly showing lines of wins and losses but couldn't figure out how to keep track of every changing series.

One series could be: win-loss-loss-win for a total series count of 4 before starting the betting series over again;
or: loss-win-loss-loss-loss-win for a total series count of 6 before staring the betting series over again;
or any other permutation.

Is there a simple setup and formula so that I could copy and paste over any amount of lines? It's easy to do for a handful of coin tosses, but not over thousands where we can play with different amounts to bet or perhaps create longer series, (e.g. 8 or 10).

I certainly hope this makes sense and someone can help.

p.s. I did do a search in the forum without much luck...

Details:
Windows 7-64bit
Excel 2010

pps. I tried using the MrExcelHtml and hopefully didn't screw it up too badly - thank you for your patience in advance!



Excel 2010
ABCDEFGHIJKL
1EXPECTED:ACTUAL:
2Change Win Ratio HereStrategy Results after 10,000 tradesTradeRisk/Reward
3Win %50No MM$461$1
4Loss %50Win % (actual)50%2$2
5(Check)100Max Drawdown-$183$4
64$8
7Change Win/Reward $ HereNumber of Wins5,021Dynamically change how many cells to look back5$12
8Risk$1Number of Losses4,979As soon as see a second win, start series over again6$16
9Reward$1Total Trades10,000
10
11No MMNo MM
12Coin Toss#ResultWin/LossWin CountLoss CountAmountAccum ProfitComments (only for guidance)Trade levelResult:AmountAccum Profit
13190loss01-$1-$1Series Start1loss-$1-$1
1422win10$1$21st win (out of potental 6)2win$2$1
15363loss01-$1$13loss-$4-$3
1647win10$1$22nd win - series ends4win$8$5
17541win10$1$3Series Re-start (& 1st win)1win$1$6
18624win10$1$42nd win - series ends2win$2$8
19719win10$1$5Series Re-start (& 1st win)1win$1$9
20854loss01-$1$42loss-$2$7
21982loss01-$1$53loss-$4$3
221096loss01-$1$44loss-$8-$5
231144win10$1$52nd win - series ends5win$12$7
241259loss01-$1$4Series Re-start1loss-$1$6
251352loss01-$1$32loss-$2$4
261496loss01-$1$23loss-$4$0
271542win10$1$32nd win - series ends4win$8$8
281634win10$1$4Series Re-start1win$1$9
291743win10$1$52nd win - series ends2win$2$11
301835win10$1$6Series Re-start1win$1$12
311973loss01-$1$52loss-$2$10
322058loss01-$1$43loss-$4$6
332197loss01-$1$34loss-$8-$2
342257loss01-$1$25loss-$12-$14
352335win10$1$32nd win - series ends6win$16$2
362478loss01-$1$2Series Re-start1loss-$1$1
372556loss01-$1$12loss-$2-$1
382678loss01-$1$03loss-$4-$5
392763loss01-$1-$14loss-$8-$13
402868loss01-$1-$25loss-$12-$25
412974loss01-$1-$3Series Re-start (6 flips with only 1 win)6loss-$16-$41
423064loss01-$1-$4Series Re-start1loss-$1-$42
Sheet1
Cell Formulas
RangeFormula
B4=100-B3
B5=SUM(B3:B4)
B13=RANDBETWEEN(0,100)
E3=G10012
E4=E7/(E7+E8)
E5=MIN(G13:G4008)
E7=SUM(D13:D10012)
E8=SUM(E13:E10012)
E9=SUM(E7:E8)
E13=IF(D13=0,1,0)
C13=IF(B13<=$B$3,"win","loss")
D13=IF(C13="win",1,0)
F13=IF(C13="loss",$B$8*-1,$B$9)
G13=SUM($F$13:F13)
K13=IF(J13="loss",VLOOKUP(I13,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I13,$K$3:$L$8,2,FALSE))
K14=IF(J14="loss",VLOOKUP(I14,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I14,$K$3:$L$8,2,FALSE))
K15=IF(J15="loss",VLOOKUP(I15,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I15,$K$3:$L$8,2,FALSE))
K16=IF(J16="loss",VLOOKUP(I16,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I16,$K$3:$L$8,2,FALSE))
K17=IF(J17="loss",VLOOKUP(I17,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I17,$K$3:$L$8,2,FALSE))
K18=IF(J18="loss",VLOOKUP(I18,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I18,$K$3:$L$8,2,FALSE))
K19=IF(J19="loss",VLOOKUP(I19,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I19,$K$3:$L$8,2,FALSE))
K20=IF(J20="loss",VLOOKUP(I20,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I20,$K$3:$L$8,2,FALSE))
K21=IF(J21="loss",VLOOKUP(I21,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I21,$K$3:$L$8,2,FALSE))
K22=IF(J22="loss",VLOOKUP(I22,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I22,$K$3:$L$8,2,FALSE))
K23=IF(J23="loss",VLOOKUP(I23,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I23,$K$3:$L$8,2,FALSE))
K24=IF(J24="loss",VLOOKUP(I24,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I24,$K$3:$L$8,2,FALSE))
K25=IF(J25="loss",VLOOKUP(I25,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I25,$K$3:$L$8,2,FALSE))
K26=IF(J26="loss",VLOOKUP(I26,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I26,$K$3:$L$8,2,FALSE))
K27=IF(J27="loss",VLOOKUP(I27,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I27,$K$3:$L$8,2,FALSE))
K28=IF(J28="loss",VLOOKUP(I28,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I28,$K$3:$L$8,2,FALSE))
K29=IF(J29="loss",VLOOKUP(I29,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I29,$K$3:$L$8,2,FALSE))
K30=IF(J30="loss",VLOOKUP(I30,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I30,$K$3:$L$8,2,FALSE))
K31=IF(J31="loss",VLOOKUP(I31,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I31,$K$3:$L$8,2,FALSE))
K32=IF(J32="loss",VLOOKUP(I32,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I32,$K$3:$L$8,2,FALSE))
K33=IF(J33="loss",VLOOKUP(I33,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I33,$K$3:$L$8,2,FALSE))
K34=IF(J34="loss",VLOOKUP(I34,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I34,$K$3:$L$8,2,FALSE))
K35=IF(J35="loss",VLOOKUP(I35,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I35,$K$3:$L$8,2,FALSE))
K36=IF(J36="loss",VLOOKUP(I36,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I36,$K$3:$L$8,2,FALSE))
K37=IF(J37="loss",VLOOKUP(I37,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I37,$K$3:$L$8,2,FALSE))
K38=IF(J38="loss",VLOOKUP(I38,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I38,$K$3:$L$8,2,FALSE))
K39=IF(J39="loss",VLOOKUP(I39,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I39,$K$3:$L$8,2,FALSE))
K40=IF(J40="loss",VLOOKUP(I40,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I40,$K$3:$L$8,2,FALSE))
K41=IF(J41="loss",VLOOKUP(I41,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I41,$K$3:$L$8,2,FALSE))
K42=IF(J42="loss",VLOOKUP(I42,$K$3:$L$8,2,FALSE)*-1,VLOOKUP(I42,$K$3:$L$8,2,FALSE))
L13=SUM($K$13:K13)
L14=SUM($K$13:K14)
L15=SUM($K$13:K15)
L16=SUM($K$13:K16)
L17=SUM($K$13:K17)
L18=SUM($K$13:K18)
L19=SUM($K$13:K19)
L20=SUM($K$13:K20)
L21=SUM($K$13:K21)
L22=SUM($K$13:K22)
L23=SUM($K$13:K23)
L24=SUM($K$13:K24)
L25=SUM($K$13:K25)
L26=SUM($K$13:K26)
L27=SUM($K$13:K27)
L28=SUM($K$13:K28)
L29=SUM($K$13:K29)
L30=SUM($K$13:K30)
L31=SUM($K$13:K31)
L32=SUM($K$13:K32)
L33=SUM($K$13:K33)
L34=SUM($K$13:K34)
L35=SUM($K$13:K35)
L36=SUM($K$13:K36)
L37=SUM($K$13:K37)
L38=SUM($K$13:K38)
L39=SUM($K$13:K39)
L40=SUM($K$13:K40)
L41=SUM($K$13:K41)
L42=SUM($K$13:K42)
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Does anyone have any ideas?

It looks like the columns on the right side were cut off and I've attached a sample:


Excel 2010
HIJKL
12Comments (only for guidance)Trade levelResult:AmountAccum Profit
13Series Start1loss-$1-$1
141st win (out of potental 6)2win$2$1
153loss-$4-$3
162nd win - series ends4win$8$5
17Series Re-start (& 1st win)1win$1$6
182nd win - series ends2win$2$8
19Series Re-start (& 1st win)1win$1$9
202loss-$2$7
213loss-$4$3
224loss-$8-$5
232nd win - series ends5win$12$7
24Series Re-start1loss-$1$6
252loss-$2$4
263loss-$4$0
272nd win - series ends4win$8$8
28Series Re-start1win$1$9
292nd win - series ends2win$2$11
30Series Re-start1win$1$12
312loss-$2$10
323loss-$4$6
334loss-$8-$2
345loss-$12-$14
352nd win - series ends6win$16$2
Sheet1
 
Upvote 0
I haven't gone through your sample to work out exactly what you want but this sounds like you need to use dynamic named ranges. The best description I've found of these is on the OzGrid site. Just Google it and you should find enough information to help you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
346
MARK858
MARK858
P
  • Question
Offset Processes
Replies
7
Views
276
SanjayGMusafir
SanjayGMusafir
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
171
Tetra201
Tetra201
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
162
gtd526
gtd526
VinceF
  • Question
Circular Reference Error
Replies
8
Views
393
VinceF
VinceF

Forum statistics

Threads
1,215,085
Messages
6,123,030
Members
449,092
Latest member
ikke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top