View Single Post
 
Old 03-09-2020, 03:53 PM
chasfh chasfh is offline Windows 7 64bit Office 2016
Novice
 
Join Date: Apr 2018
Posts: 10
chasfh is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
I think Rep2 does what you want.

Btw, Mickey Mantle example data didn't contain age 27. So Ty Cobb will have max total WAR for age range 22-27 instead.

The season in report is earliest one for this player in age range. You can modify formula for column R2NameWarSumRow to get player seasons reversed (...[Season];">=" & [@Season]...) - then latest season in age range will be returned instead.

To get all players with max total score and all their seasons in selected age range listed in report, additional helper column(s) is/are needed (you need to number all rows for all players with max total WAR in some order). When you want this, than the example must have given enough clues for you!
I'm sorry it's taken me so long to get back to you. Your solution got me significantly closer, but it's too unwieldy to be practical.

Your solution actually does work, as long as I change the formula in B4 on your Rep2 tab from SUMIFS to MAXIFS. That's because every player season on the DataTable tab sums up the total WAR that player has and places it in each season, so using SUMIFS gets you as many multiples as seasons you are querying. That fix was simple.

The problem occurs when I expand the DataTable from the 4,000 player seasons I gave you in the example to include the full universe of player seasons, some 83,000+. It balloons the calculations such that if I simply change dates and recalculate, it takes well over a minute to calculate the result. Even with 32GB of RAM and a CORE i7 9th Gen processor, it must be going through zillions of calculations just to get the answer.

I really appreciate the work you put into this to help me get the answer. Thank you.
Reply With Quote