Queries to Return Highest WAR and Player's Name(s)?
I am trying to devise a formula that scans a table and returns the batter with the highest WAR within a particular age range. For example, of all the batters who played from ages 22 to 27, which of them has the highest cumulative WAR for that range?
I have an Excel file almost 51,000 rows deep that lists more than 9,000 major league batters, who played at ages ranging from 18 to 42. There are hundreds of age ranges within, starting at 18-18, then 18-19, 18-20, etc., up to 18-42; then, 19-19, 19-20, 19-21, etc., up to 19-42; and so on, until we end up at 41-41, 41-42, and finally 42-42.
Each batter's play generates a number of WAR, as you can see on the sample I upload (please focus only on columns B, C, and D; the rest don't matter for my question). WAR is an acronym for Wins Above Replacement. That's not germane to what I'm trying to accomplish but it's context for you. I would need the exact same formula if I had been working with home runs instead of WAR (which I may, once I get this).
What I am try to determine through an elegant Excel formula is, which batter on the table generates the highest cumulative WAR total for each age range on the table. Not the highest WAR for an individual year within the range, but the highest multi-age total by any batter within the entire range.
As an example, let's consider the age range I offered in the original post: 22-27. I want an Excel formula that will scan the entire table and identify every batter who played the game anywhere within that age range (which is most of them). So anyone who played all six of those ages would qualify, as well as any batter who played in any if not all of those ages.
I happen to know there have been 7,828 batters who played during any part or all of the ages 22 to 27. This Excel formula would scan for all the batters in my table who fit that criterion, and then return that batter with the highest cumulative WAR total within: in this case, if the formula works right, it would return 54.9 as that highest number, and hopefully, another formula (using =FILTER, perhaps?) that returns Mickey Mantle as the author of that performance. (You won't get that on my sample table because I truncated it to upload the sample.)
I want to be able to apply the Excel formula to each of the 325 age ranges I am working with in part because I would like to learn how to apply this kind of formula to other queries I might be interested in doing; but, also, to avoid having to do 325 separate queries on this one website I can do age-range queries on.
I hope what I am looking for is clear, but if not let me know how I can help further clarify. I appreciate your help. Thank you!
|