Formula help: Identify specific sequences of numbers in a column

[JackDanIce]

Hi,

I have a column of numbers with integer values: -1, 0 and 1, which represents if a game is lost, drawn or won.

If there is a sequence of non-positive values that sum to -3, I want the formula to return 0.
After this, the formula should return 1 if and only if there is a sequence of non-negative values that sum to 2

This is an example of the match results and formula output next to it:

Match Result	Formula result
-1	1
0	1
0	1
-1	1
-1	0
-1	0
0	0
1	0
-1	0
1	0
0	0
1	1

Can anyone suggest a suitable formula please?

TIA,
Jack

 

[johnnyL]

How about:

=IF(SUM($A$2:A2) <-2, 0, 1)

 

[JackDanIce]

Wood from the trees, was overthinking it, that's a great suggestion @johnnyL thank you
I've updated it to:

=--(SUM($A$2:$A2)>-3)

And confirmed the result matches yours/is as needed.

However, what if the numbers were -5 and 2 respectively? With your suggestion or my modified one, it returns 1 as soon as the sum of values is >-5 which isn't correct.

In effect:
"Keep watching the game until there are X non-wins in a row. Then stop and do not restart watching until there are Y non-loses in a row"

 

[johnnyL]

Not sure I understand what you mean. Can you post another sample data that also includes the expected results?

 

[JackDanIce]

Sure, maybe this description might help with the updated example. Imagine a team vs team sport (football/soccer, basketball, cricket, etc).

I will keep betting on my team to win, until there has been a combination of draws and losses (only) which equals -5 (win = 1, draw = 0, loss = -1)
When this happens, I will not bet on my team to win, until there has been a combination of draws and wins (only) which equals 2

I've highlighted in yellow where the "change" in decision to bet occurs:

Bet on my team
Yes
Yes
Yes
Yes
Yes
Yes
No
No
No
Yes
Yes
Yes

It has to be a strict sequence of 'only' loses (or draws) that = -5 and then only after a sequence of wins (or draws) that = 2

 

[johnnyL]

You didn't show the actual win/draw/loss values.

 

[JackDanIce]

Forgot to highlight the first column before pasting in, thanks for nudge:

Match Result	Bet on my team
-1	Yes
-1	Yes
0	Yes
-1	Yes
-1	Yes
-1	Yes
0	No
1	No
1	No
1	Yes
0	Yes
1	Yes

 

[johnnyL]

What would the expected results be for the following:

Book5
	A
1	Match Result
2	-1
3	-1
4	0
5	1
6	0
7	-1
8	-1
9	-1
10	-1
11	1
12	0
13	1
14	0
Sheet1

And what would be the expected results for the following:

Book5
	A
1	Match Result
2	1
3	1
4	1
5	1
6	1
7	1
8	1
9	-1
10	-1
11	-1
12	-1
13	-1
14	1
15	0
16	1
17	0
Sheet1

 

[JackDanIce]

If using values of -5 and 2, then expected results would be "Yes" for all rows because there isn't a sequence of only -1 and 0 for match results that sum to -5, to create "No" in the next row and remain "No" until there's been only a sequence of 0 and 1s that add to 2

The highlighted row would in effect "re-start" the count back to 0 until the accumulation/sum = -5 without any wins/1's

Match Result	Bet on my team
-1	Yes
-1	Yes
0	Yes
1	Yes
0	Yes
-1	Yes
-1	Yes
-1	Yes
-1	Yes
1	Yes
0	Yes
1	Yes
0	Yes

And you always bet on the first game, because there's no prior result to consider. You then keep betting on win's until there is a sequence of only -1 and 0 values that sum to -5.

If there is a win at any point, before the sequence of results = -5, you effectively restat testing for loses or draws only

NB - this isn't actually betting on sports to feed a gambling habit and using Excel to find an edge
I'm backtesting a trading strategy up on a metal security where there are daily signals to buy or sell the product, but because the entire date range includes weekends when the market isn't open, there is no buy or sell signal (i.e. do nothing) so I have to factor 0 into this, otherwise it would be much simpler to say:
As soon as there are 5x-1's in a row, then stop and only restart when there are 2x1's in a row

If there was a filter on the table to remove weekends, then the remaining values would only be -1 or 1, thus making the pattern identification of keep buying or selling once a day as the signal indicates until there is explicitly 5 (or more) losing trades in a row. Then stop trading until there has been 2 winning trades in a row

This then changes to X losing trades in a row, then stop until Y wins in a row - as signal generation occurs regardless of trade result, if non trading days (aka result = 0) are removed

 

[johnnyL]

Formulas are not my strong point, if no one else suggests a formula, would a custom macro function be acceptable?