Mike MacGregor

You'll find a VB Pts column on the overall ranking cheatsheet. Scroll to the right in the program or its the 3rd page on the 'cheatsheets' tab when printed.

I've discussed this with a few other people by email before. I think the general consensus was that you do not want to remove keepers from the Cheatsheet Compiler because if you do it will nagatively impact the baseline calcs. Assume 10 team league with last starter baseline method. Say 5 QBs are kept, you take out these 5 players (i.e. take their FF Pts to nil). Then when the Compiler goes to find the baseline for QBs, it looks for the 10th ranked QB but with the first 5 missing, it will actually be picking up the 15th ranked QB. If you just keep the keeper players in, then it is as if they were already drafted. I think that makes sense. As for Draft Buddy, I hadn't considered keepers for that initial version. I think adding them to the end with additional rounds is a good idea. I'll consider changes to account for keepers in the next version.

Hi Brent. Something new I added to the latest Compiler version was to allow the user to completely remove a position from the overall rankings if they so choose. By default, defense and kickers I believe are on this setting (i.e. not in overall rankings). On the parameters sheet, scroll down until you see the box 'Baselines'. See the question that says 'take pos out of...'? Change defense from 'yes' to 'no', re-compile cheatsheets and they should return to the overall ranking.

Bump. A useful post.

Cool. Don't they charge $30 for their product?

No psu, there is not. I figured most leagues at a minimum would distinguish DL, LB and DB. Perhaps a poor assumption on my part? If you have a small number of IDP starters to offensive starters, you might just want to knock them off the overall cheatsheet and look at their total projected points on the position rankings.

Hey Griz. A couple of things for you to read up on: Principles of Value Based Drafting Compiler FAQ explaining baseline methods

I could email you the unzipped version but it is 4 MB. Would that be a problem due to either speed or mailbox size? Send me an email if interested.

I don't know anything about compuserve Is it like AOL?

Hey nate. See if you have the program WinZip on your computer but if not you can download an evaluation version which will work for what you need it to do. Site is http://www.winzip.com/ Once WinZip is installed, it should be fairly straight forward. Double-click the compiler.zip file (from Windows Explorer or similar) and it should bring up a WinZip program showing some buttons and a list of files located in the .zip archive. There will be only 1 file, called cheatsheet_compiler_2002_2.0.xls. Choose the Extract option button, browse to the directory you want to put the Compiler file in and hit Extract. You've just successfully unzipped the file and it is ready to use in Excel. Open it from in Excel, make sure to enable macros at start-up and you're golden.

The data inputted under the Backup column only really comes into play if you choose a VBD method that uses "X players drafted" (methods 2 or 4) rather than "X starters drafted" (methods 1 or 3). It is not the maximum allowed at each position. Any league I've been in has not set any min/max on roster/positions other than (a) need to field a complete starting lineup and ( overall roster limit. This is the way I think pretty much all leagues should operate to give the owner maximum flexibility to make roster management decisions. I typically put the starters and backups in the Compiler as the number at each position that I expect the average team in my leagues to field/draft. Even if I'm in a league with 1-2 RB, 3-4 WR flex option, I'll usually just put in 2 RB, 3 WR starters because most teams will want to draft this way, plus appropriate backups at each position. The sum of the Total Team column should equal the overall roster limit.

Ok, so the more complicated solution it will be then. Tackles and assists are scored the same? That is good because then you can just add them together and double the scoring so they will only take up one category and not two. Now we need to create a category. If you try to add columns to the Compiler, it is a very onnerous process to 'fix it' so it continues to work properly. It involves changing macros, references, etc., etc. I assume all TDs are scored the same, whether they are from fumble recovery, INT or kick return? If that is the case, then what you can do is add the FR/INT TD and Kick TD columns together in a new column or sheet. Then copy these new values back into the FR/INT TD column (make sure to Paste Special..As Values). Now you can use the tackles/assists data in the Kick TD column. If you want to score tackles/assists separately, then maybe you could do the same thing with INT and FR. Both turnovers so they are probably scored the same too, right? As an old professor of mine used to say, "Questions? Comments? Cries of Anguish?"

Dolemite, I tried to recreate the parameters you mentioned, but I'm a bit confused by your comments on backups and team totals. If I set all starters and backups to 0 I get Rabih Abdullah as my #1 overall. Actually, I think I get it now. You should only change the fields on the parameters sheet that are colored yellow. By changing the team total column to 0 beside each position it could have screwed something up. These numbers help drive the position baselines to create the overall cheatsheet. When the Compiler sorts all players to input into the overall ranking, it does it first by the VB ranking number (driven by baseline), and then by alphabetic. If baselines are messed up, then it will be sorted by alphabetic. This explains why I got Abdullah as #1 overall above. Something similar must have happened to you with a minor change. To fix this, you can do one of two things. 1. Re-download the Compiler and start over. 2. Just fix the formulas in that Total Team column on the parameters sheet. Formula for each position should be just add starter + backup cells. Might be easier to just download again. Hope that helps and respond back to let us know if that fixes the problem.

Hi Bill. Send me over the Sportsline projections to take a look at. Email: mike@profootballanalysis.com I never thought the fantasy advice through Sportsline was very good in the past, so I haven't checked their projections. Always found one of the biggest advantages of a Commissioner.com league is opponents who rely heavily on their fantasy info ...too bad with them going pay I think my money league will be moving elsewhere

Ok, back from vacation and ready to answer some questions. Gary, does your league really score tackles and assists for each team? I've never seen that before. Where are you getting the numbers for these? Did you make you own projections for 2002? If you have projected numbers, then let me ask are there any team defense categories currently in the Compiler that you do not use? Blocked kicks or special team TDs perhaps? If there are some unused categories, what you could do is just copy the tackle and assist numbers into these columns and use it for the new stat instead of what it was originally intended for. Respond back to let me know if that works or not. I have another slightly more complicated idea if needed.

Hi Clever. If you want to change the scoring system then change the boxes on the parameters sheet that correspond with each scoring category (yards, TDs, etc.) for each position. If you want to change the player projections that are in there, then go to each position tab and find the FF Today stats. Make changes beside each player as you feel are appropriate. Is that what you are looking for?

Let me just add to make sure to check the FAQ to see if it has an answer to any problems you are having: http://www.profootballanalysis.com/compiler/faq.html In particular, the "macros not enabled" problem which a lot of people seem to be experiencing: The Compiler never prompted me when I opened it if I wanted to enable macros or not. Macros are disabled for some unknown reason. What is the problem?

Title pretty much says it all. I'll be on vacation with my wife for a week so I'm not sure how much I can check in to answer questions on this board or by email. I will try to respond to everyone upon my return. Cheers, Mike

I think this has something to do with file associations in Windows. Windows may be identifying files that end in .xls (you did unzip it, right?) as being Acrobat files. Not too sure off hand how to fix it, and it probably depends on your version of Windows. So, if you open Excel and choose File > Open and browse to the Compiler, it won't open in Excel?

It was developed and works in Excel 97. What I was getting at is I've been giving people advice on how to correct the security settings problem (by leading them to the FAQ) but I myself cannot verify that is in fact the problem.

The tricky thing about this is I run Excel 97 which as far as I can see does not have security settings. However, numerous people have told me this is where the problem lies. Here is a section of the FAQ to refer to: http://www.profootballanalysis.com/compile...ler/faq.html#t3

Hi all. I am working on some upgrades on the Compiler right now and need some input on how some of these new features should work. The biggest thing people keep asking for are expanded scoring systems such as: - bonus for distance TDs and FGs - bonus points for yardage milestones - no points for yardage until hitting X milestone - yards allowed by team defense (tiered scoring system similar to points against) For anyone who has sent me an email about this I usually respond one or both of: 1. Adding scoring for all of these categories increases the file size which costs Mike and myself more to offer the Compiler at FF Today. 2. We can't possibly project all of these stat categories, like Corey Dillon will score 8 TDs but 5 will be within 10 yards, 2 will be beyond 40 and 1 will be beyond 60. Its hard enough projecting the stats we're doing now without opening up all of this. So, here is what I'm asking, finally. I would like to implement these scoring features, but how can it be done keeping in mind the above restrictions? One thing I was thinking is to test some stats from prior years to see what kind of distribution occurs given a total stat number for the season. For example, if a team allowed 3,500 pass yards for the season, how many games on average would be between 0 and 150, 151 and 200, 201 and 250, etc.? If a player is projected to catch 1,400 yards, how many will be over or under 100 yards (or any other milestone number)? I took some stat courses in university but don't have much recollection from them. It seems to me though I should be able to implement some standard deviation data and normal distribution or confidence interval formulas so Excel could calculate the distribution given X stat for each category. This is where I get lost and need some help. Is what I am contemplating even possible? Any math majors out there who can help me out? Looking forward to your responses.

That would be anything prior to Excel 97, which is getting old in its own right.

2. Copy & Paste, Drag-and-Drop Ok, lets look at a completely different way of getting projections in the Compiler, again without retyping them. You have your table of new projections in a blank sheet. What we are going to do is copy them into the Compiler. Wait a second though, because there are some precautions/steps that need to be taken or we're sure to screw up the formulas that calculate the FF Pts. First, the projections we are going to copy in should be in the same order as they will be in the Compiler. That is, for QB it should: Games PaComp PaAtt Comp% PaYd PaTD PaINT RuAtt RuYd RuTD FumLost Plus the player name will be in the first column. If the projections you copied don't include some stats (like Games, or PaComp), then include a blank column in your new projections where those stats would otherwise be. It is almost time to copy them over. First though, we want to insert a blank column beside where we are putting the new projections in the Compiler to temporarily hold each players name. This will help us line up the correct projections with the correct player in the Compiler. Say we are inserting QB projections in Site B of the Compiler. Insert a blank column in column CL (highlight CL and right click Insert). Now we're ready to copy the new projections over. Highlight the new projections, choose Edit > Copy and go to the Compiler. Place the cursor on the top left corner of the space where the new projections will go. Try to keep the formatting of the numbers by choosing Edit > Paste Special.. As Values rather than a straight paste. The new projections are in there now, but they probably aren't lined up with the correct players as shown on the left of the screen. Time to use drag-and-drop. For each player highlight there name and new projections under Site B. Then place your mouse cursor near the edge of the box until an arrow appears. When this happens, hold down the mouse button and you can move (drag) the cells to a new spot. Line it up with the player name from the Compiler and release the mouse button to drop it in place. Think of this as a puzzle. You might need to move some players out of the way before you can move new players in the right place. Or, if some players are in the same order, you can move a bunch at one time. The biggest precaution with drag-and-drop is it will mess up the formulas. What I suggest is to never drag-and-drop anything in or out of the top player row. That way the formulas in that top row should never be disrupted. Once all of your other drag-and-drop is done, copy all the formulas in row 5 from column I all the way to column AH down over all the other players. That should correct any problems that occured from drag-and-drop.

Formatting Player Names From above, I skipped over the part about getting player names to the same format between the new projection table and the Compiler, but this is a crucial step if they are not already conveniently the same (unlikely). If you copy projections off a website, then they can probably be in a few different forms, like: Column A Column B 1 Manning,Peyton 2 Manning, Peyton 3 Peyton Manning 4 Peyton Manning 5 Manning Peyton 6 P. Manning The first one in the list is great because that is the way names are inputted in the Compiler. Any of the other ones we need to do some work. 1. Edit > Replace In number 2 in the list above, the First Name and Last Name are in the right order, separated by a comma, but that space in there will throw us off. Remember, it has to be exact. We can quickly fix this though. Just highlight all of the names in your new projections table, choose Edit > Replace from the top menu. In the "Find" box just put one space " ". In the "Replace" box, don't put anything. Hit the "Replace All" button and it should quickly get rid of those spaces in the cells that were highlighted. One thing to be careful of is of players with more than just that space before the comma. If they have 3 names (Randle El, Antwaan) then the replace will slam the Randle and the El together (RandleEl) which isn't right at all. Just watch out for things like that, but it is pretty rare. 2. Data > Text to Columns.. Example 3, Peyton Manning, is no good to us because although the name is in a single cell (good), the First Name and Last Name are in the wrong order. This one is going to take a couple of steps. First, we need to split apart the name into 2 columns. Insert a blank column beside the player names column. Then highlight all of the player names and choose Data > Text to Columns.. from the menu. At this point it gives you two choices, Delimited and Fixed Width. We want Delimited. Choose this and hit "Next". Since we have a space between the First and Last Name, that is the delimiter we want. Choose the "space" checkbox. You should see an example of the data splitting into 2 columns. Hit "Finish" and now the names should be in 2 columns. Still not what we are looking for but we're getting there. Which leads me to... 3. CONCATENATE function What this does is combines cells, text, etc. into a single cell. Do you see where this is going? We're going to combine the cell holding the players Last Name, a comma and the cell holding the players First Name. Say we now have the following: A B C 1 Peyton Manning In our example, we're going to put the following formula in cell C1: =CONCATENATE(B1,",",A1) This will result in Manning,Peyton, which is cell B1 + a comma + cell A1. Copy this formula down for all players. Now, similar to our first post, we want to eliminate the formulas and just have the values. Highlight and copy the player names created from the CONCATENATE functions. Then in exactly the same spot, choose Paste Special.. As Values. At this point you're effectively turned Peyton Manning into Manning,Peyton and are ready to apply the VLOOKUP functions. In the examples above, the names that are already in 2 columns you won't have to worry about splitting the name. Just apply the CONCATENATE concepts and you should be good to go. 4. P. Manning This one is a problem. Obviously no Excel functions or features are going to fill in missing characters in a name. But, if you have a situation like this, maybe you don't want to do VLOOKUP at all but rather use copy and paste. Let me give some hints and warnings on potential traps with that in my next post.