Announcement

Collapse
No announcement yet.

Excel Spreadsheet question

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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

    Comment


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

      Comment


      • #4
        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

        Comment


        • #5
          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...

          Comment


          • #6
            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

            Comment


            • #7
              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.

              Comment


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

                Comment


                • #9
                  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.
                  "I made baseball as fun as doing your taxes!" -- Bill James on The Simpsons

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      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.

                      Comment


                      • #12
                        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

                        Comment

                        Working...
                        X