• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.
 
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.
 
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
 
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
 
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
 
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 :)
 
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
 
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.

Just advise if any issue.

Regards!
 
Hi, Usman!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
Back
Top