Highest consecutive values in column

[psctornado]

Hi All,

I am looking to create a formula where it will return the 30 consecutive values in column B.

As an example :





Column A
Column B Values


A2

2


A3

4


A4

5


A5

5


A6

6


A7

6


A8

6


A9

7


A10

758


A11

8


A12

9


A13

15


A14

55


A15

35


A16

98


A17

44


A18

15


A19

11


A20

78


A21

432


A22

290


A23

8


A24

9


A25

15


A26

55


A27

59


A28

73


A29

88


A30

103


A31

117


A32

132


A33

147


A34

161


A35

176


A36

191


A37

206


A38

220


A39

235


A40

250


A41

264


A42

279


A43

294


A44

308


A45

323


A46

338


A47

353


A48

367


A49

382


A50

397


A51

411


A52

426


A53

441


A54

456


A55

470


A56

485


A57

500


A58

514


A59

529


A60

544


A61

558


A62

573


A63

588


A64

603


A65

617


A66

632


A67

647


A68

661


A69

676


A70

691


A71

705


A72

720


A73

735


A74

750


A75

764


A76

779


A77

794


A78

808


A79

823


A80

0


A81

0


A82

1


A83

2


A84

3


A85

4


A86

5


A87

941


A88

955


A89

970


A90

985


A91

999


A92

1014


A93

1029


A94

1044


A95

1058


A96

1073


A97

1088


A98

1102


A99

1117


A100

1132


A101

50








I would expect from the above, that values A65 through A100 are the largest consecutive string & I would like to return each value from the string in a separate column, say column C. Is there a formula in which I can do that?

Any help would be appreciated!

[Aussiebear]

Not sure I follow your logic here as to "consecutive" string given that if cell A65 starts at 617 and then the values increase either by 14 or 15 until cell A79 which then drops to 0. Cell A80 to A86 either may or may not increase by 1, the Cell A87 restarts the potential increase of either 14 or 15 until cell A100.

[Paul_Hossler]

A65

617


A66

632


A67

647


A68

661


A69

676


A70

691


A71

705


A72

720


A73

735


A74

750


A75

764


A76

779


A77

794


A78

808


A79

823


A80

0


A81

0


A82

1


A83

2


A84

3


A85

4


A86

5


A87

941


A88

955


A89

970


A90

985


A91

999


A92

1014


A93

1029


A94

1044


A95

1058


A96

1073


A97

1088


A98

1102


A99

1117


A100

1132















I would expect from the above, that values A65 through A100 are the largest consecutive string & I would like to return each value from the string in a separate column, say column C. Is there a formula in which I can do that?


How exactly are you defining 'consecutive' since I can't see anything consecutive there

[psctornado]

Sorry I should clarify then. When I say consecutive, it would be the highest consecutive sum of 30 values (ie rows 65 through 100, their total for those values are the highest, vs other consecutive groups of 30 numbers).

[Aussiebear]

Surely having 7 lowest values within that range you nominated simply debunks your suggestion of "Highest consecutive sum of values"

[psctornado]

Surely having 7 lowest values within that range you nominated simply debunks your suggestion of "Highest consecutive sum of values"

I'm not sure how it 'debunks' my statement. If we sum up the consecutive values in that string, it sums to the highest value out of the 100 available. if I summed from rows 64 through 99, the sum total would be less, etc. I'm not sure how pointing out that the 7 lowest values in that string gets any closer to solving the question I had asked originally. I can resolve this thread if there isn't a solution to my question. I understand there may be not be a solution to every question to excel.

[Aussiebear]

I see now where I was wrong. From Post#2 I was looking for a pattern of values that changed either by "14 or 15", hence the 7 lowest values had thrown me off what you were chasing.

[Paul_Hossler]

If I'm understanding

This is only the first part. The 30 values would need to be pulled out separately is my logic is right

29266

[psctornado]

This looks great! Is there were to extract the values from rows 71 to 100 then?


If I'm understanding

This is only the first part. The 30 values would need to be pulled out separately is my logic is right

29266

[Paul_Hossler]

29268

This is with worksheet formulas. I could be done with a macro or a user defined function

[psctornado]

Excellent. Thanks for the help!

[Paul_Hossler]

Here's another way that uses a VBA User Defined Function (UDF) to find the group of N (a user input) entries and return a dynamic array to the worksheet for the user input number of entries to test.

You could do a classical ctrl-shift-enter array entry also, but I think this is easier since you do not need to re-select the output range each time you change the number of values to check

The green F2 is the start of the dynamic array and the orange F1 is the Sum of the dynamic array's spill range



29271





Option Explicit


Dim aryNumbers As Variant


Function LargestConseq(ValuesIn As Range, Optional HowMany As Long = 30) As Variant
Const MinHowMany As Long = 10

Dim rNumbers As Range
Dim idxEntry As Long, idxHigh As Long
Dim totHigh As Double, totCurrent As Double
Dim aryOut() As Double

On Error GoTo ErrHandler

'error conditions
If HowMany < MinHowMany Then Err.Raise 1000, "LargestConseq", "HowMany too small (" & HowMany & "). Must be at least " & MinHowMany
If ValuesIn.Columns.Count <> 1 Then Err.Raise 1002, "LargestConseq", "Input Range must be one column"




'determine 'real' data range, if entire col entered or first entry is non-numeric
If ValuesIn.Rows.Count = ValuesIn.Parent.Rows.Count Then
Set rNumbers = Range(ValuesIn.Cells(1, 1), ValuesIn.Cells(ValuesIn.Parent.Rows.Count, 1).End(xlUp))
End If

If Not IsNumeric(rNumbers.Cells(1, 1).Value) Then
Set rNumbers = rNumbers.Cells(2, 1).Resize(rNumbers.Rows.Count - 1, 1)
End If

If rNumbers.Rows.Count < HowMany Then Err.Raise 1001, "LargestConseq", "Not enough cells in Input Range (" & rNumbers.Rows.Count & ") for requested number (" & HowMany & ")"

'bring data into array
aryNumbers = Application.WorksheetFunction.Transpose(rNumbers)



'prepare output array
ReDim aryOut(1 To HowMany)


'loop data, sum HowMany enteries, keep track of largest total and it's index
For idxEntry = HowMany To UBound(aryNumbers)
totCurrent = pvtSum(idxEntry, HowMany)
If totCurrent > totHigh Then
totHigh = totCurrent
idxHigh = idxEntry
End If
Next idxEntry


'move largest block of HowMany to output array
For idxEntry = LBound(aryOut) To UBound(aryOut)
aryOut(idxEntry) = aryNumbers(idxHigh - HowMany + idxEntry)
Next idxEntry


'return output array and exit
LargestConseq = Application.WorksheetFunction.Transpose(aryOut)

Exit Function


ErrHandler:
With Err
LargestConseq = .Description
End With


End Function


Private Function pvtSum(idxStart As Long, cntHowMany As Long) As Double
Dim X As Double
Dim i As Long

For i = cntHowMany - 1 To 0 Step -1
X = X + aryNumbers(idxStart - i)
Next i

pvtSum = X
End Function

[psctornado]

Wow this is even better! I greatly appreciate your help! Thanks again!!!

[Paul_Hossler]

I'm glad

My very personal preference is to use UDFs for such complicated tasks instead of helper columns and filling down formulas, etc.

Play with it and let me know if you run into any problems