Skip to main content

Excel Functions

Below is the complete list of Excel functions.

To ensure these functions are always available when starting Excel, save the Book.xlt template file in the XLStart directory specific to your version of Excel (e.g. “C:Program FilesMicrosoft OfficeOFFICE11XLSTART” for Excel 2003). If a file already exists, do not overwrite it unless you are certain it is empty; instead, choose a different name. Alternatively, you can manually add the Excel VBA functions or module to your existing Book.xlt file if you are familiar with the process.

To restrict access to these functions to only by request, save the file under a different name in the XLStart directory. For instance, if you save the file as Ganchrow.xlt, you can then select the template “Ganchrow” when clicking “New” on the “File” menu to access all of the functions mentioned above.

Template:Download

Featured Underscoregs

Caesars logo Caesars logo Bet Now
4.7/5
4.7/5

The first bet on Caesars is $1,000.

FanDuel logo FanDuel logo Bet Now
4.8/5
4.8/5

Place a $5 bet and receive $200 in bonus bets if you win.

bet365 logo bet365 logo Bet Now
4.6/5
4.6/5

Wager $5 and receive $150 in bonus bets.

Fanatics Underscoreg logo Fanatics Underscoreg logo Bet Now
4.4/5
4.4/5

Receive up to $1,000 in Bonus Bets

Individual Functions

SBRVer()

Shows the current version number of the template.

US2Dec(USOdds) (usage 1)

Converts US-style odds to decimal format. For example, converting -110 US odds would result in 1.909090909 in decimal format.

US2Dec(range of USOdds) (usage 2)

This function converts an array or Excel range of US-style odds to decimal parlay odds. For example, when inputting US2Dec(-110, -110), the output would be 3.644628099.

US2Par(range of USOdds)

This function converts an array or Excel range of US-style odds to US-style true parlay odds. For example, US2Par(-110,-110,-110) equals +595.7926.

Dec2US(DecimalOdds)

The function US2Dec converts decimal odds to US format. For example, US2Dec(1.909090909) equals -110.

US2Win(USOdds, WagerQuantity {default = 1})

Converts US or Decimal odds and wager size to potential win amount. Note that by using a default wager size of 1, these functions can also convert from US/decimal odds to fractional odds. For example, US2Win(-120, 120) = 100, or US2Win(-110) = 0.90909.

Dec2Win(DecOdds, WagerQuantity {default = 1})

These functions convert US or Decimal odds and wager size to the potential win amount. When using a default wager size of 1, these functions effectively convert from US/decimal odds to fractional odds. For example, US2Win(-120,120) would result in a potential win of 100, while US2Win(-110) would equal 0.90909.

US2Res(USOdds, WagerQuantity {default = 1}, Result)

This function converts US or Decimal odds, wager size, and result into a corresponding outcome (WIN, LOSS, or PUSH). For example, US2Res(-120, 120, “P”) = 0, or US2Res(-110, 200, “Win”) = 181.82.

Dec2Win(DecOdds, WagerQuantity {default = 1}, Result)

This function converts US or Decimal odds, bet size, and outcome (where “WIN”, “W”, or “1” represents a win; “LOSS”, “L”, or -1 represents a loss; and “PUSH”, “P”, or 0 represents a push). For example, US2Res(-120, 120, “P”) = 0, or US2Res(-110, 200, “Win”) = 181.82.

US2Prob(USOdds)

This tool can convert odds from US or decimal format to probability. For example, converting US +100 odds to probability is the same as converting decimal 2.0000 odds to probability, both of which equal a 50% probability.

Dec2Prob(DecimalOdds)

Changing from US or decimal odds to probability. For instance, converting US2Prob(+100) is equivalent to Dec2Prob(2.0000) which equals a 50% probability.

US2Hold(range of US Odds)

The theoretical hold is calculated based on an Excel range of US or decimal odds. For example, if cells A1 and A2 both have odds of -110, the US2Hold(A1:A2) would be 4.54545%.

Dec2Hold(range of Decimal Odds)

This function determines the theoretical hold percentage based on a range of US or decimal odds in an Excel spreadsheet. For instance, if cells A1 and A2 both contain odds of -110, the function US2Hold(A1:A2) will calculate a theoretical hold of 4.54545%.

{US2Real(range of US Odds)}

The array function US2Real returns an array of zero-vig probabilities calculated from a range of US or decimal odds in Excel. For example, if cells A1 and A2 both contain -110 odds, applying the array formula {=US2Real(A1:A2)} to cells B1, B2, and B3 would result in B1 and B2 showing a 50% probability, while B3 would display the theoretical hold at 4.54545%.

{Dec2Real(range of Decimal Odds)}

This array function calculates the zero-vig probabilities for an Excel range of US or decimal odds. For example, if cells A1 and A2 both have odds of -110, applying the formula {=US2Real(A1:A2)} to cells B1, B2, and B3 would result in B1 and B2 displaying 50%, while B3 would show the theoretical hold of 4.54545%.

{US2Fair(range of US Odds)}

The array function US2Fair returns an array of zero-vig fair value odds based on an Excel range of US or decimal odds. For example, if cells A1 and A2 contain -200 and +176, and cells B1 and B2 are set to the array formula {=US2Fair(A1:A2)}, B1 and B2 will display -184 and +184 respectively.

{Dec2Fair(range of Decimal Odds)}

The array function returns a set of zero-vig odds that are fair value based on an Excel range of US or decimal odds. For instance, if cells A1 and A2 contain -200 and +176, and the array formula {=US2Fair(A1:A2)} is applied to cells B1 and B2, they will display -184 and +184 respectively.

ProbUS2Edge(Probability, USOdds)

Edge is determined by calculating the difference between the win probability and the US or decimal odds. For instance, if the win probability is 55% and the US odds are -110, the edge would be 5%.

ProbDec2Edge(Probability, DecimalOdds)

This function determines the edge using win probability and US or decimal odds. For instance, the calculation ProbUS2Edge(55%,-110) equals 5%.

EdgeUS2Prob(Edge, USOdds)

This function determines the likelihood of winning by taking into account the advantage and either US or decimal odds. For instance, ProbUS2Edge(5%,-110) = 55%.

EdgeDec2Prob(Edge, DecimalOdds)

Calculates the probability of winning based on the advantage and US or decimal odds. For instance, if the edge is 5% and the odds are -110, the probability of winning would be 55%.

ProbEdge2US(Probability, Edge)

This function ProbEdge2US calculates US or decimal odds by taking into account the probability and edge. For example, ProbEdge2US(55%,5%) would result in -110.

ProbEdge2Dec(Probability, Edge)

This function calculates US or decimal odds by factoring in the probability and edge. For instance, ProbEdge2US(55%,5%) is equal to -110.

USRisk2Win(USOdds, RiskQuantity {default=1})

Calculate the amount won based on US/Decimal odds and the amount risked. These functions can also be referred to as USR2W(·) and DecR2W(·). For example, USRisk2Win(-110,22) = USR2W(-110,22) = $20.

DecRisk2Win(DecimalOdds, RiskQuantity {default=1})

The function USRisk2Win calculates the resulting win amount based on US/Decimal odds and the risk amount. It can also be referred to as USR2W or DecR2W. For example, USRisk2Win(-110,22) = USR2W(-110,22) = $20.

USWin2Risk(USOdds, WinQuantity {default=1})

Calculate the necessary risk based on US/Decimal odds and the desired risk amount. These functions can also be referred to as USW2R(·) and DecW2R(·). For example, USWin2Risk(-110,20) = USW2R(-110,20) = $22.

DecWin2Risk(DecimalOdds, WinQuantity {default=1})

Calculate the necessary risk based on US/Decimal odds and the desired risk amount. These functions can also be referred to as USW2R(·) and DecW2R(·). For example, USWin2Risk(-110,20) = USW2R(-110,20) = $22.

Exch2US(US Exchange Odds, Commission {default = 2%})

Determine the equivalent sportsbook odds in US or decimal format based on exchange odds and commission. For example, Exch2US(-110, 1%) would indicate the sportsbook equivalent odds for a -110 bet with a 1% commission, approximately -111.11.

Exch2Dec(Decimal Exchange Odds, Commission {default = 2%})

Find the sportsbook equivalent odds in US or decimal format by using the exchange odds and commission. For example, Exch2US(-110, 1%) means finding the equivalent odds of -110 with a 1% commission, resulting in approximately -111.11.

E2S(US exchange odds, exchange commission {default = 2%})

Exch2US shortcut (US Exchange Odds, Commission).

ExchUS2Hold(range of US Odds, Commission)

Theoretical hold, taking into account sports betting exchange commissions, is calculated using an Excel range of US or decimal odds. For instance, if cells A1 and A2 both have a value of -102, the formula ExchUS2Holds(A1:A2,2%) would determine the theoretical hold on the -102/-012 market, factoring in a 2% exchange commission (resulting in a value of 1.961%).

ExchDec2Hold(range of Decimal Odds, Commission)

This function, ExchUS2Holds, calculates the theoretical hold for a sports betting exchange, taking into account commissions and using a range of odds in either US or decimal format. For instance, if cells A1 and A2 both have odds of -102, running the function ExchUS2Holds(A1:A2,2%) would give the theoretical hold for the -102/-012 market with a 2% exchange commission included (resulting in a hold of 1.961%).

KUtil(bankroll, Kelly multiplier {default = 1})

Calculate the Kelly criterion utility based on the given bankroll percentage and Kelly multiplier. For example, a KUtil(1.05, 0.5) would result in half-Kelly utility for a bankroll that is 105% of the initial amount.

InvKUtil(utilily, Kelly multiplier {default = 1})

The inverse Kelly Utility function determines the bankroll percentage associated with a specific Kelly criterion utility and Kelly multiplier. For instance, InvKutil(KUtil(X, KellyMult),KellyMult) would be equal to X, as long as X is greater than 0.

SBKelly(Probability, Odds, Kelly Multiplier {default = 1}, Decimal Odds Flag {default = FALSE})

This function calculates the single bet Kelly stake based on the expected win probability, payout odds, and an optional Kelly Multiplier. If the “Decimal Odds Flag” is not set or is set to FALSE, the function will make an educated guess as to whether the odds specified are in US or decimal style (assuming decimal if the absolute value is less than 100). Setting the flag to TRUE will always assume decimal-style odds, which can be useful when using decimal-style odds with very high payout levels.

SBKelly(Probability, Odds, Kelly Multiplier {default = 1}, Decimal Odds Flag {default = FALSE})

This function calculates the Kelly stake for a single bet based on the expected win probability, payout odds, and optional Kelly Multiplier. If the “Decimal Odds Flag” is not set or is set to FALSE, the function will make an educated guess as to whether the odds specified are in US or decimal style (if the absolute value is less than 100, it will assume decimal). Setting the flag to TRUE will always make the function assume decimal-style odds (which can be useful when using decimal-style odds with very high payout levels).

{P2L(range of win probabilities)}

This function returns an array of probabilities for different win-loss outcomes based on the input probabilities. Each element in the output array corresponds to the likelihood of a specific win-loss combination. For example, if the input probabilities are 75%, 70%, and 65%, the output array would show probabilities for different win-loss scenarios. Please note that this function may take longer to calculate for larger input sets.

{EnumCombin(range of items, size)}

The EnumCombin function returns a 2-D array containing all possible combinations of a specified size from the input range. For example, if the input range is (“A”, “B”, “C”, “D”, “E”), calling {=ENUMCOMBIN(A1:A5, 2)} would generate a 6-row, 2-column array with combinations like (“A”, “B”) and (“C”, “D”). Please note that this function may take longer to calculate for larger input sets due to the number of combinations being generated.

lg(p)

Find the logit function of a probability p, denoted as lg(p), by calculating ln(p) – ln(1-p) when p is between 0 and 1.

invlg(x)

The inverse logit function of x, denoted as invlg(x), is given by the formula invlg(x) = Exp(x) / (1 + Exp(x)).

MB2US(US Matchbook Exchange Odds, Commission {default=1%})

Determine the sportsbook equivalent US or Decimal odds based on Matchbook exchange odds and commission. The commission structure involves paying a percentage of the lesser of risk or win, regardless of the bet outcome. For example, MB2US(-110, 1%) indicates the sportsbook equivalent odds for a bet at -110 with a 1% Matchbook commission, approximately -112.12.

MB2DEC(Decimal Matchbook Exchange Odds, Commission {default=1%})

Find the sportsbook equivalent odds in US or Decimal format based on Matchbook exchange odds and commission. This commission structure involves the player paying a set percentage of the lower of risk or win regardless of the outcome of the bet. For example, MB2US(-110, 1%) would indicate the sportsbook equivalent odds of placing a bet at -110 with a 1% Matchbook commission (approximately -112.12).

{Bets2Stats(range of Odds, range of Wager Quantities {default=1}, range of Outcomes, range of Edges {default=0%), Decimal Odds Flag = {default = FALSE})}

Function that accepts a range of betting odds (US odds by default, but can also handle decimal odds if the Decimal Odds Flag argument is set to TRUE), an optional range of wager amounts (assumes 1 unit per wager if not provided), a range of outcomes (1 or a string starting with ‘W’ for a win, -1 or a string starting with ‘L’ for a loss, any other value for a push/no action), and a range of expected edges (defaulting to 0). The function returns an array with the following values:

  1. Number of Non-Pushed Bets
  2. Number of Wins
  3. Win %
  4. Unit Return
  5. % Return
  6. Unit Standard Deviation
  7. % Standard Deviation
  8. Standard Score
  9. p-value (from t-distribution)

To ensure that these functions are always available when you open Excel, save the Book.xlt template file in your Excel XLStart directory. The default directories are: “C:Program FilesMicrosoft OfficeOFFICE11XLSTART” for Excel 2003, Office12XLSTART for Excel 2007, Office10XLSTART for Excel 2002, and OfficeXLSTART for Excel 2000 and 97. If the file already exists, do not overwrite it unless you are certain it is empty. Instead, save the file with a different name. Alternatively, you can manually add the Excel VBA functions or module to your existing Book.xlt file if you are familiar with the process.

If you want these functions to only be available only by request then save the file under a different name in the XLStart directory. For example, if you saved the file as Ganchrow.xlt, then by clicking ”New” on the ”File” menu, you’d be able to select the template ”Ganchrow” and have all the above functions available.