## Does anyone keep salaries and contracts on excel?

Moderator: Baseball Moderators

### Does anyone keep salaries and contracts on excel?

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

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

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

Posts: 13223
(Past Year: -487)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

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
Ron Belliard 4 NG-4
Jim Edmonds 20 NG-4
jfox72
Softball Supervisor

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

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
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.
Big Pimpin
Mod in Retirement

Posts: 13223
(Past Year: -487)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

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

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

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

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

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.
Big Pimpin
Mod in Retirement

Posts: 13223
(Past Year: -487)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

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

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

Next