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