Find distance between Max to Min values in array?

Maybe:

=MATCH(MAX($A$1:$A$5),$A$1:$A$5,0)-MATCH(MIN($A$1:$A$5),$A$1:$A$5,0)-1

Probably need an ABS() function as part of that, because it seems that you won't know in advance whether MAX() or MIN() will be earlier/later in the array. ABS(position of max-position of min)-1

Edit to add -- unless you want the sign of the distance to indicate direction? but your example does not show an interest in direction, so I would guess not.

Thank you, Sir!!!


=ABS(MATCH(MAX($A$1:$A$5),$A$1:$A$5,0)-MATCH(MIN($A$1:$A$5),$A$1:$A$5,0))-1

Why not just use a named range?

Named ranges can be dynamic (usually using the OFFSET() function rather than INDEX()): https://www.excel-easy.com/examples/...med-range.html

Replace the "$A$1:$A$5" references with your dynamic named range (or an OFFSET() function, if you don't want to define a named range).
1) define named range "mynamedrange" as =OFFSET(something). You have not described the exact logic you are using to dynamically define the range, so I cannot make a specific suggestion. If you are unfamiliar with the OFFSET() function: https://support.office.com/en-us/art...e-b4d906d11b66

2) Replace the $A$1:$A$5 references in the above function with "mynamedrange". Using the MAX() function part as an example =...MAX(mynamedrange)...

3) Or, if you don't want to define a named range, simply replace the $A$1:$A$5 references with your OFFSET() function. Again, using the MAX() part of the function as the example. =...MAX(OFFSET(logic_needed_to_define_range))...

Hi Shorty,
being at it for hours now...I was unable to recreate the formula with your offset solution!
to explain it better I create a example sheet for you please check it out. thx

This named range (List):
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(1E+100,Sheet1!$A:$A))

with the modified formula:
=ABS(MATCH(MAX(List),List,0)-MATCH(MIN(List),List,0))-1

will extend as far as is needed to cope with the length of your data column...

Try this
=ABS(MATCH(MAX(OFFSET($A$1,0,0,$B$1)),OFFSET($A$1,0,0,$B$1),0)-MATCH(MIN(OFFSET($A$1,0,0,$B$1)),OFFSET($A$1,0,0,$B$1),0))-1

th kvsrinivasamurthy , that works beautifully stars for u)

Thanks for the compliment.