nesting functions to compare segments of two columns
The intent is to be able to type in two numbers; a minimum value (I1) and a
maximum value (I2). *The location of those values would then be identified in
a column of ascending numbers (F) but the numbers would make a range
separated by a variable number of other cells dependant on the min & max
inputs. *Then the maximum value of the corresponding range of numbers in
column G (not sorted) would be identified.
Maybe this would help:
=MAX(OFFSET(G1,
MATCH(I1,F1:F8000)-1,
0,
MATCH(I2,F1:F8000)-MATCH(I1,F1:F8000)+1,
1))
The idea is to calculate the "height" parameter of OFFSET vector using
the difference between the two MATCHes.
|