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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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

S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
165
SBExcel123
S
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
251
etaf
etaf
togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
1K
MARK858
MARK858
K
  • Question Question
create fixtures but 2 vs 2
Replies
7
Views
149
kerm007
K
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
182
Pbobas
P

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

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