The W
Views: 100237925
Main | FAQ | Search: Y! / G | Color chart | Log in for more!
30.10.07 1249
The 7 - Internet & Computers - Excel help requested Register and log in to post!
(317 newer) Next thread | Previous thread
User
Post (1 total)
drjayphd
Scrapple
Moderator
Level: 115

Posts: 2520/3944
EXP: 16751270
For next: 60145

Since: 22.4.02
From: Long Island

Since last post: 3 days
Last activity: 3 days
AIM:  
ICQ:  
Y!:
#1 Posted on 19.8.05 0013.39
Reposted on: 19.8.12 0015.01
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?
Promote this thread!
ALL ORIGINAL POSTS IN THIS THREAD ARE NOW AVAILABLE
Thread ahead: DVD Burners
Next thread: Google Earth +WWE
Previous thread: Internet poker
(317 newer) Next thread | Previous thread
The 7 - Internet & Computers - Excel help requestedRegister and log in to post!

The W™ message board - 7 year recycle

ZimBoard
©2001-2014 Brothers Zim
This old hunk of junk rendered your page in 0.127 seconds.