Search:

Help for a (Hypothetical) Gambler!

Asked By: Clifford    Date: Feb 07    Category: MS Office    Views: 1127

Just for fun, I compare ratings for different college football teams
and then compare those ratings to the odds for a given week to come up
with a strategy for (hypothetical) wagering. My spreadsheet has the
following columns: A) Favorite B) Favorite Numerical Rating C) Underdog
D) Underdog Numerical Rating E) Numerical Rating Difference F) Odds
G) Difference between Numerical Ratings and Odds.

What I do is bold each home team. I would like to create a macro that
would add 3 points to the numerical rating of each team that is
bolded. Any ideas? I am not a big macro creator but would like to
start learning. Also, any ideas on how to learn from the ground up?

Share:

To help  get you familiarized with Excel macros, try this:

Turn on the Visual Basic Toolbar
(right-click on toolbars, select Visual Basic)
record a macro (save in THIS workbook, change name if desired)
select a cell
change to Bold.
stop recording

go to VBA Editor (from toolbar)
you should have the Project Builder panel displayed.
Turn on the Debugger toolbar (right-click, select Debug)
find the Modules folder, open Module1
Sub Macro1()
' Macro1 Macro
' Macro recorded 11/2/2007 by
'
Range("B10").Select
Selection.Font.Bold = True
End Sub

change it to:
Sub Macro1()
' Macro1 Macro
' Macro recorded 11/2/2007 by
'
If (Range("B10").Font.Bold) Then
MsgBox "BOLD"
Else
MsgBox "Plain"
End If
End Sub

run the macro (from the toolbar)

Now, you can change this to loop through your data:

sub Macro1
dim R
for R = 2 to 1000
if (cells(R,1) <> "") then '(checks for non-blank rows)
if (cells(R,1).font.bold) then
cells(R,2) = cells(R,2) + 3
end if
end if
next R
end sub

Keep in mind that running this macro will add 3 EACH TIME it is RUN!

see?
wasn't that easy?

If you add 3 to a typed-in numerical rating, you risk losing the original
value (which will certainly happen) and also risk having the 3 added more
than once, or not removed properly if you got the original bolding wrong.

My preferred solution to this sort of thing is to:

(1) Put the adjustment in a calculated field - in this case, that's
presumably column E.

(2) Write a small VBA function that will return True if the font is bold.
Then do the rest in the formula.

So, the function is

Option Explicit

Public Function IsBold(ByRef Cell As Range) As Boolean
Call Application.Volatile
IsBold = Cell.Font.Bold
End Function

In its essentials, this simply returns True or False depending on whether
the font in the range passed to it is bold or not.

However, note that the act of bolding/unbolding a cell does not actually
result in the recalculation of anything. I have inserted the Volatile call
to ensure that the cell containing the function call will be recalculated
every time anything else is recalculated. This is not, of itself, enough to
get you a recalculation, but at least an F9 will now cause the calculation
to be redone.

How to use it?

Your basic formula for E38 is

=B38-D38

Now, exactly one of A38 or C38 will be bold, to indicate which is the home
team. (My assumption is that there is always a home team.) Therefore, you
can assume the underdog is the home team if the favourite is not bold.

=B38-D38+IF(IsBold(A38),3,-3)

So add 3 if the favourite is the home team and subtract 3 if it is not.

If neutral games are possible, then

=B38-D38+IsBold(A38)*3-IsBold(C38)*3

relies on the fact that True can be used as 1 and False as 0 in a numeric
calculation.

Didn't find what you were looking for? Find more on Help for a (Hypothetical) Gambler! Or get search suggestion and latest updates.