ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Row reference for consecutive series calculation (https://www.excelbanter.com/excel-worksheet-functions/446747-row-reference-consecutive-series-calculation.html)

Santu

Row reference for consecutive series calculation
 
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:A3 9<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


All times are GMT +1. The time now is 08:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com