Formula Help

[BobA]

I have the following array formula in cell U22.

=MAX(FREQUENCY(IF('4.6'!F5:'4.6'!F5005>0,ROW('4.6'!F5:'4.6'!F5005),""),
IF('4.6'!F5:'4.6'!F5005*'4.6'!F6:'4.6'!F5005<0,ROW('4.6'!F5:'4.6'!F5005),"")))

This formula counts the longest consecutive entry of positive numbers in sheet
4.6, column F. (Putting *-1 at the end counts for negative numbers.)

What I would like to do in cell U24 is have a formula that counts the most recent
number of consecutive positive numbers.

Example:

1
-4
-3
2
4
7
-1
7
8

The answer would be two. The last two entries 7 & 8 are positive, so we have
two in a row. In other words I'm counting my current positive streak-not my
best streak overall as my inserted formula calculates, which would be three.

Thanks

 

[Marcelo Branco]

Maybe...

	A​	B​	C​
1​	Numbers​		Result​
2​	1​		2​
3​	-4​
4​	-3​
5​	2​
6​	4​
7​	7​
8​	-1​
9​	7​
10​	8​
11​

Array formula in C2
=1/LOOKUP(2,1/FREQUENCY(IF(A2:A10>0,ROW(A2:A10)),IF(A2:A10<=0,ROW(A2:A10))))
Ctrl+Shift+Enter

M.

 

[BobA]

Maybe...

	A​	B​	C​
1​	Numbers​		Result​
2​	1​		2​
3​	-4​
4​	-3​
5​	2​
6​	4​
7​	7​
8​	-1​
9​	7​
10​	8​
11​

<tbody>
</tbody>

Array formula in C2
=1/LOOKUP(2,1/FREQUENCY(IF(A2:A10>0,ROW(A2:A10)),IF(A2:A10<=0,ROW(A2:A10))))
Ctrl+Shift+Enter

M.

Thank you very much. It works fine. However, I asked my question wrong. I'd like to know the current winning or losing streak. So, in my last six entries: if I have five positive entries and then the very last entry is negative then the answer would be minus 1. (-1)

Can we do this by altering the same formula, or will it require two different formulas?

Thanks, again.

 

[Marcelo Branco]

Thank you very much. It works fine. However, I asked my question wrong. I'd like to know the current winning or losing streak. So, in my last six entries: if I have five positive entries and then the very last entry is negative then the answer would be minus 1. (-1)

Can we do this by altering the same formula, or will it require two different formulas?

Thanks, again.

Maybe this ...

	A​	B​	C​
1​	Numbers​		CuurentStreak​
2​	1​		2​
3​	-4​
4​	-3​
5​	2​
6​	4​
7​	7​
8​	-1​
9​	7​
10​	8​

<tbody>
</tbody>

Formula in C2
=SUMPRODUCT(--(SIGN(A10:INDEX(A2:A10,LOOKUP(2,1/(SIGN(A2:A10)<>SIGN(A10)),ROW(A2:A10)-ROW(A2)+1)))=SIGN(A10)))

The formula returns 2

Then change A10 to -1 and the formula returns 1

Is this what you want?

M.

 

[BobA]

Maybe this ...

	A​	B​	C​
1​	Numbers​		CuurentStreak​
2​	1​		2​
3​	-4​
4​	-3​
5​	2​
6​	4​
7​	7​
8​	-1​
9​	7​
10​	8​

<tbody>
</tbody>

Formula in C2
=SUMPRODUCT(--(SIGN(A10:INDEX(A2:A10,LOOKUP(2,1/(SIGN(A2:A10)<>SIGN(A10)),ROW(A2:A10)-ROW(A2)+1)))=SIGN(A10)))

The formula returns 2

Then change A10 to -1 and the formula returns 1

Is this what you want?

M.

Hi, thanks. It's what I want, but I am continually adding data to the column and it doesn't work even if I change the A10 to A100 or A1000 or whatever.

Also, if the last three entries are negative numbers it should say -3. If the last three are positive it would say 3.

Sorry, I appreciate the help. You're doing a lot of work here. I feel like I'm putting you out.

 

[Marcelo Branco]

No problem.

See if this is Ok (i used two helper cells only to simplify the final formula)

	A​	B​	C​	D​	E​
1​	Numbers​		Position of LastNum​	Position Last Num <> Signal​	CurrentStreak​
2​	1​		9​	8​	1​
3​	-4​
4​	-3​
5​	2​
6​	4​
7​	7​
8​	-1​
9​	7​
10​	-1​
11​
12​

<tbody>
</tbody>

Formula in C2
=MATCH(9.99E+307,A2:A1000)

Formula in D2
=LOOKUP(2,1/(SIGN(A2:INDEX(A2:A1000,C2))<>SIGN(INDEX(A2:A1000,C2))),ROW(A2:INDEX(A2:A1000,C2))-ROW(A2)+1)

Formula in E2 (Result)
=SUMPRODUCT(--(SIGN(INDEX(A2:A1000,C2):INDEX(A2:A1000,D2))<>SIGN(INDEX(A2:A20,D2))))

Then put new numbers in A11, A12, A13... and check the results

M.

 

[Marcelo Branco]

oops...

Is possible to use a much simpler formula in E2
=C2-D2

That's it!

M.

 

[BobA]

oops...

Is possible to use a much simpler formula in E2
=C2-D2

That's it!

M.

"(C2-D2)", yes I noticed that.

Here is what I did.

First I changed the end of this formula:

=SUMPRODUCT(--(SIGN(INDEX(A2:A1000,C2):INDEX(A2:A1000,D2))<>SIGN(INDEX(A2:A20,D2))))

to A2:A1000.

Then, I created another helper cell
with this formula:

=LOOKUP(9.99E+307,A2:A1000)

And finally I used this formula to give me an answer that tells me whether the streak is negative or positive.

=IF(F2>0,(C2-D2),IF(F2<0,((C2-D2)*-1)))

I'm sure there is a much more efficient way of doing this, but it works fine for now.

Thanks again for your effort,

Bob

 

[Marcelo Branco]

I was over complicating things. The SUMPRODUCT formula is not necessary.
Try

	A​	B​	C​	D​	E​	F​
1​	Numbers​		Position of LastNum​	Position Last Num <> Signal​	CurrentStreak​	Positive or Negstive​
2​	1​		9​	8​	1​	Negative​
3​	-4​
4​	-3​
5​	2​
6​	4​
7​	7​
8​	-1​
9​	7​
10​	-1​

C2 and D2 --> see formulas post 6

E2
=C2-D2

F2
=IF(INDEX(A2:A1000,C2)>0,"Positive","Negative")

M.

 

[BobA]

I was over complicating things. The SUMPRODUCT formula is not necessary.
Try

	A​	B​	C​	D​	E​	F​
1​	Numbers​		Position of LastNum​	Position Last Num <> Signal​	CurrentStreak​	Positive or Negstive​
2​	1​		9​	8​	1​	Negative​
3​	-4​
4​	-3​
5​	2​
6​	4​
7​	7​
8​	-1​
9​	7​
10​	-1​

<tbody>
</tbody>

C2 and D2 --> see formulas post 6

E2
=C2-D2

F2
=IF(INDEX(A2:A1000,C2)>0,"Positive","Negative")

M.

Brilliant, thank you.