Tech Support Guy banner

Solved: Variable MIN range

901 Views 5 Replies 3 Participants Last post by  andrew1968
I've attached a file and I'm wondering if there is a formula that will accomplish what I'm looking for.

See sample file

In column A there is data
column B has mostly 0's but sometimes there will be rows of sequential numbers
column C is what I would like to calculate.

- Whenever column B=1 I would like to get the MIN(range) of column A from the row where B=1 to the row that B<1 (not including the row where B goes back to 0). Sometimes this is just one row sometimes it is several rows.

Is this possible?

Thanks so much. My last time on this forum was a great success. Hoping for two in a row!


Not open for further replies.
1 - 6 of 6 Posts
Hi, I don't know if there is a formla way to solve this, but I mostly use VBA

I added the following code the sheet's vba project:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
Dim startRow    As Long
Dim endRow      As Long
Dim minVal      As Double
Dim x           As Long
endRow = Cells(Rows.Count, 1).End(xlUp).Row
minVal = WorksheetFunction.Max(Range(Cells(1, 1), Cells(endRow, 1)))
If Target.Row = 1 Then
    startRow = Target.Row
    For x = Target.Row To 1 Step -1
        If Cells(x, 2).Value = 0 Then
            startRow = x + 1
            Exit For
        End If
    Next x
    If startRow < 1 Then startRow = 1
End If
endRow = 0
For x = startRow To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(x, 2).Value = 0 Then
        endRow = x - 1
        Exit For
    End If
Next x
If endRow = 0 Then endRow = Cells(Rows.Count, 1).End(xlUp).Row
For x = startRow To endRow
    minVal = WorksheetFunction.Min(minVal, Cells(x, 1).Value)
Next x
Cells(startRow, Target.Column).Offset(0, 3).Value = minVal
End Sub
It's also in the attached file,

Change the number is column B and the min will be calculated for that section.

See if it makes sense, I'll explain more if necessary


See less See more
An approach using formulas is attached also:


The formula is basically saying if the value in column B = 1, find the minimum of the range that runs from that row in column A to the row before the first zero in Column B, looking down the column.

The only thing to watch is that the formula looks at a range of nearly 1000 cells, which is probably excessive. You could change the B1000 reference in the above to a shorter range if you think your min range is only likely to be in single or double digits.

Hope this helps



See less See more
I'm not that good on formulas :up: nice one
I'm not that good with VBA ;) :D
I used the formula which is perfect. But thank you for the VBA... I'm going to look at it and see what I can learn from it.

This forum is fantastic!
1 - 6 of 6 Posts
Not open for further replies.