Excel help (sorry lol) - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to Baseball Leftovers

Excel help (sorry lol)

Moderator: Baseball Moderators

Excel help (sorry lol)

Postby gostanford07 » Mon Mar 06, 2006 2:02 am

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

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

Postby screwyinstlouie » Mon Mar 06, 2006 4:29 am

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

User avatar

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

Postby The Jury » Mon Mar 06, 2006 12:31 pm

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]
The Jury
General Manager
General Manager

User avatar

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

Postby Matthias » Mon Mar 06, 2006 1:06 pm

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
General Manager


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

Postby TopChuckie » Wed Mar 08, 2006 11:55 am

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


Posts: 73
(Past Year: 4)
Joined: 3 Feb 2006
Home Cafe: Baseball

Postby Matthias » Wed Mar 08, 2006 12:20 pm

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
General Manager


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

Postby coolerthanlc » Wed Mar 08, 2006 4:08 pm

Matthias, for your consideration:

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


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

Postby Matthias » Wed Mar 08, 2006 4:14 pm

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
General Manager


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

Postby thaklanksta » Wed Mar 08, 2006 6:40 pm

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

Pick 3 Weekly Winner
Posts: 516
Joined: 26 Jul 2003
Home Cafe: Baseball
Location: The BUS

Postby Matthias » Wed Mar 08, 2006 6:51 pm

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

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


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

Next

Return to Baseball Leftovers

Who is online

Users browsing this forum: No registered users and 14 guests

Forums Articles & Tips Sleepers Rankings Leagues


Today's Games
Sunday, Apr. 20
(All times are EST, weather icons show forecast for game time)

Toronto at Cleveland
(1:05 pm)
LA Angels at Detroit
(1:08 pm)
Seattle at Miami
(1:10 pm)
indoors
Atlanta at NY Mets
(1:10 pm)
Milwaukee at Pittsburgh
(1:35 pm)
St. Louis at Washington
(1:35 pm)
NY Yankees at Tampa Bay
(1:40 pm)
indoors
Minnesota at Kansas City
(2:10 pm)
Cincinnati at Chi Cubs
(2:20 pm)
Chi White Sox at Texas
(3:05 pm)
Houston at Oakland
(4:05 pm)
Philadelphia at Colorado
(4:10 pm)
Arizona at LA Dodgers
(4:10 pm)
San Francisco at San Diego
(4:10 pm)
Baltimore at Boston
(7:05 pm)

  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact