## Excel help (sorry lol)

Moderator: Baseball Moderators

### Excel help (sorry lol)

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.
gostanford07
Minor League Mentor

Posts: 546
Joined: 23 Feb 2005
Home Cafe: Baseball
Location: 707... The home of Jonny Gomes and Jason Lane

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.
screwyinstlouie
Minor League Mentor

Posts: 560
Joined: 12 Jun 2005
Home Cafe: Baseball
Location: St. Louis

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.
The odds of the AL MVP coming from the American League are looking pretty good.
The Jury
General Manager

Posts: 3328
Joined: 17 Feb 2003
Home Cafe: Baseball

Here's what I'd do:

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

3) Make a pivot table (see Excel help)
Matthias
General Manager

Posts: 4860
Joined: 16 Mar 2005
Home Cafe: Baseball

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.
TopChuckie
Softball Supervisor

Posts: 82
(Past Year: 10)
Joined: 3 Feb 2006
Home Cafe: Baseball

yah, but even after he has gathered all the data via vlookup, he's still going to want to average it.

hence, pivot table.
Matthias
General Manager

Posts: 4860
Joined: 16 Mar 2005
Home Cafe: Baseball

(VLOOKUP + VLOOKUP)/2
coolerthanlc
T-Ball Trainer

Posts: 1
Joined: 8 Mar 2006
Home Cafe: Baseball

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).

pivot table.
Matthias
General Manager

Posts: 4860
Joined: 16 Mar 2005
Home Cafe: Baseball

I did this last year. Pretty easy really.

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.
thaklanksta
Minor League Mentor

Posts: 516
Joined: 26 Jul 2003
Home Cafe: Baseball
Location: The BUS

OR.... you can just use a pivot table.

to quote zoolander, "i feel like i'm taking crazy pills!"
Matthias
General Manager

Posts: 4860
Joined: 16 Mar 2005
Home Cafe: Baseball

Next

### Who is online

Users browsing this forum: No registered users and 5 guests