 |
| Register | Login | |||||
 |
Main
| Memberlist
| Active users
| ACS
| Commons
| Calendar
| Online users Ranks | FAQ | Color Chart | Photo album | IRC Chat |
|
| | ||
|
| Register | Login | |||||
|
Main
| Memberlist
| Active users
| ACS
| Commons
| Calendar
| Online users Ranks | FAQ | Color Chart | Photo album | IRC Chat |
|
| | ||
| 0 user currently in Hardware/Software. |
| Acmlm's Board - I2 Archive - Hardware/Software - Excel problem |
 |  |  |
| Add to favorites | "RSS" Feed | Next newer thread | Next older thread |
| User | Post | ||
drjayphd Beamos What's that spell? pimp! Level: 56  Posts: 1266/1477 EXP: 1387410 For next: 10766 Since: 03-15-04 From: CT Since last post: 2 hours Last activity: 2 hours |
| ||
Ooh, a question that's DIFFERENT for a change.  I've made up a spreadsheet for each team in my fantasy football league. It'll calculate player scores, team scores, and the best possible score players could have gotten. 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: lists positions, as in QB, RB, WR, etc. B: If the player in this row is starting, I put "Y" in this cell. C: Player names. Not important. D: Team the player is on. Only important so I can fill in... E: Team's opponent. Only used so if a team has the week off, Q will spit out 0. F-P: 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. 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 A cell says "WR", the two best where it says "RB", and of all the remaining players where A is either "WR" or "RB", the two highest of THOSE. EDIT: I looked this up on Microsoft's web site and they gave me this formula. I've since rearranged the spreadsheet so that the positions (A) are now right next to the points scored (Q). 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. (edited by drjayphd on 08-17-05 11:08 PM) (edited by drjayphd on 08-18-05 06:19 PM) |
| Add to favorites | "RSS" Feed | Next newer thread | Next older thread |
| Acmlm's Board - I2 Archive - Hardware/Software - Excel problem |
| | |
