Results 1 to 12 of 12

Thread: Excel Spreadsheet question

  1. #1
    Join Date
    Jun 2003
    Posts
    2,332

    Exclamation Excel Spreadsheet question

    is there a way in excel to take all the hitters for example and sort them by 1. hrs, sbs, rbis, runs?? thanks..so that i get the guy with the highest combo of them all like soriano i would imagine would be tops...etx..

  2. #2
    Join Date
    Jul 2003
    Posts
    2,423

    Default

    Add a column that combines them and use it for sorting?

    The problem is you need to normalize and marginalize each column before doing this. Otherwise, your list probably won't be in the order you desire. Compare SB and RBI. Max RBI is 130'ish. Max SB is 50'ish. The worst RBI total in the draftable population is well above zero (maybe 20'ish for a 12 team AL league). The worst SB total is in the draftable populations is zero. So adding RBI to SB, the worst RBI guy will get credit for being an avg or better basestealer.
    While the individual man is an insoluble puzzle, in the aggregate he becomes a mathematical certainty.
    --Sherlock Holmes

  3. #3
    Join Date
    Jun 2003
    Posts
    8,405

    Default

    Why not just filter PX and SX? Start with everyone who has 100+ for each, and go from there.

  4. #4
    Join Date
    Apr 2006
    Location
    Washington, DC
    Posts
    1,138

    Default

    You might consider creating a column for each statistic (avg, hr, rbi etc) that gives the percentile rank for each category. Then average (or sum) the percentile ranks. You can then sort on that average/sum and get a list of players ranked by their average 'rank' in each category.
    The Beanball Rotisserie Baseball League -- founded 1997 (7-team, AL-only 5x5 keeper league, 40-man rosters, 25-man active rosters, very limited access to free agents)
    League Championships: 1997, 1999, 2000, 2006

  5. #5
    Join Date
    Jun 2003
    Posts
    2,332

    Default

    maybe i wasnt clear..i exporting all the hitters from rotoloab...and now im just trying to sort them...by home runes, then sbs..etx...

  6. #6
    Join Date
    Jul 2003
    Posts
    2,423

    Default

    I guess it depends where you're exporting from, but if it's the editor then . . . Select the entire range and use the Sort option on the Data menu.
    While the individual man is an insoluble puzzle, in the aggregate he becomes a mathematical certainty.
    --Sherlock Holmes

  7. #7
    Join Date
    Jun 2003
    Posts
    8,405

    Default

    Quote Originally Posted by playsccr View Post
    maybe i wasnt clear..i exporting all the hitters from rotoloab...and now im just trying to sort them...by home runes, then sbs..etx...
    Sorting does not work the way you're expecting. It can only sort one column at a time. You could sort the list in order of descending HR, but it will simply show the number of SBs for each hitter. It will only sort SBs if two hitters have the same number of homers, in order to break the tie:

    Player A: 45 HR, 35 SB
    Player B: 45 HR, 0 SB
    Player C: 43 HR, 5 SB
    Player D: 42 HR, 15 SB

    So, you'd have to go down the list and pick out the power hitters who also have speed.

    Now, if you add Runs into the sort, it makes no sense, because two hitters would have to have the same number of HR and SB before Excel even attempts to further sort by runs. It's certainly not going to look at all the categories and somehow "sum" them to see which hitter has the best overall stats.

  8. #8
    Join Date
    Jun 2003
    Posts
    2,332

    Default

    so there is no easy way for excel to sort it so it gives me the guy with the highest hrs and sbs

  9. #9
    Join Date
    Jun 2003
    Location
    Detroit, MI
    Posts
    25,940

    Default

    It's all easy with Excel. You can filter for someone high in both categories. You can sort first on homers, second on steals in the case of a tie in homers. You can create a custom formula in a new column (such as adding together HR + SB) and sort on that.

    We're unclear what you're trying to do.
    “Wouldn't it be funny if he weren't in there?" -- Charles Dillingham, a pallbearer at Harry Houdini's funeral

  10. #10
    Join Date
    Jun 2003
    Posts
    2,332

    Default

    i want a list that will sort by homeruns, then sbs...then rbis then runs...so lets say howard has 50 hrs...but 0 sbs he gets knocked to the bottom almost whereas m cameron would be ahead because of more sbs...soriano would probably be on top..nl only

  11. #11
    Join Date
    Jun 2003
    Posts
    8,405

    Default

    Quote Originally Posted by playsccr View Post
    i want a list that will sort by homeruns, then sbs...then rbis then runs...so lets say howard has 50 hrs...but 0 sbs he gets knocked to the bottom almost whereas m cameron would be ahead because of more sbs...soriano would probably be on top..nl only
    Such a list is impossible without doing something like ajc's suggestion below. You have to come up with a formula that assigns each player a rank based on his combined stats.

    I think you're making this too complicated. Just filter on PX > 100 and SX > 100, and then sort by OPS or something. Or add PX and SX together. It's going to be subjective, so you'll just have to take the results and highlight the players you're interested in.

  12. #12
    Join Date
    Jun 2003
    Posts
    9

    Default not sure if this would work

    If you're looking for players who provide both how about:
    1) using Bill James' Power/Speed number 2(HRXSB)/HR+SB
    2) a similar calculation using PX and SX, perhaps also weighting by AB

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •