Posted by pjbrankin on 8/5/2010 11:34:00 PM (view original):
does someone have a template of the excel form that they would be willing to email me?

I found building my own formulas to be one of the most fun parts of the game.  

building formulas for what?

Excel formulas for evaluating recruits.  I thought you were asking for a spreadsheet that would automatically rank players for you.  
What excel form are you looking for?

Oh, I had only heard of people using excel sheets to archive their players' ratings before and after each season, etc. Wouldn't be hard to make, but I can't stand excel.

Your macro should look something like the following. With this macro, you would have to selection the first cell, and the macro would put the data in the column to the right. One problem you might face is that the potential colors might not match the color index value (different shade of green or red). Instead of Font.ColorIndex, you might be able to use Font.Color and do some color range in the IF statement.

Sub CalculateColor()
'
' CalculateColor Macro
'
   
Do Until Selection.Value = 0
If Selection.Font.ColorIndex = 4 Then
Selection.Offset(0, 1).Value = 25
ElseIf Selection.Font.ColorIndex = 1 Then
Selection.Offset(0, 1).Value = 10
ElseIf Selection.Font.ColorIndex = 3 Then
Selection.Offset(0, 1).Value = 0
Else
Selection.Offset(0, 1).Value = "Check color"
End If

Selection.Offset(1, 0).Select
Loop

End Sub

Posted by pjbrankin on 8/6/2010 1:25:00 AM (view original):
Oh, I had only heard of people using excel sheets to archive their players' ratings before and after each season, etc. Wouldn't be hard to make, but I can't stand excel.

excel came from the bosom of a goddess, man

Posted by theeyetest on 8/6/2010 10:26:00 AM (view original):

Your macro should look something like the following. With this macro, you would have to selection the first cell, and the macro would put the data in the column to the right. One problem you might face is that the potential colors might not match the color index value (different shade of green or red). Instead of Font.ColorIndex, you might be able to use Font.Color and do some color range in the IF statement.

Sub CalculateColor()
'
' CalculateColor Macro
'
   
Do Until Selection.Value = 0
If Selection.Font.ColorIndex = 4 Then
Selection.Offset(0, 1).Value = 25
ElseIf Selection.Font.ColorIndex = 1 Then
Selection.Offset(0, 1).Value = 10
ElseIf Selection.Font.ColorIndex = 3 Then
Selection.Offset(0, 1).Value = 0
Else
Selection.Offset(0, 1).Value = "Check color"
End If

Selection.Offset(1, 0).Select
Loop

End Sub

definitely giving that a try

Okay, this isn't the most elegantly coded macro, and the spreadsheet is unfinished. Plus, I've left a scoring system that I use to rank recruits on the sheet. Ignoring all of that, here is a modified version of the macro in action. You need to be using Excel 2007 (or more recent), and you need to enable macros. To get the data in, copy and paste from the Compare Recruits window of the Recruiting page. I did have a problem with the green and black, so I added a couple of exceptions to the If-Then statement. You may run into some more exceptions that will cause "Check color" to appear in one of the output cells. After you paste the data into the spreadsheet, just click the button at the top left of the sheet. If I have time, I'll try to post an update. Aside from needing to finish the sheet, I need to not hard code the +25, +10, 0. Also, I plan on incorporating the scoring calculation into the macro.

HD_PowerSheet - I posted this to Google Docs. You shouldn't have to login to access it.

Just found this in the forums and I don't know how to make macros and I would love it to make this process easier, any advice on how to implement a macro into my excel spreadsheet?

Function CalcAttr(SelectedCells As Range)
' this function will correct an attribute based on potential
' pass in only one cell at a time like this CalcAttr(A8)

Dim Cell As Object
Dim x As Double
x = 0
For Each Cell In SelectedCells
x = Cell.Value
If Cell.Font.ColorIndex = 1 Then
If x <= 5 Then
x = x + 3
Else
x = x + 10
End If
ElseIf Cell.Font.ColorIndex = 10 Then
If x <= 5 Then
x = x + 8
Else
x = x + 25
End If
Else
x = Cell.Value
End If
Next Cell

If x > 100 Then
x = 100
End If

CalcAttr = x

End Function



I use that function in a macro ... then I call it like this in the spreadsheet:


=IF(AND($B2<>"",$B2<>"Pos"),CalcAttr(D2),"")

you can sort by color (from http://office.microsoft.com/en-us/excel-help/sort-data-in-a-range-or-table-HP010073947.aspx):

Sort by cell color, font color, or icon

If you have manually or conditionally formatted a range of cells or table column, by cell color or font color, you can also sort by these colors. You can also sort by an icon set created through a conditional format.

1. Select a column of data in a range of cells, or make sure that the active cell is in a table column.
2. On the Data tab, in the Sort & Filter group, click Sort.

The Sort dialog box is displayed.

1. Under Column, in the Sort by box, select the column that you want to sort.
2. Under Sort On, select the type of sort. Do one of the following:
   • To sort by cell color, select Cell Color.
   • To sort by font color, select Font Color.
   • To sort by an icon set, select Cell Icon.
3. Under Order, click the arrow next to the button, and then, depending on the type of format, select a cell color, font color, or cell icon.
4. Under Order, select how you want to sort. Do one of the following:
   • To move the cell color, font color, or icon to the top or left, select On Top for a column sort, and On Left for a row sort.
   • To move the cell color, font color, or icon to the bottom or right, select On Bottom for a column sort, and On Right for a row sort.

 Note   There is no default cell color, font color, or icon sort order. You must define the order that you want for each sort operation.

1. To specify the next cell color, font color, or icon to sort by, click Add Level, and then repeat steps three through five.

Make sure that you select the same column in the Then by box and that you make the same selection under Order.

Keep repeating for each additional cell color, font color, or icon that you want included in the sort.

1. To reapply a sort after you change the data, click a cell in the range or table, and then on the Data tab, in the Sort & Filter group, click Reapply.

I took an Excel class at college last semester. I took my WIS worksheet and asked the professor:

Can I make a formula that  "increases all green numbers by 20 and all black numbers by 10?"

 To his knowledge, there wasn't a way to do that.

Posted by alblack56 on 9/13/2011 11:24:00 AM (view original):
I took an Excel class at college last semester. I took my WIS worksheet and asked the professor:

Can I make a formula that  "increases all green numbers by 20 and all black numbers by 10?"

 To his knowledge, there wasn't a way to do that.

The reason is you would have to use Macro's not forumla's... So technically it isn't Excel doing the work it would be Visual Basic.

Posted by alblack56 on 9/13/2011 11:24:00 AM (view original):
I took an Excel class at college last semester. I took my WIS worksheet and asked the professor:

Can I make a formula that  "increases all green numbers by 20 and all black numbers by 10?"

 To his knowledge, there wasn't a way to do that.

My VB function does just that ... it increases the value by 25 for green and 10 for black. (or by 8 if green and 3 if black of the initial value starts out less than 5)

Posted by zsap on 9/11/2011 7:21:00 PM (view original):
Just found this in the forums and I don't know how to make macros and I would love it to make this process easier, any advice on how to implement a macro into my excel spreadsheet?

There is a macro recorder built into Excel that will actually record your keystrokes for the simple stuff.  I use that to alter my recruits list according to potential (but haven't gone to the trouble of using potential as part of a ranking formula).

If you want to get more complicated than the macro recorder, you'll need to be able to use Excel's VBA language.  One of the real beauties of Excel these days (compared to when I first learned 1-2-3 back in the '80s) is that everything you need to know is available on the internet for free.  The number of tutorials out there is mind-boggling.