Useful Spreadsheets

[The Young Fella]

Hi guys,

My dad is an accountant and he would be ashamed of my lack of MS Excel skill. I know this is quite lazy as I could probably create my own with a little work, but could anyone point me towards some useful ready-made spreadsheets? I’m looking to create my own tissues and would find it handy to have a template that instantly converts between decimal odds and percentages, showing me my total overround as I work.

Any help appreciated :)

[Marginal Value]

Method A. If you always have less than 16 runners, and can remember to subtract 1 off each of the decimal odds input (that’s tedious and prone to error) then this will be OK:

http://www.sportsbettingindex.com/odds-calculator.html

Method B. If you want up to 40 runners, and do it youself, it is quite quick to do in Excel.

I have a spreadsheet that does what you want, but I use LibreOffice Calc. I haven’t used Excel for about 10 years; my tight-fisted nature draws me towards free software that does the job I want. Your version of Excel may not load a LibreOffice spreadsheet so I will try to explain in text.

It is not complicated at all. There is only one formula and one sum.

I have had a quick look on the internet at Excel commands, and this is what you do. You will know a lot of this already, but I do not know what you do not know, so it is simplistic, I hope.

Start a new blank spreadsheet.
In cell A2 type the text: Number of Runners
In cell B2 type: =COUNTA(A7:A46)
Note – the COUNTA function will count the number of non-blank cells in a range, the range you want is A7 to A46.
In cell D2 type the text: Maximum 40 runners allowed in Rows 7 to 46
In cell A4 type the text: Total PerCent
In cell B4 type: =SUM(C7:C46)
In cell A6 type the text: Horse ID
In cell B6 type the text: Decimal Odds
In cell C6 type the text: PerCent
Note – you may want to make those headings bold and underlined, if you think it’s worth it.

In cells A7 to A 15 type the text: Happy Punter, Busted Flush, Silly Bet, Winning Script, Macro Writer, Smart Tools, Mini Chance, Major Gaff, Running Fast (or whatever!)

In cells B7 to B15 type the numbers: 2.88, 4.70, 5.90, 6.40, 20.00, 28.00, 21.00, 140.00, 450.00

In cell C7 type: =IF(B7<>””,100/B7,””) There are no spaces in this formula.
Note – The IF function has three bits separated by commas; if the first bit is True then do the second bit; if it is False then do the third bit. In this case it means IF cell B7 is not equal to nothing, then calculate 100 divided by the number in B7 and put the answer in cell C7, IF cell B7 is empty then put nothing in cell C7.

Put the cursor in cell C7. Click the Right mouse button and click Copy in thr pop-up menu, (the same copy command can be achieved by pressing the Ctrl & C keys together)

Put the cursor in cell C8. Hold down the Left mouse button and drag the cursor down to cell C46. Release the mouse button. Cells C8 to C46 should now be highlighted. With the cursor in the highlighted zone, click the Right mouse button and Click on Paste in the pop-up menu, (or press Ctrl and V keys to achieve the same effect. The formula in C7 will be copied to the other rows with Row number being appropriately changed.

Cell C7 to C15 should now contain: 34.72, 21.28, 16.95, 15.63, 5.00, 3.57, 4.76, 0.71, 0.22

Cell B4 should now contain: 102.84 (so the odds for the field are 2.84% overround.)

Cells A16 to C46 should be empty

That’s it. I’ll leave you to figure out how to calculate the OverRound per runner, and how to “tidy up” the spreadsheet with appropriate column widths, presenting the numbers to two decimal places, etc.

SAVE THE FILE !! Make a backup copy!

I have checked my spreadsheet against the website quoted above and they give the same answers, as long as you remember to subtract 1 from the website decimal odds.

[The Young Fella]

Brilliant – thank you very much MV.

[Author]

Posts