>>> Oh well, the favor involves an Excel spreadsheet that I am using to
>>> keep track of the team’s standings. I have a formula in there to
>>> calculate Win-Loss percent as you will see, but what I would also like
>>>each table (AL East, AL West, NL East, NL West) is sort the teams
>>>according to W-L percentage.
>>>
>>> I think what my problem is..is that I am trying to sort ‘within’ the
>>> spreadsheet, ie, just certain cols and rows. If I made each table
>>> (e.g. AL East) a separate spreadhseet then I suppose I could do it.
>>> Well don’t spend too much time on it…(you are too expensive for
>>> me)…I just thought I would give it a try.
>> I copied your sheet 1 to sheet2.
>> I removed your extra columns, merged cells, and two hidden merged cell
>> columns.
>> I “equalized” in column width all cells.
>> I then selected each range to be sorted. Did “data”
>> “sort” “PCT” for the range.
>> I repeated that for each one of the four ranges.
>> I think that is what you were trying to do.
>> It should be possible to code a macro to sort all four ranges
>> automagically.
>> (But you said don’t spend a lot of time.)
>That was great, thanks so much! So it looks like after I make an update,
>that all I have to do is then highlight the range to be sorted, and Select
> Data, Sort. I gave it a test and it worked great.
>
>My next challenge will be to try and write a macro to calculate the GB
> (Games Back)! Thanks again John, I knew you would know how to do that.
http://www.math.utoronto.ca/mathnet/questionCorner/baseball.html
*** begin quote ***
UNIVERSITY OF TORONTO MATHEMATICS NETWORK
Question Corner and Discussion Area
How To Compute Standings In Baseball
Asked by David Tobey on October 6, 1997:
In sports statistics, namely baseball, how do you calculate the number of games that a second place team is behind the first place team? This is easy if both teams have played the same number of games, but if they have not played the same amount, it isn’t so clear. This sounds like a fascinating mathematical question for a math fan and sports buff. I have come up with a few scenarios, but never have seen a definitive formula.
*** end quote ***
Their Answer:
===
My summary: The original question taught me that sorting in Excel can not handle “hidden columns” (i.e., width set to zero — a consultant’s trick), columns of unequal width, and merged cells. Interesting facts I was unaware of. The effort was time stopped before I learned how to sort four ranges on the same page. I assume a macro, but I’ll bet named ranges might work as well since all sort on the same “pct”. Finally, the heavy lifting of calculating games behind was solved nicely by the University of Toronto. Thanks to the folks there. Finally that was interesting because they don’t play baseball. :-) For all those kids who went to gooferment skool, that formula is (I guess) the Canadian way of writing the generalized solution to a quadratic equation. Also interesting.
An interesting interesting exercise.
Reinke Faces Life 