CODEWITHSUNDEEP
google-sheets
jms2k
jms2k

Reputation: 81

Sort or Filter Column, display in ranges

I have a log to track my running distance/times/etc. I am trying to come up with a way to display my top 10 or so times (pace/mile) at various distances (0-2.9, 3-4.9, 5-6.9, etc). I've tried to use "Sort" and I'm able to sort by distance, but I want to be able to only select the distances within those groups, then sort based on pace. Ideally, it would display Date / Distance / Pace.

I'm not quite sure what terms I'm supposed to be looking for, so it makes it hard to find.

Here's a link to my sheet: https://docs.google.com/spreadsheets/d/1d0aOsaarKaoSJNupzyk62oDh_o1yWosoi8TldCcKs0Y/edit?usp=sharing

Basically, in the "Tests" sheet, I want to display "Best Pace 3-5 Miles" and have it sort my top 10(or whatever) runs using the "Mileage" sheet column C for distance, Column F for pace, and Column B for date.

Thanks!

Upvotes: 0

Views: 53

Answers (1)

Tesseract
Tesseract

Reputation: 8139

Try this expression.

=sort(filter(Mileage!A10:E, (Mileage!C10:C >= 3) * (Mileage!C10:C <= 5) * (Mileage!E10:E <> 0)), 5, false)

Here is the documentation for filter https://support.google.com/docs/answer/3093197?hl=en

and for sort https://support.google.com/docs/answer/3093150?hl=en

(Mileage!C10:C >= 3) * (Mileage!C10:C <= 5) * (Mileage!E10:E <> 0) means the value in column C must be >= 3 and <= 5 and the value in E must be different from 0. sort(range, 5, false) means: sort the 5th column in the given range in descending order.

Upvotes: 1

Related Questions