Return to Baseball Leftovers

Does anyone keep salaries and contracts on excel?

Moderator: Baseball Moderators

Does anyone keep salaries and contracts on excel?

Postby jfox72 » Wed Nov 15, 2006 9:42 pm

I need some help or ideas on formulas to help input salaries for the next season. A formula would be easier than manually entering each player for all the teams.
jfox72
Softball Supervisor
Softball Supervisor

User avatar

Posts: 73
(Past Year: 4)
Joined: 24 Nov 2003
Home Cafe: Baseball

Postby Big Pimpin » Thu Nov 16, 2006 12:15 am

Unless you've got salaries in another spreadsheet I don't see how a formula will do you any good... Maybe I'm missing something. :-?
Big Pimpin
Mod in Retirement
Mod in Retirement

User avatar
EditorCafeholicFantasy ExpertCafe WriterCafe RankerGraphics ExpertMock(ing) DrafterEagle EyeWeb SupporterMatchup Meltdown ChampionPick 3 Weekly WinnerLucky Ladders Weekly Winner
Posts: 13223
(Past Year: -487)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

Postby jfox72 » Thu Nov 16, 2006 7:12 am

This is for my keeper baseball league. Every player is given a guarenteed or non guarenteed contract. In the excel program they are designated as a G or NG and followed by the contract length, ex. NG-3 would be a non guarenteed 3 year deal. Every year a NG deal goes up higher than a G deal. I am looking for a formula to punch in that would automatically award the next seasons raises without me having to go in and manually enter them. This is an example of the sheet.

Name Salary($) Contract
Mark Buehrle 8 NG-3
Brett Myers 10 NG-4
David Wright 32 G-3
Todd Walker 0
Jesse Crain 3 G-3
Bobby Crosby 10 NG-3
Jorge Posada 5
Ron Belliard 4 NG-4
Jim Edmonds 20 NG-4
jfox72
Softball Supervisor
Softball Supervisor

User avatar

Posts: 73
(Past Year: 4)
Joined: 24 Nov 2003
Home Cafe: Baseball

Postby Big Pimpin » Thu Nov 16, 2006 9:17 am

jfox72 wrote:This is for my keeper baseball league. Every player is given a guarenteed or non guarenteed contract. In the excel program they are designated as a G or NG and followed by the contract length, ex. NG-3 would be a non guarenteed 3 year deal. Every year a NG deal goes up higher than a G deal. I am looking for a formula to punch in that would automatically award the next seasons raises without me having to go in and manually enter them. This is an example of the sheet.

Name Salary($) Contract
Mark Buehrle 8 NG-3
Brett Myers 10 NG-4
David Wright 32 G-3
Todd Walker 0
Jesse Crain 3 G-3
Bobby Crosby 10 NG-3
Jorge Posada 5
Ron Belliard 4 NG-4
Jim Edmonds 20 NG-4


That's a totally different story... Explain how raises work and we can come up with a formula for you. ;-D
Big Pimpin
Mod in Retirement
Mod in Retirement

User avatar
EditorCafeholicFantasy ExpertCafe WriterCafe RankerGraphics ExpertMock(ing) DrafterEagle EyeWeb SupporterMatchup Meltdown ChampionPick 3 Weekly WinnerLucky Ladders Weekly Winner
Posts: 13223
(Past Year: -487)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

Postby jfox72 » Thu Nov 16, 2006 9:16 pm

This is how I do raises in my league:

1. (G) GUARANTEED CONTRACTS:

$1-20 = $1 raise
$21-40 = $2 raise
$41 and over = $3 raise


2. (NG) NON-GUARANTEED CONTRACTS:

$1-20 = $3 raise
$21-30 = $4 raise
$31 to 40 = $5 raise
$41 to 50 = $6 raise
$51 and over = $7 raise
jfox72
Softball Supervisor
Softball Supervisor

User avatar

Posts: 73
(Past Year: 4)
Joined: 24 Nov 2003
Home Cafe: Baseball

Postby Matthias » Thu Nov 16, 2006 9:35 pm

You can make an embedded if function for guaranteed and non-guaranteed, but it would probably just be easier to sort by it and make two different formulas.

For guaranteed, you can write it as (assuming your salary info is in B1):
If(B1<21,B1+1,if(B1<41,B1+2,B1+3)))

And then non-guaranteed would be:
If(B1<21,B1+3,if(B1<31,B1+4,if(B1<41,B1+5,if(B1<51,B1+6,B1+7))))
Matthias
General Manager
General Manager


Posts: 4826
(Past Year: -34)
Joined: 16 Mar 2005
Home Cafe: Baseball

Postby Big Pimpin » Fri Nov 17, 2006 12:01 am

Matthias wrote:You can make an embedded if function for guaranteed and non-guaranteed, but it would probably just be easier to sort by it and make two different formulas.

For guaranteed, you can write it as (assuming your salary info is in B1):
If(B1<21,B1+1,if(B1<41,B1+2,B1+3)))

And then non-guaranteed would be:
If(B1<21,B1+3,if(B1<31,B1+4,if(B1<41,B1+5,if(B1<51,B1+6,B1+7))))


I would make just one formula (actually I'm doing it in two steps). I don't know that you want to sort and what not if you've got an order for your players and such...

Anyway, I'll assume that the entire name is in Column A, the Salary is in B, and the Contract status is in C. The headings are in Row 1, so Row 2 would be A2: Mark Buerhle, B2: 8, and C2: NG-3.

So, in D2 I would paste this formula
Code: Select all
=IF(C2="","",LEFT(C2,FIND("-",C2)-1))


That will tell you whether it's a "NG" or a "G" contract.

Next, in E2, I would paste this formula
Code: Select all
=IF(D2="","",IF(D2="G",IF(B2<21,B2+1,IF(B2<41,B2+2,B2+3)),IF(B2<21,B2+3,IF(B2<31,B2+4,IF(B2<41,B2+5,IF(B2<51,B2+6,B2+7))))))


Then just copy the two columns all the way down. This will also put blanks in the cells where your guys don't have contracts anymore, so you won't have that clutter. You'll obviously have to change cell references if my assumptions are off. Let me know if you have questions. ;-D
Big Pimpin
Mod in Retirement
Mod in Retirement

User avatar
EditorCafeholicFantasy ExpertCafe WriterCafe RankerGraphics ExpertMock(ing) DrafterEagle EyeWeb SupporterMatchup Meltdown ChampionPick 3 Weekly WinnerLucky Ladders Weekly Winner
Posts: 13223
(Past Year: -487)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

Postby jfox72 » Fri Nov 17, 2006 7:11 am

Thanks, I will try and see if I can get it.
jfox72
Softball Supervisor
Softball Supervisor

User avatar

Posts: 73
(Past Year: 4)
Joined: 24 Nov 2003
Home Cafe: Baseball

Next

Return to Baseball Leftovers

Who is online

Users browsing this forum: No registered users and 2 guests