# 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!

#### Attachments

• 8.7 KB Views: 42
Status
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:

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

#### Attachments

• 15.6 KB Views: 36
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

#### Attachments

• 8.8 KB Views: 39
See less See more
I'm not that good on formulas :up: nice one
I'm not that good with VBA
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
Status
Not open for further replies.