Solved: Variable MIN range

901 Views 5 Replies 3 Participants Last post by andrew1968, Jul 9, 2014

andrew1968

Discussion Starter · Jul 7, 2014

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!

Save

Status: Not open for further replies.

1 - 6 of 6 Posts

Keebellah

· #2 · Jul 8, 2014

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:

Code:

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
Else
    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

Save

CodeLexicon

· #3 · Jul 8, 2014

An approach using formulas is attached also:

=IF(B1=1,MIN(INDIRECT(CELL("address",A1)&":"&CELL("address",OFFSET(A1,MATCH(0,B2:B1000,0)-1,0)))),"")

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