count max loss within visible cells

[timpson]

Hello
i have list of results (e.g. 123,12,-23,-24,1....) lets say B1:B12
i need to count max consecutive loss number but only for visible cells(if i hide some cell with table filtration it wouldnt be counted)
so far i found =MAX(FREQUENCY(IF(B1:B12<0;ROW(B1:B12));IF(B1:B12>=0;ROW(B1:B12))))
but this one count value even if cell is hidden

 

[Domenic]

Try the following formula...

=MAX(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B1:B12,ROW(B1:B12)-ROW(B1),0,1))>0,IF(B1:B12<0,ROW(B1:B12))),IF(SUBTOTAL(3,OFFSET(B1:B12,ROW(B1:B12)-ROW(B1),0,1))>0,IF(B1:B12>=0,ROW(B1:B12)))))

To also exclude from the result rows that are manually hidden, replace SUBTOTAL(3,... with SUBTOTAL(103,...

Hope this helps!

 

[timpson]

Try the following formula...

=MAX(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B1:B12,ROW(B1:B12)-ROW(B1),0,1))>0,IF(B1:B12<0,ROW(B1:B12))),IF(SUBTOTAL(3,OFFSET(B1:B12,ROW(B1:B12)-ROW(B1),0,1))>0,IF(B1:B12>=0,ROW(B1:B12)))))

To also exclude from the result rows that are manually hidden, replace SUBTOTAL(3,... with SUBTOTAL(103,...

Hope this helps!

Thank you! this is exactly what i need)

 

[timpson]

Try the following formula...

=MAX(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B1:B12,ROW(B1:B12)-ROW(B1),0,1))>0,IF(B1:B12<0,ROW(B1:B12))),IF(SUBTOTAL(3,OFFSET(B1:B12,ROW(B1:B12)-ROW(B1),0,1))>0,IF(B1:B12>=0,ROW(B1:B12)))))

To also exclude from the result rows that are manually hidden, replace SUBTOTAL(3,... with SUBTOTAL(103,...

Hope this helps!

how to make it reversal to count wins?

 

[timpson]

i am sorry, also how to make it to ignore #VALUE! cells

 

[Domenic]

In that case, try...

=MAX(FREQUENCY(IF(ISNUMBER(B1:B12),IF(SUBTOTAL(103,OFFSET(B1:B12,ROW(B1:B12)-ROW(B1),0,1))>0,IF(B1:B12<0,ROW(B1:B12)))),IF(ISNUMBER(B1:B12),IF(SUBTOTAL(103,OFFSET(B1:B12,ROW(B1:B12)-ROW(B1),0,1))>0,IF(B1:B12>=0,ROW(B1:B12))))))

Hope this helps!

 

[timpson]

In that case, try...

=MAX(FREQUENCY(IF(ISNUMBER(B1:B12),IF(SUBTOTAL(103,OFFSET(B1:B12,ROW(B1:B12)-ROW(B1),0,1))>0,IF(B1:B12<0,ROW(B1:B12)))),IF(ISNUMBER(B1:B12),IF(SUBTOTAL(103,OFFSET(B1:B12,ROW(B1:B12)-ROW(B1),0,1))>0,IF(B1:B12>=0,ROW(B1:B12))))))

Hope this helps!

awesome! Thank you very much)

 

[Domenic]

You're very welcome, glad I could help.

Cheers!