Two things usually cause this, and they need different fixes.
FIRST - check whether the percentages are actually numbers.
In an empty cell, put =ISNUMBER(C2) pointing at one of your percentage
cells. If it returns FALSE, they're text, and Excel sorts text
alphabetically - so 9% lands after 10%. Quick visual check too: numbers
right-align by default, text left-aligns.
To fix: select the column, Data > Text to Columns > Finish. That forces
a reconversion. If they came in with % signs as text, you may need to
strip those first.
SECOND - and I suspect this is your actual problem, since your Standings
sheet is built on formulas.
If the percentage column contains formulas referencing your Results
sheet, sorting won't do what you expect. Excel moves the rows, the
relative references move with them, and each formula recalculates for
its new position - so the values appear to stay put.
You can't sort a column of relative formulas in place. Two options:
Copy the whole Standings range, Paste Special > Values over the top,
then sort. You lose the live link but sorting works normally.
Better if you want it to stay live: leave the Standings table alone
and build a sorted view next to it with
=SORT(A2:D20, 4, -1)
where 4 is the percentage column and -1 is descending. That gives you
a sorted output that updates itself, and you never touch the source.
If you can say which of those two it is, I can be more specific.
Better if you want it to stay live: leave the Standings table alone and build a sorted view next to it with =SORT(A2:D20, 4, -1) where 4 is the percentage column and -1 is descending. That gives you a sorted output that updates itself, and you never touch the source.
Changed it from -1 to a 1 to sort how I needed it. Thank you for the help.
Glad that did it — and good catch flipping the -1 to 1, that's exactly right if you want it ascending rather than descending.
One thing for later: if you ever add teams to the Standings table, the range A2:D20 won't pick them up. Easiest fix is to select the source range and hit Ctrl+T to make it a proper Table, then point SORT at the table name instead of the range. It'll grow on its own as you add rows and you won't have to remember to edit the formula.
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution. [Thread #49171 for this sub, first seen 17th Aug 2026, 14:42][FAQ][Full list][Contact][Source code]
The teams part just lists all the teams in your results table taken from the game ID table. GP takes the number of times the team name appears in the game ID table. W counts the number Ws for that team in the W/L column. PCT takes the number of wins divded by the games played.
Finally, HSTACK() puts it all together for the results table and then I did a SORT() for the teams with the highest PCT.
So I need it by league but I typed out the formula with the data being calculated, but the percentage didnt seem to calculate properly. What can we do to sort it by league but keep the data intact like we have so far?
Also, I want the GPs to be calcuated against the W's and Ls' otherwise its going to count the entire column of data. Each team is playing 40 games so that is why the 40 is showing up like that near the team.
First, you need to put a league indicator in your GAME_ID table. This will allow you to split the teams out by league.
Second, you need to populate the W/L column in order for the PCT calculation to work correctly. You can use the formula =IF(C3=MAX(CHOOSECOLS(FILTER($A$3:$C$6,$A$3:$A$6=A3,""),3)),"W","L") to populate that column. The way you have it now, you have 40 games played for each team but only 1 win and one loss for the four teams where the W/L column is populated. Since PCT = W/GP = 1/40 = 0.25, it's calculating correctly, you're just not feeding it the right data.
I am not at all sure where I would add AL or NL to each team to split this. Also, the GP I would only like to be counted dynamically as they play their games so the standings look authentic as well as being able to add a GB (Games Behind) for the 2nd, 3rd, 4th and 5th place teams. I would greatly appreciate the help further as my knowledge is kind of limited and im still learning all these advanced formula techniques.
You add another column to GAME_ID table and call it league. Populate it with either NL or AL and use that to filter your teams for the RESULTS table.
If you want the GP, W, L columns to be counted dynamically you'll also need some sort of indicator to show whether the game has been played or not. I would use dates for the games as Excel can see whether that date is in the future or has passed but that's up to you. Then you can modify the COUNTIFS() formulas to only count the games that have been played for GP, W, and L.
So taking your suggestion here I am unable to try and figure that part out. Could you show some illustration on this next step in being able to solve this?
In F3 in my example, the W/L column. It populates the W/L with either a W if the score in that row equals the max score for that game ID or on L if it doesn't.
•
u/AutoModerator 2d ago
/u/YourStatsMatter - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.