MAX Consecutive across columns

Marvin.
Thanks! I take it these results are done with a macro? I do not see the macro there but the results are correct. Can you elaborate a bit more on how this works? Are there other options for coming up with the same information using an array?
Thanks.
William

William, please don't quote whole posts -- it's just clutter.

Press the REPLY button, not the QUOTE button.

Thanks.

Originally Posted by williampdx

Are there other options for coming up with the same information using an array?

Realistically given your data you would need to create a contiguous listing and work from that.

Using your sample file... insert a new sheet, name it "Data" and add following headers:

A1: Date
B1: +/-
C1: Sign

Then

A2:
=IF(MOD(ROWS(A$2:A2)-1,7),A1,INDEX('COR Table'!$B:$B,3+CEILING(ROWS(A$2:A2)/7,1)+CEILING(ROWS(A$2:A2)/35,1)-1))

B2:
=INDEX('COR Table'!$A:$BF,3+CEILING(ROWS(A$2:A2)/7,1)+CEILING(ROWS(A$2:A2)/35,1)-1,9+8*MOD(ROWS(A$2:A2)-1,7))

C2:
=IF(B2=0,N(C1),SIGN(B2))

copy A2:C2 down to row 1191 (to cover your 1190 data points)

With the above in place your "streak" calculations can be based off the above data set with relative ease:

Wins:
=MAX(FREQUENCY(IF(Data!$C$2:$C$1191=1,ROW(Data!$C$2:$C$1191)),IF(Data!$C$2:$C$1191<1,ROW(Data!$C$2:$C$1191))))
confirmed with CTRL + SHIFT + ENTER

Losses (as above but using -1 and > test):
=MAX(FREQUENCY(IF(Data!$C$2:$C$1191=-1,ROW(Data!$C$2:$C$1191)),IF(Data!$C$2:$C$1191>-1,ROW(Data!$C$2:$C$1191))))
confirmed with CTRL + SHIFT + ENTER

Based on the sample data I believe the answer for both calculations is 24, correct ?

As a general rule we try to offer the most efficient approach open to you - if a simple formula were available it would most likely have been provided.
(note: most likely - I am not asserting that it is not beyond the realms of possibility - perhaps a convoluted frequency array)

Working with non-contiguous data sets is generally inefficient - doing so where there is an irregular pattern within the non-contiguous set is (understandably) even less efficient.

Work with contiguous data sets - if you're not willing/prepared/able to do that then use a UDF (I've not looked at that offered to you already so am not passing comment on the specifics).

As for the expected results being 15 (wins) and 12 (losses) -I confess I'm slightly confused - either the 0's interspersed amongst the winning streak add to the appropriate streak or they do not ?

If the zeroes neither reset the streak nor add to its longevity then I make the longest winning streak 11 - such a streak occurs on 2 occasions:

2nd July 2010 GBP/JPY to 7th July 2010 GBP/JPY inclusive.
20th August 2010 GBP/JPY to 24th August 2010 EUR/USD inclusive

Else the streak is seemingly greater than 11 (ie the 0s interspersed amongst the positive values increment the streak)

If you stand by the 15 perhaps you could specify the data points involved ?