Have an excel question for roster and salary management - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to Commissioner's Corner

Have an excel question for roster and salary management

Moderator: Baseball Moderators

Have an excel question for roster and salary management

Postby jfox72 » Sun Oct 14, 2007 4:21 pm

We use excel to organize salaries and contracts for every team. I need an easy way to go through all the teams and subtract one year from each players contract. This is an example of a team.

Name Salary($) Contract
Paul Konerko 29 NG-4
Zach Greinke 5
Delmon Young 1 G-4
Manny Ramirez 42 G-1
John Lackey 19 NG-3
Todd Jones 3 NG-4
Josh Fields 1 NG-4
Matt Cain 4 NG-3
Scot Shields 6 NG-4
Hunter Pence 1 G-4
Chone Figgins 13 NG-2
Jeff Francoeur 3 G-4
Francisco Liriano 4 NG-3
Derrek Lee 34 NG-2
Derek Jeter 40 NG-4
James Shields 0
Rich Hill 15 NG-4
Corey Hart 1 G-4
Troy Glaus 5
Carlos Villanueva 5
Kevin Gregg 5
Eric Chavez 5
Juan Uribe 5
Felix Pie 1 G-4
Jeff Mathis 0
Jeremy Bonderman 9 G-2
Dustin McGowan 5
Jared Burton 0
Cap Hit 4
Total 265
jfox72
Softball Supervisor
Softball Supervisor

User avatar

Posts: 69
Joined: 24 Nov 2003
Home Cafe: Baseball

Re: Have an excel question for roster and salary management

Postby mission27 » Sat Oct 20, 2007 5:40 pm

Ill email you a word doc with instructions, hope it helps :-) !
Image
mission27
Major League Manager
Major League Manager

User avatar

Posts: 1132
Joined: 12 Feb 2007
Home Cafe: Baseball
Location: Connecticut

Re: Have an excel question for roster and salary management

Postby BigKahuna83 » Thu Jan 03, 2008 4:46 pm

could i get a copy too please ;-D
BigKahuna83
College Coach
College Coach

User avatar

Posts: 110
(Past Year: 1)
Joined: 16 Aug 2006
Home Cafe: Baseball
Location: Top of the Fantasy Mountain!

Re: Have an excel question for roster and salary management

Postby Matthias » Thu Jan 03, 2008 5:46 pm

All you need is this...

=MID(A1,FIND("-",A1)+1,1)

assuming that the player you're looking at is in cell A1 and then just copy down.

Then subtract 1 off of each number. And then the formula...

=LEFT(A1,FIND("-",A1))

will give you everything but the number.

And then if your new number is in C1 and the rest of it is in D1 just...

=D1&C1

For the future, though, it's just better to keep the number of years in the contract in its own column. And then Guaranteed/Non-Guaranteed in its own column. Then you don't have to do anything except subtract one.

Note that this only works because the OP had his years in the format where he had a dash (-) before the years and so that's what it was looking for. If you don't have something like that, then it won't work.
0-3 to 4-3. Worst choke in the history of baseball. Enough said.
Matthias
General Manager
General Manager


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

Re: Have an excel question for roster and salary management

Postby JTWood » Fri Jan 04, 2008 12:45 am

I'd just select the column with all the data in it and use "Tools -> Text to Columns" to delimit on dashes (enter it into the other field).

That will put the contract years into their own column (column B) and remove the dash. Do a formula in column C like =if(B1>0,B1-1,"") to subtract one from each player who had a multi-year contract. Make sure to copy and paste special -> values over the formula after doing that so you don't change things later.

So now you have most of the original data in column A, the original contract length in column B, and the new contract length in Column C. To put it back together in the old format, enter this formula into column D: =A1&"-"&C1. Copy that formula all the way down, copy, and paste special -> values over the final data. Voila!

;-D
Image
JTWood
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicCafe WriterEagle EyeWeb Supporter
Posts: 11508
Joined: 22 Jun 2004
Home Cafe: Baseball
Location: Unincorporated Heaven

Re: Have an excel question for roster and salary management

Postby Curtis Pride » Fri Jan 04, 2008 12:51 am

yeah, text to columns is the easiest thing for me. I like to rely on as simple formulas as possible and try to make each cell only have one bit of data.
Curtis Pride
Major League Manager
Major League Manager

User avatar

Posts: 1361
(Past Year: 3)
Joined: 19 May 2007
Home Cafe: Baseball


Return to Commissioner's Corner

Who is online

Users browsing this forum: No registered users and 1 guest

Forums Articles & Tips Sleepers Rankings Leagues


Today's Games
Saturday, Jul. 26
(All times are EST, weather icons show forecast for game time)

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

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