Hey, I already did an "Excel help needed" thread back in the olden days... I think. It popped up in auto-complete.
I've made up a spreadsheet for each team in the W League 2. It'll calculate player scores, team scores, and the best possible score players could have gotten (for Manager Efficiency purposes). For the last part, I'm trying to get Excel to return the two highest values for one column if two conditions are true. Here's how the columns are set up.
A: If the player in this row is starting, I put "Y" in this cell. B: Player names. Not important. C: Team the player is on. Only important so I can fill in... D: Team's opponent. Only used so if a team has the week off, Q will spit out 0. E-O: Various stat categories. QB, WR, RB, and TE use all these columns. K uses F-K, and DEF uses F-L. For the purposes of this formula, we'll only concern ourselves with QB, WR, RB, and TE. P: Lists positions, as in QB, RB, WR, etc. Q: The total points a player scored. Calculates the points for each stat.
So, since you can start two players at WR, two at RB, and two more at WR/RB, I want to get the two best scores where the P cell says "WR", the two best where it says "RB", and of all the remaining players where P is either "WR" or "RB", the two highest of THOSE.
I looked this up on Microsoft's web site and they gave me this formula. It's an array formula, and I26 contains "WR", so it's looking for WR's.
*{=INDEX($P$2:$Q$13,SMALL(IF($P$2:$P$13=$I$26,ROW($P$2:$P$13)),ROW(2:13)),2)}*
The problem is that this returns the smallest value. I want the second smallest. Also, if I change the SMALL to LARGE, it spits back not the highest value, but the value from the LAST position listed as WR.
Because I want to be free to update this at work (shhh), the solution can't involve macros. I also don't want to have it put the contents of Q2:Q14 elsewhere. Did that last year and it sucked in an inelegant manner. Anyone have any ideas on how I could do this? |