Excel Spreadsheet question

[playsccr]

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

[RobR]

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.

[Tom_T]

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

[ajc730]

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.

[playsccr]

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

[RobR]

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.

[Tom_T]

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.

[playsccr]

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

[Michael@HQ]

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.

[playsccr]

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

[Tom_T]

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.

[steve1066]

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