Alright, let me start off by warning you that I have basically no background in excel, so this may be a stupid question. Anyway, let's say that I have multiple sources of projected stats and want to combine them to one spreadsheet using averages of each source. I did some searching and found a recent post on the topic, but could not quite understand it. Do all of my players from each position need to be in the same order? If so, then is there an easy way to make one column uniform for all of the different sources? Any help is greatly appreciated... and please bear with me since I'm relatively new to spreadsheets.
Minor League Mentor
Joined: 23 Feb 2005
Home Cafe: Baseball
Location: 707... The home of Jonny Gomes and Jason Lane
screwyinstlouie wrote:I suppose you could set up indiv. sheets on your Excel file for each projection source, then create a summary sheet and link the formulas to each source...
Sheet1 - Abreu (in cell +A2) 30 (for proj 30 HR's in Cell +B2) Sheet2 - Abreu (also in Cell +A2) 26 (for proj 26 HR's; also Cell +B2) Sheet3 - (summary) - Abreu (Cell +A2); Cell +B2 =+('Sheet1'!B2+'Sheet2'!B2)/2 you should get 28 HRs avg
But it would get tricky unless you had the same number of players and stats, and as you suggested, in the same order on all the sheets (rows for players as well as columns for stats).
I'm no expert on databases, but it sounds to me like a database program such as Microsoft Access or Lotus Approach might serve you better. Good luck to you.
Yeah you can use formulas that incorporate data from more than one sheet, but I'm aware of Excel having a fantasy sports option to "combine several sheets." I don't think it's smart enough to do that yet.
[quote:4fef447375="Geek"]The odds of the AL MVP coming from the American League are looking pretty good.[/quote]
1) Make sure all the player names are spelled the same (sort them, copy them, and do a formula which is [cell1] = [cell2] (A1 = B1), for instance. If it's the same, it will say true. If it's not, it will say false.
2) Make sure all the stats are in the same order using cut/paste
I think the easiest way to accomplish what you are asking is the VLOOKUP function. It is invaluable for compiling fantasy baseball data from different sources. It's a bit much to explain here, but once you know how to use it you will save a lot of time and avoid a lot of mistakes.
The key is for all the names to be formatted exactly the same from each source, i.e. if one source is "Griffey, Ken", you need to make all sources "Griffey, Ken" as opposed to "Griffey,Ken" or Griffey Jr, Ken" or "Ken Griffey". There are some tricks for expediting the formatting of the names from each source using Text to Columns and Concatenate.
yah. i get it. but what's easier: creating X number of vlookups and averaging formulas therefrom or just doing the 10-second pivot table?
if you've ever done both, the answer is (b). besides, if you don't want to make the vlookup overly cumbersome, then you have to create references within the formula to move over the correct number of columns, and some references are fixed, and some are not, etc., etc. correct answer is (b).
Put in your players by position and leave columns to the right for each source. Put in the ranking for each source next to the persons name. At the end of the end of each column you can write a formula to add across the rankings from each and divide it by the number of sources you received. Yopu can then sort each position based on the overall average.......
Pm me your e-mail and I will send you a step by step totorial of how to do this with snag-it screen shots.