nesting functions to compare segments of two columns

  • Thread starter Thread starter PPease
  • Start date Start date
P

PPease

In Excel I would like to nest functions to make the formula

=MAX(OFFSET((ADDRESS((MATCH(I1,F1:F8000)),6)):(ADDRESS((MATCH(I2,F1:F8000)),6)),0,1))

The part =ADDRESS((MATCH(I1,F1:F8000)),6) works on its own as does
=MAX(OFFSET(F14:F23,0,1)) but they don’t work together.

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. For example, I’d like to be able
to pick any two numbers in the first column (i.e. 201.1 – 201.6) below and
then identify the maximum value in the second column (530.03); only I have
8000 rows instead of 10.

201 423.96
201.1 461.13
201.2 530.03
201.3 463.68
201.4 406.03
201.5 439.66
201.6 412.12
201.7 522.31
201.8 444.17
201.9 458.86
 
The intent is to be able to type in two numbers; a minimum value (I1) anda
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.
Click to expand...

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.
 
Back
Top