I am running Numbers 15.3.1 on Tahoe. I have a master sheet with data entries reflecting the statistical performance of a large group of baseball players. Some of these values are absolute, such as at-bats. Others are the results of formulas, such as batting averages. I want to create a table on a second sheet that reflects the top 10 batting averages from my first, master sheet. How can I do that? Thanks!
MacBook Air 15″, macOS 26.5
First off, I know nothing of baseball scores, but I still think I can help here :)
I'll take it that you have the 'batting averages' calculation done, and you just want to get a list of the top-10.
There are a few ways of doing this. For my example I created a simple table of name/score values:
Then, in a separate table, in cell A2, I enter the formula:
=CHOOSEROWS(SORTBY(Table 1::A:B,Table 1::B,-1),SEQUENCE(10))
Working from the inside-out:
SORTBY() takes all the values in Table 1::A:B (which you would extend to cover your data... I only have two columns in my example), and sorts them in descending order by the values in column B (my 'Score' column). Internally, this creates a sorted list by score.
This is passed to CHOOSEROWS() which returns a subset of those sorted values.
For the selector, I use SEQUENCE(10) which creates a list of values 1..10, indicating the rows that you want to select, but you could also write this out as 1,2,3,4,5,6,7,8,9,10
Now this will return the top 10 scores:
Note that if there are two players with the same 10th score this function will return the one listed first in the original list. This may or may not be a failure state for you. If it is, the function could be rewritten to capture the top 10 scores, even if that returns >10 names. Just let me know.
First off, I know nothing of baseball scores, but I still think I can help here :)
I'll take it that you have the 'batting averages' calculation done, and you just want to get a list of the top-10.
There are a few ways of doing this. For my example I created a simple table of name/score values:
Then, in a separate table, in cell A2, I enter the formula:
=CHOOSEROWS(SORTBY(Table 1::A:B,Table 1::B,-1),SEQUENCE(10))
Working from the inside-out:
SORTBY() takes all the values in Table 1::A:B (which you would extend to cover your data... I only have two columns in my example), and sorts them in descending order by the values in column B (my 'Score' column). Internally, this creates a sorted list by score.
This is passed to CHOOSEROWS() which returns a subset of those sorted values.
For the selector, I use SEQUENCE(10) which creates a list of values 1..10, indicating the rows that you want to select, but you could also write this out as 1,2,3,4,5,6,7,8,9,10
Now this will return the top 10 scores:
Note that if there are two players with the same 10th score this function will return the one listed first in the original list. This may or may not be a failure state for you. If it is, the function could be rewritten to capture the top 10 scores, even if that returns >10 names. Just let me know.
This is super, thank you. I have two next-level questions.
Creating a top 10 batting averages table in Numbers