Maximum of coherent cells

Hi all,

I am running into the following issue. I have one column (52560 cells) which is containing numerical values. The goal is to find the maximum of coherent values (coherent are the values which are separated by zeros). Its always the last of the coherent numbers since the numbers are always starting from 1 counting up to a certain number. I need that max. reoccurring in the cell right next to it.

If>0 then max. of coherent array else 0 --> the whole things needs to be consecutive dragging down the formula.

I hope I explained it well enough. It would be great if somebody can help to find me a formula which identifies the maximum.


Regards,
Molo


given result
0 0
0 0
1 7
2 7
3 7
4 7
5 7
6 7
7 7
0 0
0 0
0 0
1 5
2 5
3 5
4 5
5 5
0 0
0 0

Try this in B2:

=IF(A2=0,0,IF(N(B1)>0,B1,MATCH(0,A2:$A$52561,0)-1))

Phuocam,

Thanks a lot for your support. The formula works just fine. To be honest, I thought that problem has to be solved with an array formulat.

Anyways, thanks again!


Molo