rhhince;466498 said:
I have numbers in column E8:F65500 which I named RANGE1, N8:O65500
which I named RANGE2.
Column A is numbered 1 to 65500 referencing RANGE1, Column J is
numbered as a continuation of column A 65501 to 131000 referencing
RANGE2.
Let's say I am looking for the minimum in RANGE1 and RANGE2, between
number 62500 and 75000 identified in Column A and Column J. How can I
put that in a formula?
I know this is in the worksheet function section of codecage
nonetheless you could make your own user defined function and use i
thus on a worksheet:
=mymin((RANGE1,RANGE2),62500,75000)
however the code behind it is:Function MyMin(TheRange
myBottom, myTop)
MyMin = 1E+20
For Each rw In TheRange.Rows
numb = rw.Offset(, -4).Resize(, 1).Value
If numb <= myTop And numb >= myBottom Then
For Each Cll In rw.Cells
If MyMin > Cll.Value Then MyMin = Cll.Value
Next Cll
End If
Next rw
End Function
It's a bit more flexible:
- the ranges can be any number of columns wide, and multiple range
don't have to be the same width
- there can be any number of ranges
- doesn't require named ranges
but:
- the column of numbers must be 4 columns to the left of each range
- if there's more than one range then those ranges must be enclose
in their own parentheses (see examples)
- so far I've not provisioned for non-numeric values in the ranges
- if it doesn't find a minimum it returns 1E+20
- it's not very efficient
examples:
=mymin(E8:F45,6,30)
=mymin(*(*E8:F65500,N8:O65500*)*,62500,75000)
=mymin(*(*L26:S35,L41:Q56,L59:X62*)*,10,30)
If you wanted, we could add a parameter to allow the use of an offse
other than -4
