"Choose" function for the Best Running Time

Jay Walsh

Board Regular
Joined
Jan 19, 2004
Messages
117
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,588
Office Version
2019
Platform
Windows
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

New Member
Joined
Oct 31, 2019
Messages
19
If you have Excel 365 / 2019:
Code:
=MINIFS($B$1:$B$100,$A$1:$A$100,">"&$E1,$A$1:$A$100,"<="&$F1)
For older versions:
Code:
=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

Board Regular
Joined
Jan 19, 2004
Messages
117
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,465
Messages
5,468,796
Members
406,608
Latest member
bambers3185

This Week's Hot Topics

Top