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