# Max and Min formula with loop

#### Usman

##### New Member
I have a large data around 666,000 rows. In this data i have to calculate the max and min of each cycle.

3 1 one cycle is 1-1 do that max value in that cycle will be 8 and min 3
8 0 and the max and min value in the 2nd cycle will be 8 and 5 respectively
6 0
8 1
9 0
5 1

I am looking for a formula that can calculate the max and min value during each cycle from a data where i have around 2000 cycles.

#### Sam Mathai Chacko

##### Active Member
Usman, apparently, your question has left everyone clueless. You might want to post a trimmed down version of your workbook with the input, and the expected output. That way, it might be easier for the volunteers here to try and provide a solution to your query.

#### NARAYANK991

##### Excel Ninja
Usman, apparently, your question has left everyone clueless. You might want to post a trimmed down version of your workbook with the input, and the expected output. That way, it might be easier for the volunteers here to try and provide a solution to your query.
Hi Sam ,

Let me make a guess , and set the ball rolling !

I am posting data which I assume is the way Usman has his data laid out :
Code:
``````Col_1  Col_2  Col_3  Col_4  Col_5
8      1      1      3      9
9      0      1      3      9
3      0      1      3      9
5      0      1      3      9
2      1      2      2      9
7      0      2      2      9
9      0      2      2      9
1      1      3      1      9
4      0      3      1      9
9      0      3      1      9
6      1      4      3      6
3      0      4      3      6
9      1      5      9      9
6      1      6      4      8
8      0      6      4      8
4      0      6      4      8
5      1      7      1      5
2      0      7      1      5
1      0      7      1      5
8      1      8      8      8``````
Here Col_1 and Col_2 are the input data columns ; Col_3 is a helper column I have used ; Col_4 gives the minimum and Col_5 gives the maximum for each cycle , where a cycle is defined as the period from one 1 to the next.

Of course my results may be wrong , but I think you can get an idea of what Usman is looking for.

Narayan

#### Sam Mathai Chacko

##### Active Member
That seems to make sense Narayan. Let me try that out.

#### Sam Mathai Chacko

##### Active Member
And speaking of which, Usman, any reason why a VBA solution will not suffice?

#### Sam Mathai Chacko

##### Active Member
OK, so from your original query, I am assuming that you mentioned that the max and min for the second cycle is 8 and 5, and NOT 9 and 5, because you are only considering the values against the starting 1 and ending 1 for each cycle. Keeping that assumption as true, and assuming your data starts from A2 to B21, use these formulas for your MIN and MAX for each cycle.

Code:
``````=MIN(IF(1=INDIRECT("B"&MAX((B\$2:B2)*ROW(B\$2:B2))&":B"&MIN(IF(B3:B\$21,ROW(B3:B\$21)))),INDIRECT("A"&MAX((B\$2:B2)*ROW(B\$2:B2))&":A"&MIN(IF(B3:B\$21,ROW(B3:B\$21)))),""))

=MAX(INDIRECT("A"&MAX((B\$2:B2)*ROW(B\$2:B2))&":A"&MIN(IF(B3:B\$21,ROW(B3:B\$21))))*(1=INDIRECT("B"&MAX((B\$2:B2)*ROW(B\$2:B2))&":B"&MIN(IF(B3:B\$21,ROW(B3:B\$21))))))``````
I do think that this can be shortened, but I didn't want to spend much neuron power on it, because I think it's better you validate the output first.

Both of the formulas are array formulas.

Narayan, thanks for the direction. Just to put my assumption as a visual, this is what I assume the output should be. Keeping your original table, but just adding my assumed columns at the end (Col_4a, Col_5a). Of course I could be wrong too :D

Code:
``````Col_1 Col_2 Col_3 Col_4 Col_5 Col_4a Col_5a
8 1 1 3 9 2 8
9 0 1 3 9 2 8
3 0 1 3 9 2 8
5 0 1 3 9 2 8
2 1 2 2 9 1 2
7 0 2 2 9 1 2
9 0 2 2 9 1 2
1 1 3 1 9 1 6
4 0 3 1 9 1 6
9 0 3 1 9 1 6
6 1 4 3 6 6 9
3 0 4 3 6 6 9
9 1 5 9 9 6 9
6 1 6 4 8 5 6
8 0 6 4 8 5 6
4 0 6 4 8 5 6
5 1 7 1 5 5 8
2 0 7 1 5 5 8
1 0 7 1 5 5 8
8 1 8 8 8 8 8``````

#### NARAYANK991

##### Excel Ninja
Hi Sam ,

If we go by your logic , what Usman wants is the minimum and maximum at each 1 ; if this is so , it means all the in-between values , which have 0 against them , are irrelevant ?

In this case , the easiest thing to do is an Autofilter , so that all the 1's come together ; copy this entire set of data to a new worksheet , and just compare one value with the next to get the minimum and maximum.

Narayan

#### Sam Mathai Chacko

##### Active Member
Narayan, I just deduced that logic from this statement

3 1 one cycle is 1-1 do that max value in that cycle will be 8 and min 3
8 0 and the max and min value in the 2nd cycle will be 8 and 5 respectively
6 0
8 1
9 0
5 1
so yes, I agree that filtering it out as you say should give the answer OP is looking for, but I thought the requirement was that of a formula #### NARAYANK991

##### Excel Ninja
Hi Sam ,

My fear is the 666,000 rows !

I doubt that your formula or any other formula which attempts to do what Usman wants without using helper columns will be resource and time intensive.

Either we look for simpler formulae , or we take the VBA route as you have already suggested.

Narayan

#### SirJB7

##### Excel Rōnin
Hi, Usman!

Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Max and Min formula with loop (for Usman at chandoo.org).xlsm

This is the code:
Code:
``````Option Explicit

Sub AThousandAndOneDevilList()
'
' constants
Const ksWS = "Hoja1"
Const ksData = "DataList"
Const ksCycle = "CycleList"
Const ksMin = "MinList"
Const ksMax = "MaxList"
Const kbStartOnFirst1 = True
Const kbEndOnLast1 = True
'
' declarations
Dim ws As Worksheet
Dim rngData As Range, vData As Variant
Dim rngCycle As Range, vCycle As Variant
Dim rngMin As Range, vMin As Variant
Dim rngMax As Range, vMax As Variant
Dim lStart As Long, lEnd As Long, lCount As Long
Dim iDataMin As Integer, iDataMax As Integer
Dim I As Long, J As Long, K As Long
'
' start
Debug.Print Now()
'  application
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
'  ranges
Set ws = Worksheets(ksWS)
With ws
Set rngData = .Range(ksData)
Set rngCycle = .Range(ksCycle)
Set rngMin = .Range(ksMin)
Set rngMax = .Range(ksMax)
End With
rngMin.ClearContents
rngMax.ClearContents
'  arrays
vData = rngData.Value
vCycle = rngCycle.Value
vMin = rngMin.Value
vMax = rngMax.Value
lCount = UBound(vData)
'
' process
I = 1
Do Until I >= lCount
' start of cycle
J = I
If (J = 1) And kbStartOnFirst1 Then
Do Until vCycle(J, 1) = 1 Or J = lCount
J = J + 1
Loop
End If
lStart = J + (J = lCount)
' cycle defaults
iDataMin = vData(J, 1)
iDataMax = vData(J, 1)
' cycle
J = lStart + 1
Do Until vCycle(J, 1) = 1 Or J = lCount
Select Case vData(J, 1)
Case Is < iDataMin
iDataMin = vData(J, 1)
Case Is > iDataMax
iDataMax = vData(J, 1)
End Select
J = J + 1
Loop
If (J <> lCount) Or Not (kbEndOnLast1) Then
lEnd = J - (J <> lCount)
' fill cycle
For K = lStart To lEnd
vMin(K, 1) = Val(iDataMin)
vMax(K, 1) = Val(iDataMax)
Next K
Else
lEnd = lCount
End If
' next cycle
I = lEnd
Loop
'
' end
'  arrays
rngMin.Value = vMin
rngMax.Value = vMax
'  ranges
Set rngMax = Nothing
Set rngMin = Nothing
Set rngCycle = Nothing
Set rngData = Nothing
Set ws = Nothing
'  application
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
'  beep
Beep
'
Debug.Print Now()
End Sub``````
Adjust constants kbStartOnFirst1 and kbEndOnLast1 to true to start cycle when first 1 appears and to end cycle when last 1 appears; leave them as false to include all records or mix them as required.

Execution time: 5 sec.

Regards!

• Usman

#### Usman

##### New Member
SIRJB7 your makro worked well.. thanks 