Win lost draw

How can i calculate the most win draw sequence before a loss so example
win
win
draw
win
draw
loss
so this answer would be 5, so this be the best run before a loss

i have win loss draw in column H5:H200 im just trying to figure out the best run before the team lost

i only want it to be done using the H column this is the Column with the relevant info

hope ive explained this as best i can

Originally Posted by JohnTopley

I don't know if this can be done without a "helper" column.

As an example:

in I5 put

=IF(H5="loss",0,I4+1) (I4 must be 0/blank)

Copy down

In any cell =MAX(I5:I200) will give longest sequence

The helper column can be any of your choosing and hidden

Just in had to go out - John i could use Column H as helper column as it already has Win, Loss or Draw in it these run from h5 to h200

Originally Posted by mrice

Try this user defined function

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a suitable cell, enter = =NoLossRun(H5:H200)

Remember to save the workbook as a macro enabled workbook .xlsm

Done this but it gives me exactly what ive got already in H2:h200 it doesnt count just says win, loss draw.

but thank you

i need the lost entries as its important that these are in included. currently there is 41 losses

This is correst

Thank you mehmetcik but this only gives me win loss or draw, not giving me winning streak but thank you anyway

What exactly do i need to copy from? i copies this Function NoLossRun(MyRange As Range) and all the code down until an including End Function
trying again now

the ones in colour are my own the plain ones are from the suggested reply but thank you
seq.PNG

Originally Posted by Tony Valko

See if this does what you want...

Data Range

	H	I	J
5	Loss		Longest Streak
6	Win		6
7	Loss
8	Win
9	Win
10	Win
11	Win
12	Loss
13	Draw
14	Draw
15	Draw
16	Win
17	Draw
18	Win
19	Loss
20	Draw
21	------	------	------

This array formula** entered in J6:

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(H5:H20,{"Win","Draw"},0)),ROW(H5:H20)),IF(H5:H20="Loss",ROW(H5:H20))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thank you John this is working perfectly and thank you to all that have made other suggestions as well ye are brill

Thanks are due to Tony not myself.

Originally Posted by Tony Valko

See if this does what you want...

Data Range

	H	I	J
5	Loss		Longest Streak
6	Win		6
7	Loss
8	Win
9	Win
10	Win
11	Win
12	Loss
13	Draw
14	Draw
15	Draw
16	Win
17	Draw
18	Win
19	Loss
20	Draw
21	------	------	------

This array formula** entered in J6:

=MAX(FREQUENCY(IF(ISNUMBER(MATCH(H5:H20,{"Win","Draw"},0)),ROW(H5:H20)),IF(H5:H20="Loss",ROW(H5:H20))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

My mistake Thank you Tony and to all