Writers of Pro Football Prospectus 2008

18 Aug 2012

QB flex question

My league scoring has been adjusted this year to try to equalize scoring between QB, RB, WR and TE (1 point for 30 yards passing, 1 point for 10 yards running or receiving + PPR). We have also added an option to flex any offensive player (including QB). I know there is no option for QB flex in the scoring, but if I remove the flex option and add 0.25 to the numbers for starters for each position, will this approximate a QB flex option?

Posted by: super.mighty on 18 Aug 2012

6 replies , Last at 01 Sep 2012, 11:39am by Zac

Re: QB flex question
by Zac :: Thu, 08/23/2012 - 12:29pm

I know I suggested that in another thread, but my recent analysis of the KUBIAK macros indicates that all variables get pulled into the macros as integers, so the .25 probably doesn't work.

However, we can get around this with a little more trickeration of the settings. Take the roster information, and multiply it all by the number of teams. Then enter that as the roster information, and set number of league participants to 1. For instance, in a 12 team league where you start 1 QB, 2 RB, 2 WR, 1 TE, 1 Flex (that can be any of those 4), 1 K, 1 DEF, and let's say 6 reserves, the settings would be:
League Participants: 1
QBs: 15 (1.25 * 12)
RB: 27 (2.25 * 12)
WR: 27 (2.25 * 12)
TE: 15 (1.25 * 12)
Rb/WR Flex: 0
K: 12
DEF: 12
Bench: 72 (6 * 12)
And if you are in an auction league make sure to set the budget to be the total amount of money available for the entire league.

This should set the baselines properly. The only issue is that it will disable the ADP column because that auto-calculates based on the number of teams in your league. If you go this route, I can show you how to manually adjust the formula to show the ADP data that you want it to show.

Once you get the spreadsheet generated, go to the baselines tab and see if the 4 baselines are approximately equal. If they're not, you may need to adjust them a little bit (add 1 player at the highest baseline position, remove one player at the lowest baseline, recalculate, check again). It's quite a difficult (some might say impossible?) task to balance positions in this manner at both the top end and the bottom end. I suspect a QB in the 13-24 range might still outscore a RB in the 25-36 range, given that those QBs have guaranteed playing time and those RBs are probably in committees.

Re: QB flex question
by Zac :: Sat, 08/18/2012 - 1:32pm

Alright, might as well deal with the ADP issue right away.
If you go with this method, then you need to change the formula to manually point to the proper ADP information. Remember that if you are in multiple leagues, these ADP numbers will no longer change if you change the number of teams. (You can always download a new copy of the spreadsheet if you want to get that back).

Copy the proper formula below and paste it into cell CX2 (the ADP Rank column for the highest ranked player). Note that on my screen, these formulas go past the white into the green a little bit. Make sure you copy the whole thing.

8 teams: =IF(ISNA(VLOOKUP($F2,$ADP.$D$2:$I$196,3,FALSE())),COUNTA($A$1:$A$1048293)+100,VLOOKUP($F2,$ADP.$D$2:$I$196,3,FALSE()))
9/10 teams: =IF(ISNA(VLOOKUP($F2,$ADP.$D$2:$I$196,4,FALSE())),COUNTA($A$1:$A$1048293)+100,VLOOKUP($F2,$ADP.$D$2:$I$196,4,FALSE()))
11/12/13 teams: =IF(ISNA(VLOOKUP($F2,$ADP.$D$2:$I$196,5,FALSE())),COUNTA($A$1:$A$1048293)+100,VLOOKUP($F2,$ADP.$D$2:$I$196,5,FALSE()))
14 or more teams: =IF(ISNA(VLOOKUP($F2,$ADP.$D$2:$I$196,6,FALSE())),COUNTA($A$1:$A$1048293)+100,VLOOKUP($F2,$ADP.$D$2:$I$196,6,FALSE()))

Click in box CX2 and do the key combination Ctrl-Shift-Down. That should hilight the ADP ranks for every player. Then do a Fill Down (Ctrl-D) and it should copy that formula in for every other player as well.

Re: QB flex question
by super.mighty :: Sun, 08/19/2012 - 11:16am

Thanks Zac - in a 14 team league i still end up with some decimal points (0.5) showing up. Is this a small enough roundoff error given the size of the numbers that it won't make much difference in the final data?

Re: QB flex question
by Zac :: Sun, 08/19/2012 - 9:25pm

Yeah if you're talking about say 24.5 starting RBs vs 25 then that's not a big deal. It's either going to truncate it down to 24 or round it up to 25 anyway.

Re: QB flex question
by super.mighty :: Sat, 09/01/2012 - 9:51am

Hi Zac-
quick question for you on the formulas above - When I enter it in exactly it gives me an error message, but in referring to the original formula, this is fixed when I change "$ADP." to "ADP!" (i.e., remove the $, replace . with !). Is this still ok? is there supposed to be a "!" instead of a "." between $ADP and $D$2?

Re: QB flex question
by Zac :: Sat, 09/01/2012 - 11:39am

Yeah, that'll be fine. I use a flavor of OpenOffice called LibreOffice. I thought it used all the same syntax as Microsoft Excel, but apparently it does the references to other sheets differently than Excel.

Login or register to post comments