Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consecutive date calculation involving holidays | Excel Worksheet Functions | |||
highlight consecutive series of four numbers vba | Excel Programming | |||
Calculating largest gap in a series of consecutive numbers | Excel Worksheet Functions | |||
How do I reference a value to consecutive merged cells | Excel Worksheet Functions | |||
reference to consecutive worksheets within a workbook | New Users to Excel |