The W
Views: 100800384
Main | FAQ | Search: Y! / G | Calendar | Color chart | Log in for more!
24.11.14 0815
The W - Internet & Computers - Excel help requested
This thread has 2 referrals leading to it
Register and log in to post!
Thread rated: 5.57
Pages: 1
(672 newer) Next thread | Previous thread
User
Post (1 total)
drjayphd
Scrapple
Moderator








Since: 22.4.02
From: Long Island

Since last post: 18 days
Last activity: 1 day
AIM:  
ICQ:  
Y!:
#1 Posted on | Instant Rating: 5.57
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?



You wanted the best, you got... Out of Context Quote of the Week.

"...but that doesn't mean he can't relate an amusing anecdote about the Haiti Kid and one of the Frenchman's testicles." (Hogan's My Dad)

Promote this thread!
Thread rated: 5.57
Pages: 1
Thread ahead: DVD Burners
Next thread: Google Earth +WWE
Previous thread: Internet poker
(672 newer) Next thread | Previous thread
If you have a scroll wheel on your mouse and click the link with it, it'll open the link in a new tab. CTRL-left click does the same. There's no option to make that the default behavior.
The W - Internet & Computers - Excel help requestedRegister and log in to post!

The W™ message board

ZimBoard
©2001-2014 Brothers Zim

This old hunk of junk rendered your page in 0.059 seconds.