Resetting a fibonacci sequence

[davidalexander1959]

I have a very simple problem but I just can't figure it out. There are two columns. The left column has random 0 and 1 going down to 5000. The right column has the sequence starting at 10,10, 20, 30, 50, 80, 130, 210.
It's a betting sequence. A loss is a 0 and a win is a 1. Whenever the left column shows a 0 the bet increases until a win or the sequence reaches 210 when the sequence restarts at 10 in the row following the 1 or win. I have the formula sorted to start the sequence but when it resets. Column A has the 1 and 0. Column B is the sequence that needs to run. Column D has my formula I have started creating. As you can see it starts out ok. When it resets it is starting at 10 again but the next cell down should be another 10 to start the sequence off again. My formula adding the two above and putting the answer in D13. The 220 needs to be the second 10 to start the sequence again.

Thank you,
David

Roulette 123.xlsx
	A	B	C	D	E
1	0	10
2	0	10
3	1	20		0
4	0	10		10	10
5	0	10		10
6	0	20		20
7	0	30		30
8	0	50		50
9	0	80		80
10	0	130		130
11	0	210		210
12	0	10		10
13	1	10		220
14	0	20		10
15	1	30		230
16	0	10		10
17	0	10		240
18	0	20		250
19	1	30		490
20	0	10		10
21	0	10		500
22	0	20		510
23	0	30		1010
24	0	50		1520
25	0	80		2530
26	0	130		4050
27	0	210		6580
28	0	10		10630
29
30	=IF(OR(D3=210,A3=TRUE),$E$4,SUM(D2+D3))
Sheet2

Cell Formulas
Range	Formula
D3:D28	D3	=IF(OR(D2=210,A2=1),$E$4,SUM(D1+D2))

 

[footoo]

You don't need columns D & E.
Put in B3 :
=IF(B2=210,10,B2+((B1<>210)*B1))

Perhaps I've misunderstood what you want to do.

 

[davidalexander1959]

You don't need columns D & E.
Put in B3 :
=IF(B2=210,10,B2+((B1<>210)*B1))

Perhaps I've misunderstood what you want to do.

Thank you for your reply. Unfortunately your formula didn't work. When I have the correct formula it will match Column B meaning D4 will go into B4, D5 into B5. Column B is how the sequence is suppose to come out. Column D has my formula but as you can see the cells highlighted in orange should be the second 10 in the fibonacci sequence. When the formula in Col D is correct it will match Col B. Eventually my formula will go in Column B. Sorry I don't think I've drawn up my sheet very well. See below for new version.
My formula is adding the first two cells directly above creating and resetting the fibonacci sequence but the orange cell is adding the 210 and 10 above to give 220. The sequence is working correctly except for the total in the orange cells. If that total in the orange cells was the second 10 then the sequence will run again. I think I need another IF to place the second 10 in the sequence. The sequence I want to run is 10,10, 20, 30, 50, 80, 130, 210 but the second 10 in the sequence is adding the first 10 and 210 from the end of the sequence or whatever is the last number in the sequence before it resets.
Column B shows how it is suppose to run. See how the numbers in the blue cells are the final number in the sequence. Reset to 10, 10, 20, 30, and so on until a win or it reaches 210. Reset.

Hope that is a little clearer.

Thanks,
David

Roulette 123.xlsx
	A	B	C	D
4	0	10	10	10
5	0	10	10
6	0	20	20
7	0	30	30
8	0	50	50
9	0	80	80
10	0	130	130
11	0	210	210
12	0	10	10
13	1	10	220
14	0	20	10
15	1	30	230
16	0	10	10
17	0	10	240
18	0	20	250
19	1	30	490
20	0	10	10
21	0	10	500
22	0	20	510
23	0	30	1010
24	0	50	1520
25	0	80	2530
26	0	130	4050
27	0	210	6580
28	0	10	10630
29
30	=IF(OR(D3=210,A3=TRUE),$E$4,SUM(D2+D3))
Sheet2

Cell Formulas
Range	Formula
C4:C28	C4	=IF(OR(C3=210,A3=1),$D$4,SUM(C2+C3))

 

[davidalexander1959]

Sorry. Big mistake. A13 is suppose to be 0. Disregard the last sheet. See new one. If you change the orange cells C13, C17, C21 to 10 the sequence works.

Roulette 123.xlsx
	A	B	C
4	0	10	10
5	0	10	10
6	0	20	20
7	0	30	30
8	0	50	50
9	0	80	80
10	0	130	130
11	0	210	210
12	0	10	10
13	0	10	220
14	0	20	230
15	1	30	450
16	0	10	10
17	0	10	460
18	0	20	470
19	1	30	930
20	0	10	10
21	0	10	940
22	0	20	950
23	0	30	1890
24	0	50	2840
25	0	80	4730
26	0	130	7570
27	0	210	12300
28	0	10	19870
Sheet2

Cell Formulas
Range	Formula
C4:C28	C4	=IF(OR(C3=210,A3=1),10,SUM(C2+C3))

 

[davidalexander1959]

I have finally figured it out. This is the formula I came up with.

=IF(OR(C3=210,A3=1),10,IF(AND(C3=10,C2<>10),10,SUM(C2+C3)))

Your formula footoo =IF(B2=210,10,B2+((B1<>210)*B1)) helped me on the right track so thank you for that.

This is resolved.