Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
nesting functions to compare segments of two columns
In Excel I would like to nest functions to make the formula
=MAX(OFFSET((ADDRESS((MATCH(I1,F1:F8000)),6)):(ADD RESS((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 dont 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, Id 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting Functions? | Excel Worksheet Functions | |||
Nesting Functions | Excel Worksheet Functions | |||
using functions to compare multiple columns for mismatch of cells | Excel Worksheet Functions | |||
Nesting functions in the functions dialog box | Excel Worksheet Functions | |||
Nesting Functions | Excel Worksheet Functions |