"Choose" function for the Best Running Time

[Jay Walsh]

Hi,
I am storing all of m running times on an excel sheet. I have the distance run in column A (lets say A1:100) and the time in column B (B1:100).

In a separate table columns E and F have ranges of distances (0 - 10[in E1 and F1 resp.], 11 to 20 in row 2, 1 to 30 I row 3, etc).

What I want is I column G - for example G1 to pick out the best / lowest time I have done for a run between0 and 10 miles.So the formula will read column A, pick out the distances between 0 and 10, then choose the lowest time from column B.
I think it is a combination of CHOOSE and MIN but I cannot quite get it.

Any help greatly appreciated
Jay

 

[jasonb75]

It might be possible with choose, but I would look at aggregate.

For 0 to 10, try

=AGGREGATE(15,6,$B$1:$B$100/($A$1:$A$100> E1)/($A$1:$A$100< F1),1)

 

[william_man]

If you have Excel 365 / 2019:

=MINIFS($B$1:$B$100,$A$1:$A$100,">"&$E1,$A$1:$A$100,"<="&$F1)

For older versions:

=AGGREGATE(15,6,$B$1:$B$100/(($A$1:$A$100>$E1)*($A$1:$A$100<=$F1)),1)

I would also suggest updating your distance ranges to something like 1-10, 10-20,20-30 etc, otherwise you might be excluding the runs that fall in between 10 and 11 for instance.

 

[Jay Walsh]

Excellent!!
jasonb, this works perfect. AGGREGATE is not one of my go-to formulas b will now. Works very well!! Thanks a lot, very much appreciated.

wlliam_man, agreed, I have made those changes. Thanks for that.

Thanks both, very helpful
Cheer