Maddon is going to get off the hook for this because it worked out but...
Down 8-2 in the 8th inning, bases loaded, 2 outs, and they bring their LEFTY closer into the game. Sticking with fucking Jason Heyward over Albert Almora, who is on the bench and who happens to slaughter lefty pitching, is indefensible. Yes, Heyward hit a 18 hopper weakass grounder through the middle somehow. But it was still a fucking stupid move. Really worked up about this one.
It can be done.
For clarification- in determining the champion school for a race, you are taking the four highest scores (subject to the bbbg/gggb constraint) from all eight events (V,JV,Soph,Fresh for boys and girls)? It could be from any level- ie, in theory if a school got first place in both boys and girls on the Freshman/JV level, they could win the event? How many kids are in each race/how many schools are there? Or does that fluctuate.
If I'm understanding you correctly, this should do. What's in formula bar up top is the formula in highlighted cell (J2):
I2 (column labeled "Last Year") is calculating like you do- max function.
You mentioned that sometimes the test score may be the column before. If that is the case, change the "+1" at the very end of the formula to "-1" and you're good to go.
Not allowing myself to get excited yet but Heyward actually looked passable at the plate today. Don't know any fans who haven't given up on him... if he returned to league average, it would make this lineup terrifying (as if it isn't already).