Counting no. cells in range until first max./min. value is reached

Originally Posted by zeegerman

Hi Jose,

Many thanks, works perfectly!

Are you sure?

In the file you say:

(if B2 is the max. value in range, then answer should be 0)

(if B2 is the min. value in range, then answer should be 0)

Jose's formulas do not account for that.

Tony Valko is right.

IF B2 is the minimum or maximum then my formula gives you 1 instead 0.
My formulas could be
=MATCH(MAX($B$2:$B$17),$B$2:$B$17,0)-(B2=MAX($B$2:$B$17))
=MATCH(MIN($B$2:$B$17),$B$2:$B$17,0)-(B2=MIN($B$2:$B$17))

Try these...

Min:

=IF(B2=MIN(B2:B17),0,MATCH(MIN(B2:B17),B2:B17,0))

Max:

=IF(B2=MAX(B2:B17),0,MATCH(MAX(B2:B17),B2:B17,0))

In "F6"

Formula:

=MATCH(MAX(B2:B17),B2:B17,0)

In "F8"

Formula:

=MATCH(MIN(B2:B19),B2:B19,0)