Tks all for the precious help...

with this one obtained the desired result



"T. Valko" wrote:

Assume your range of numbers are in B9:B30.

List the unique numbers starting in D9 on down.

Enter this array formula** in E9 and copy down as needed:

=MAX(FREQUENCY(IF(B$9:B$30=D9,ROW(B$9:B$30)),IF(B$ 9:B$30<D9,ROW(B$9:B$30))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Antonio" wrote in message
...
Hi

the formula presently used as follows:

=MAX(FREQUENCY(IF(B9:OFFSET(INDEX(B:B;MATCH(99^99; B:B));-1;0)=B10:OFFSET(INDEX(B:B;MATCH(99^99;B:B));0;0);R OW(B9:OFFSET(INDEX(B:B;MATCH(99^99;B:B));-1;0)));IF(B9:OFFSET(INDEX(B:B;MATCH(99^99;B:B));-1;0)<B10:OFFSET(INDEX(B:B;MATCH(99^99;B:B));0;0); ROW(B9:OFFSET(INDEX(B:B;MATCH(99^99;B:B));-1;0)))))+1

but still unable to get the desired result, that for the given set of
numbers should be:
1 = 0 (consecutive)
2 = 0 (consecutive)
3 = 2 (Consecutive)
4 = 5 (Consecutive)
5 = 0
6 = 0

Tks for the assistance n
bst rgds

"T. Valko" wrote:

That can be reduced to:

=MAX(FREQUENCY(IF(C1:C27=C2:C28,ROW(C1:C27)),IF(C1 :C27<C2:C28,ROW(C1:C27))))+1

Note that it will count empty cells as consecutives *and* empty cells and
numeric 0 will evaluate as the same.


--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
How about adapting the expression that Ron C. gave you in an earlier
posting?
To suit it here for your col C data in C1:C28, try array-entered:
=MAX(FREQUENCY(IF(C1:C27=(C2:C28),ROW(C1:C27)),IF( C1:C27<(C2:C28),ROW(C1:C27))))+(COUNT(1/(C1:C27=(C2:C28)))0)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---