find the lowest within a table/range of values

[benjaminkcjones]

I have been struggling to work this out so thank you in advance for any help, it is much appreciated.

I have a table with 3 columns and about 3800 rows.

Column A is the category
Column B is the Sub category
Column C is the value.

There are circa 100 Categories and 6 sub categories per category. Each sub category has multiple rows as they have multiple values.

I need to find which sub category has the lowest value for each category and which sub category has the lowest average value across the category.

For clarification, I have created an example below. I want to find who has the fastest (lowest) time and lowest average time in the U11 and U12 100m. For example, David has the fastest time in the U11 100m with 10.8 and he also has the fastest average time of 10.9

Race Name Time
U11 100m John 11.02
U11 100m John 11.1
U11 100m John 11.2
U11 100m David 10.8
U11 100m David 10.9
U11 100m David 11
U11 100m Harry 13.1
U11 100m Harry 13.1
U11 100m Harry 12.9
U11 100m Michael 12.4
U11 100m Michael 12.4
U11 100m Michael 12.5
U12 100m James 10.8
U12 100m James 10.9
U12 100m James 11
U12 100m John 13.1
U12 100m John 12.5
U12 100m John 13.1
U12 100m Chris 12.9
U12 100m Chris 12.4
U12 100m Chris 12.4
U12 100m Ben 12.5
U12 100m Ben 11.02
U12 100m Ben 11.1

[queuesef]

You could try putting the data into a Table (I highlight the Range, then hit Control T to make the data a Table). Once you have the info in a table, you can use the Data Filter (under Data tab) to display the filters (they look like downward pointing triangles). Pick the filter for the column you want and go to "Number Filters" ~ then off to the right side there will be "Top 10" Select that. In the Show box - change TOP to Bottom (for lowest) and you can display as many items (default is 10 for the Top 10). It's quicker once you do it a couple of times. Good luck

[sandy666]

you can try Pivot Table or Advanced Filter + AVERAGEIFS()