Hello. I have a sports league that we update the total points standings every week. I want to change the total points value but have the ranking in standings (1-30) stay the same in that lead column. Is this possible yet?
Any help is appreciated, thanks!
you can lock only tables. However... Numbers allows you to create a table with only a single column-- which you can lock
Thanks for the reply. I'm not what you would call an avid user of Numbers. Can I then combine two tables into one so the locked column of "Place in standings" is part of a "team and total points table"?
perhaps posting an example if what you have (omitting personal or private information) would allow me to provide a more specific answer
Here is a link showing how to post a screenshot just in case:
Here you go, Wayne. As you'll see, the column on the left is the place in the standings each team will hold, and the POINTS column will change on a weekly basis. I'd love to simply change the values of the POINTS and not have the places numbers change, which they currently do. Any help/thoughts are appreciated!
Are you saying the order the teams are in should remain the same AND that when you change scores the ranks should update?
If this is the case, then use the formula:
A2=rank(C2, C)
this is shorthand for, select cel A2, then type (or copy and paste from here) the formula:
=rank(C2, C)
select cell A2, copy
select A2 thru the end of column A, paste
Hi g'
As I read it, you want the order of the numbers in column A to remain the same.
You want to change the numbers (scores) in column C.
After recording the new (total) scores in column C, you want to sort the scores using a Descending value sort on column C.
You want the team names to be rearranged to keep each team on the same row as its (sorted) score.
Here's a sample, using 9 teams and made up scores over a period of three weeks. The three images are of the same table.
Teams with names ending in an even number gained points during week 2. Those with names ending in odd numbers did not. The gains were enough to reverse the order of the score totals for the even number teams. Odd number teams kept the same scores, and sank to the lower half of the standings, but kept the same order with respect to the rest of the odd number teams.
In Week 3, only the odd number teams gained points. In some cases the gain was enough to move them up the list, but for team 9, the 10 point gain left the team still in last position.
How it works:
Column A contains a formula, entered into A2, then filled down into all cells bellow that in column A:
A2: =ROW()-1
Column B contains fixed data. The names of the teams are entered once, and do not change.
Column C contains entered data. The numbers will change from week to week as new scores are entered.
After entering the new scores, the whole table is sorted, using "Sort Descending" on the values in column C (Scores).
The numbers in column A are recalculated automatically.
One issue: Note that Team 7 and Team 3 have identical scores, but different ranks (or standings). With identical scores (and nothing else used to determine the rankings), these two teams should have the same rank, determined by the number of teams with better scores, as shown below:
Five teams have scores greater than 15, so he rank of Team 3, and the rank of Team 7 are both 6.
Seven teams have better scores than Team 2, so Team 2's rank is 8.
Another week later, there may be more than one pair of teams with the same scores
No team has a better score than Team 7, and no team has a better score than Team 3. They are both at the top of the standings, in the number 1 position.
Two teams have better scores than Team 9, putting Team 9 in the number 3 position.
ROW()-1 won't do this, as it is aware only of its own position in the table.
RANK is a better function to use here, as it uses the actual values determining each team's standing, and calculates the team's rank based on that score.
A2: =RANK(C,$C,largest is high)
Fill the formula down the rest of column A.
Regards,
Barry
gmort wrote:
I'd love to simply change the values of the POINTS and not have the places numbers change, which they currently do.
If you want to list 1...30 down the left column and have that remain unchanged when you resort the table, then you can do this:
The formula in A2 copied down the column.
=ROW()−1
Then let's say you enter new scores (points) in column C. Assume team C adds 100 points and, for simplicity, the others remain the same.
Now when you sort descending on column C:
You end up with this:
Team C has jumped up to the top of column C. Despite the sort on the new scores, the rankings numbers in column A still remain in the original order as if they were "locked" in place.
SG
Is it possible to lock a single column? Like in a sports table?
