# Excel - Row reference for consecutive series calculation

Asked By Santu on 02-Aug-12 01:45 PM
```One of the previous boarders had a nice formula to calculate longest
consecutive sequence of numbers. I need an additional feature which
displays a particular Cell reference where the series began and where it
ended for the longest series calculated
The longest consecutive sequence of numbers is done by
{=MAX(FREQUENCY(IF(A1:A39=nmbr,ROW(1:39)),IF(A1:A39<>nmbr
,ROW(1:39))))}

Let me give an eg

A              B            C            D
1/1           PE           -10        10
1/2           CE            12         20
1/3           PE            -10        30
1/4           PE            -10        40
1/5           CE            -10        50
1/6           PE              10        20
1/7           CE              10        30
1/8           PE              10        50

1) Count the longest sequence of losses (-10) points
It should be 3
The formula {=MAX(FREQUENCY(IF(C2:C10=-10,ROW(2:10)),IF(C2:C10<>-10
,ROW(2:10))))} works
2)What I additionally require is, the dates from column A which
correspond to start of series of longest sequence and end date of the
sequence
In this case 1/3 - 1/5 from column A
3) Also how to get all the sequence of consecutive losses put out with
corresponding dates?

Thanks

--
Santu```