rows between maximum and minimum

[Mika]

Hi,

Is there a formula to find/count the number of rows (all filled with
numbers) between the maximum and the minimum of a range.Something
like:

="CountRows"(min("a1:a200"),max("a1:a200"))

This time I don´t want to use VBA.
Rgds
Mika

 

[Harlan Grove]

Is there a formula to find/count the number of rows (all filled with
numbers) between the maximum and the minimum of a range.Something
like:

="CountRows"(min("a1:a200"),max("a1:a200"))

This time I don´t want to use VBA.

Number of rows between topmost max and topmost min is given by

=ABS(MATCH(MAX(Rng),Rng,0)-MATCH(MIN(Rng),Rng,0))

Largest and smallest gaps between possibly multiple instances of min and max
can be calculated with more complicated *array* formulas.

Largest gap:
=MAX(ABS(SMALL(IF(Rng=MAX(Rng),Rng),ROW(INDIRECT("R"&COUNTIF(Rng,
MAX(Rng))&"C",0)))-SMALL(IF(Rng=MIN(Rng),Rng),COLUMN(INDIRECT("RC"
&COUNTIF(Rng,MIN(Rng)),0)))))

 

[Mika]

Thank you a lot Harlan !

Mika