I'm working on this rather complex - at least for my level - worksheet for my upcoming Fantasy Football league, and I have pretty much everything working the way I want, except for one thing. I'd like to add a tool that would look through my big board and find the best player to match (bye week and playing against tougher opponents) with a player I've selected.
The file is attached to this post.
If you take a look at the first tab, called "DRAFT BOARD", you'll see "MY TEAM" (Columns N:AZ). The formula I'm looking for is to be entered in AZ (Best Match).
The second tab, called BIG BOARD, has a table that lists all the most common players. For this specific task, the only columns worth paying attention to are B, C, F and AK:AZ. That last array works as a filter sorta, basically it returns the value 1 if the player is playing against weak opponents that week (which we won't need), and returns the value -1 if the player is either on BYE week or playing against top-5 defense.
What I'm trying to do is...
1) Search for the player entered in column O of 'DRAFT BOARD' in my [BIGBOARD], and correctly identify the -1 weeks for that specific player.
2) Then, taking ONLY those weeks into account, find the player that is the best match, respecting these three criteria:
...a) Player hasn't been drafted (so column B on [BIGBOARD] is "")
...b) Player is playing the same position (so column C on [BIGBOARD] is the same as column N on 'DRAFT BOARD')
...c) Player returns the MAX value (number of 1's in the AK:AZ array) available ONLY for the weeks we've identified in step 1.
3) Return the name of that player (column F on [BIGBOARD]) as the best match, in column AZ on 'DRAFT BOARD'.
So if we take Aaron Rodgers as an example, you can see he has a -1 in FWK4, FWK13 and FWK14. So I need to find the best match for him by finding the players at the same position, with the most 1s for these three specific weeks. In this case, it would be Matthew Stafford, assuming he hasn't been taken (column B on [BIGBOARD]="") whose SUM for these three weeks would be 3. If already taken (column B on [BIGBOARD]="X"), then the next best match would be Philip Rivers, who's the first entry on [BIGBOARD] matching those criterias, with a SUM of 1 for the weeks used.
I hope I'm making sense.
Anyway, if someone is willing to help, it would be very appreciated.
Oh, one more thing...the BIG BOARD is gonna be sorted, so if two players have the same MAX total, the first entry at the correct position without an X in BIGBOARD[X] should be the result...
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Unfortunately, upon further testing, it seems to only work halfway. However, I "think" I found the reason why, but I'm not sure how to tweak the formula.
When entering Aaron Rodgers, it does give me Matthew Stafford as the best possible match, which is correct (weekly matches). However, once Matthew Stafford gets selected, the recommendation becomes Ben Roethlisberger (who's 0 for those weeks)...but I think the formula you have provided me sees Roethlisberger as a 1, because he has a an entry (-1) in WK4. When looking for the player to match, it seems to calculate any entry, rather than looking for just 1 as a match.
At least, that seems to be the common denominator in each error I've had so far (ran a few tests with a bunch of different names and positions).
I'll try to make sense of it on my side, to see if I can spot the problem, but that formula may be out of my league.
If you have any suggestion, I'm all ears.
But in any case, I appreciate you taking the time and giving it a shot !
...if I could add rep a thousand times, I would.
once Matthew Stafford gets selected, the recommendation becomes Ben Roethlisberger
Could you attach another copy of the file showing this please.
I could follow the logic of the criteria in post #1, but got lost trying to figure out the basic data entry / selection process
Edit:-
Maybe I'm couldn't follow the logic as well as I first thought.
My understanding of it is that Roethlisberger matches the criteria in Week 13, so is tied with Rivers who meets the criteria in Week 4.
If you're saying that the earlier week should have priority then I think that it might be time to look at alternative methods, you're getting to the point where there are too many variables for a formula to process.
You're right, Roethlisberger does have a match in week 13. However, just like Rodgers, he's facing a tougher opponent in week 4, which should negate this...so Roethlisberger's SUM for those three weeks is 0 (-1 in WK4, 1 in WK 13, 0 in WK 14).
Stafford should be the best initial match, as his SUM during these three weeks is 3 (1 in WK4, 1 in WK13, 1 in WK14). Once he gets selected, there aren't any QB with a SUM of 2 for those three weeks in the database, so the next best match should be Philip Rivers, with a SUM of 1 (1 in WK4, 0 in WK13, 0 in WK14). Followed by Romo, Cousins, Dalton and so on, who all have a SUM of 1 as well.
Here, I entered both Rodgers and Stafford in this file. I also added a simple SUM on 'BIG BOARD' column BB, so you can easily see the best option available.
(Note: I moved a few columns in the table, but it shouldn't be any concern for the formula, it works as is).
Hmmm...unfortunately, it doesn't seem to work properly.
Now Aaron Rodgers' first best match is Philip Rivers (whose SUM is 1) instead of Matthew Stafford (whose SUM is 3).
Edit: I think I may have found a different way to do it. It requires a few change to my table, and it's way too late for me to try it out now (almost 2 AM here), but I'll give it a shot tomorrow, and will let you know if it works or not.
Basically, instead of having an entire column of best possible match on 'DRAFT BOARD', I added a tool underneath the 'MY TEAM' table, where I have to manually input the name of the player I want to find the best match to.
Then, on 'BIG BOARD', I added a column header above AK:AZ, with a simple IF function that looks up for the name entered in the tool described before, and return a "Y" for each -1 that player has, week by week. Then, added a column to 'BIG BOARD' table (column BA) that SUMIF every week with a "Y" for every player, and combined that with an IF(OR( to erase results for players that a) have already been selected ("X" in column B) and/or b) don't play the same position as the player entered in the tool on 'DRAFT BOARD'.
From there, I added an INDEX/MATCH function, combined with a MAX to find the best available result in the new BA column on 'BIG BOARD', and return the name of the player...and BOOOM ! Voilą !
It's not perfect, as now I have to manually enter the players I want a match for, but it does the trick !
That being said, thank you again for taking the time, and helping me out. I think our discussion ultimately showed me the path to my answer.
I'm uploading the file, just in case you wanted to see what I did. To activate the match tool, you need to enter a name in cell O23 of 'DRAFT BOARD'.
... it's way too late for me to try it out now (almost 2 AM here), but I'll give it a shot tomorrow,
Originally Posted by KomicJ
Alright, well I couldn't resist..
And I thought that I was the only one with such a lack of resistance
When I changed the formula, I had Stafford in the draft table in order to check that those with a total of 1 were being returned correctly, I didn't clear the table to check that it still looked for the higher totals first.
The first method using Countifs needed the frequency array to function, but it was causing problems with sumifs, removing it seems to have worked.
Results, Stafford, Rivers, Romo, Cousins, in that order, that's as far as I checked.
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by KomicJ
Bookmarks