The W
Views: 130836838
Main | FAQ | Search: Y! / G | Calendar | Color chart | Log in for more!
22.2.18 0904
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
(689 newer) Next thread | Previous thread
Post (1 total)

Since: 22.4.02
From: Outside of Boston

Since last post: 158 days
Last activity: 10 hours
#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.


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
(689 newer) Next thread | Previous thread
Seems fine to me, even here at work. First page shows all the Diva Search contestants.
- Deacon Jim, isn't working for me. (2006)
The W - Internet & Computers - Excel help requestedRegister and log in to post!

The W™ message board

©2001-2018 Brothers Zim

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