I have a formula which calculates longest streak of the number 1 in column AC, but I have just realised that the sheet will quite often be autofiltered
This is the formula
It is an array formula, but it does not handle the results correctly when the sheet is filtered
Any idea of the change/s required to have it work only on visible cells?
cheers
This is the formula
{=MAX(FREQUENCY(IF(AC:AC=1,ROW(AC:AC)),IF(AC:AC<>1,ROW(AC:AC))))}
It is an array formula, but it does not handle the results correctly when the sheet is filtered
Any idea of the change/s required to have it work only on visible cells?
cheers