# Horse Racing / Betting Formulas

Aug 3, 2007

I've made my own spreadsheet to record my racing bets. Everything works great, however I want to expand it to become more complex.

There are 2 formulas I'm having trouble creating:

1...the formula for dead-heat finishes including the Rule 4 deductions (2,3 & 4 way dead-hets)
2...the formula for each-way bets including the Rule 4 deductions (the win part & the placed part)

I guess only people with knowledge of horse racing / betting will be able to help me on this.

## Summary Of Horse Racing Statistics

May 6, 2009

i need to do the following on the attached spreadsheet using a macro: SHEET 1. This is an example of the original data i will be working with i need

1) You will notice that the Min and Max columns are not all together, i need these to all be together. I then only want the Race course, race time, date and type, Min, Max and ratings.

2) I then want the following formula added to Column F, Max minus Min. In column G i would like the following formula Rating minus Min. And finally in Column H Rating minus Max.

3) I would the like to Highlight in Yellow the horse with the highest possible number. I want this done for each column (F,G,H) in each race i would like all Horses that dont have a yellow cell in any of the Columns (F,G,H) to be deleted, leaving me with just the highlighted horses. An example of the required final result is contained in Sheet 2 of the attached spreadsheet.

## Horse Racing Odd's Line Formula

Oct 7, 2006

I have odds line numbers on this excel sheet which is the VL column. I'd like to be able to scratch a horse, (delete a horse from the sheet) and the odds line (VL column) will update to the new value after the horses is scratched from this sheet.

Now I do this in a program that uses VBA and transports this info to Access, where then I export to Excel to this sheet.

Here's the formula I use for the odds line...

#EQUAL(#COLVAL(51),1, .00001)*((#COLVAL(53))-(#COLVAL(53)*.4))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),2, .00001)*((#COLVAL(53))-(#COLVAL(53)*.25))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),3, .00001)*((#COLVAL(53))-(#COLVAL(53)*.10))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),4, .00001)*((#COLVAL(53))+(#COLVAL(53)*.05))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),5, .00001)*((#COLVAL(53))+(#COLVAL(53)*.10))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),6, .00001)*((#COLVAL(53))+(#COLVAL(53)*.25))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),7, .00001)*((#COLVAL(53))+(#COLVAL(53)*.40))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),8, .00001)*((#COLVAL(53))+(#COLVAL(53)))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),9, .00001)*((#COLVAL(53))+(#COLVAL(53)*1.3))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),10, .00001)*((#COLVAL(53))+(#COLVAL(53)*1.6))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),11, .00001)*((#COLVAL(53))+(#COLVAL(53)*1.9))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),12, .00001)*((#COLVAL(53))+(#COLVAL(53)*2.2))/(#COLVAL(50))+
#EQUAL(#COLVAL(51),13, .00001)*((#COLVAL(53))+(#COLVAL(53)*2.7))/(#COLVAL(50))

I added a couple of columns in the excel sheet to occomadate this formula which should work if coded into excel properly. Notice the (50), (51), (53) numbers.

(50) = TC in this excel sheet
(51) = 2V in this excel sheet
(53) = TCa in this excel sheet
VL column is where this formula updates to.

I have one other minor problem with something else, but so not to confuse, I'll mention it later.

## Charting Live Horse Racing Odds

Sep 28, 2009

I have a line graph which I use in conjunction with live horseracing odds. On the vertical axis are the odds which are sourced from a worksheet. Each horse is represented by a data series that moves along the horizontal axis. The refresh rate is determined by the user embedded in the code.

I would like to expand the length of each data change along the horizontal axis as the data can be volatile. Sometimes there are rapid changes on the vertical axis which makes it difficult to interpret. With each refresh the line data moves about 3mm. It would be much better if it were about 5mm, especially as I now use a widescreen display.

There are no values on the horizontal axis as such, only time is suppose set by Excel?

## Counting Macro Based On 2 Criteria (Horse-racing Spreadsheet)

Jun 21, 2006

Horses have 5 running styles; early speed to late closers; denoted as 1 to 5 in column Z. I'd like to count those noted as 1 thru 3 for each race.

In column FC (the last column with data) I have a race ID # for each race on the spreadsheet, somewhere between 2500 to 5000 races in each one. They're a monthly record. The race ID # is a concatenation of columns B (Track ID), C ( Date) & D (race #). Each is unique to its race. E.G. Aqueduct, jan 1st, 2004, 2nd race is id'd as AQU379872.

Would it be possible to create a macro that would (A) count the number of horse 1s, horse 2s & horse 3s from a specific race ID & then post those numbers in columns FD thru FF & (B) then continue to the next race ID in column FC & count those horses from that race & so on thru the entire spreadsheet?

Would it be simpler if each race ID were changed to a number--race 1 down to race 2500?

## Betting Formula

Jun 15, 2009

I'm trying to create a spreadsheet for placing two bets - one at a bookie and one at an exchange which leave equal profit or loss.

Basiclaly - I need to fill a cell with a number which will cause my two profits at the bookies and exchange to be as close to equal as possible.

Here is an example.

Place �10 at Ladbrokes on Man U to win at odds of 2.0 meaning either �10 loss or �10 win
At Betfair I need to place a lay bet of ?? in order to cover the 5% commission and leave me with as nearer profit or loss to the bookies.

i.e if I placed a lay bet of �10 at Betfair if the bookies won I would end �0 profit/loss while if Betfair won I would end up -50p due to the comm, I need a formula which will give the lay figure to ensure both these numbers were as close as possible - in this case a lay of �10.26 would leave me with a -25p at both bookies - but how do I get the spreadsheet to work out this �10.26 figure for me?

## Ranking Horse Sale

Oct 8, 2007

I'm making a spreadsheet to record the results of a horse sale, and I'm trying to make a summary sheet to list various statistics such as the 10 top sellers, 10 worst sellers, etc.

Now, I know I can get the 10 top prices easily by using Large(<Range>, 1...10) in my cells but I can't figure out a way to properly determine the hip # and name. Everything works fine when there's only 1 entry at price n, but when there are multiple I run into problems.

EX:

Horse 1 sold for \$5500
Horse 2 sold for \$8500
Horse 3 sold for \$5500
Horse 4 sold for \$6000

So, my summary should show
Horse 2 \$8500
Horse 4 \$6000
Horse 1 \$5500
Horse 3 \$5500

## Ranking Racing Sheet

Nov 7, 2007

First off, can someone guide me to a better formula to E2:E14 or E21 if used later without the array? Other folks will use this workbook. Next, is there a way to do that without using columns F2 or G2. There are numbers below 0 to take into account that need ranked. The whole idea is to rank them as to whatever #'s are closest to 4.7000. I eventually want it to match the name in column- A2 to the appropriate ranking. 0.0000 is #1 and the highest abs would be #13-(1.0098) in the sheet. If anything in A2:A21 is added or removed I'd like the entire row to remain "blank".

************************************************************************>Microsoft Excel - newstart.xls___Running: xl97 : OS = Windows ME (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD2E2F2G2H2D3E3F3G3H3D4E4F4G4H4D5E5F5G5H5D6E6F6G6H6D7E7F7G7H7D8E8F8G8H8D9E9F9G9H9D10E10F10G10H10D11E11F11G11H11D12E12F12G12H12D13E13F13G13H13D14E14F14G14H14=
ABCDEFGHI1Name / Car #1st Pass2nd PassBest Run4.70000 Name2Bartlett 5254.65694.55594.6569-0.04310.00000.00001Moody3R. Stringer 41334.72264.58994.5899-0.11010.00010.00012S. Stringer4Norman 4894.73474.68924.6892-0.0108-0.00030.00033Powell5M. Jones 46594.73534.69934.6993-0.00070.00040.00044A. Mears6Powell 4134.73944.69974.6997-0.00030.00050.00055K. Rierson7Penner 42914.78304.99034.78300.0830-0.00070.00076M. Jones8Moody 49074.81024.70004.70000.0000-0.01080.01087Norman9A. Mears F4094.92884.70044.70040.00040.01180.01188D. David10K. Rierson 450R5.57334.70054.70050.0005-0.04310.04319Bartlett11Muse 47995.84025.70215.70211.00210.08300.083010Penner12LH Newlin 44425.84525.70985.70981.0098-0.11010.110111R. Stringer13D. David 48386.42984.71184.71180.01181.00211.002112M. Muse14S. Stringer 4229.20154.70014.70010.00011.00981.009813LH Newlin15 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

## VBA To Compare Racing Times

Dec 30, 2008

I have a spreadsheet of about many rows and 5 cols shown in the attached. I show 3 races..each will have a different time to the previous race. I want to use the rsq function to compare the data for that race in col c with that in col d and produce the answer for the 1st race in each row in col e for that race..then proceed to do the same for race 2,then race 3 etc...

I need a formula in e2 I can drag down...each cell in col e should be filled with the answer for that race..I have thousands of races..

## Using Cell References In File Paths For Formulas To Create Dynamic Formulas

Dec 3, 2013

I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:

=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B\$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B\$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B\$292

What I want to do is extract the file path from the above formula and make it a composite of several cell references.

So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:

_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH

I tried several versions, I am hoping for something like this:

=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B\$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B\$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B\$292

## Converting Formulas To Relative/absolute References With Formulas Referencing Other Sheets

Dec 15, 2008

I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.

## Formulas To Hide Partial Concatenate Data And Determining Two Other Formulas

Dec 11, 2013

I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.

I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?

## Excel 2007 :: Formulas In Cells Not Being Recognized As Formulas?

Jan 10, 2013

I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.

## Paste Formulas As Values (strip Out Unwanted Formulas)

May 13, 2008

I have a macro running this code to strip out unwanted formulas and formatting.

Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False

Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2

'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If

Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................

A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.

## Hide Formulas From Formula Bar While Still Having Formulas Active?

Jan 16, 2014

Is it possible to hide formulas from the formula bar while still having the formulas active?

## Using Two IF Formulas (3 Or More To Count If Other IF Formulas Are Actually Returning A Value)

Aug 24, 2009

I have a spreadhseet with various functions on it and what I am trying to do is this.

Cell E4 returns a >35 or <35 true or false value
Cell G4 is either blank or has "Yes" text type into it.

What I am trying to do is get cell F4 to return certain arguments.

E4 = >35 and G4 is blank I want it to state "Email Hiring Manager"
E4 = ,35 and G4 is blank I want it to state "Wait"

I have a basic IF formula that returns this
=IF(E4>35,"Email Hiring Manager","Wait")

Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of
=IF(G4="Yes","Email Agency","Email Hiring Manager")

If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency"

Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?

## Convert Formulas In Text Into Formulas

Dec 12, 2007

If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?

## Formulas Won't Calculate. Just Displaying As Formulas

Dec 11, 2008

I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.

## VBA Formulas Without \$

Mar 6, 2014

I need to have variable formulas created without the \$ so I can copy and paste the cells.

How to do this with vba determined range.

## If Formulas In VBA

Oct 16, 2008

I have a sheet of data which is refreshed eash day, the data has frequencies and values in it. I need the code to say:

if column E:E = Monthly, and column M:M = Annually then divide the value in column N:N by 12

If column E:E = Monthly, and column M:M = Quarterly then divide value in column N:N by 4

We have these four freqencies:

Monthly
Quarterly
Half Yearly
Annually

and the above code will need apply to all scenarios i.e. if E:E = Quarterly and M:M = Monthly then x N:N by 3.

E:E being the origonal frequency and M:M being the new one, we need to know the value of the new gift at the old frequency.

## Sum A Row With Formulas?

Jan 29, 2013

I have a Row of numbers ( 1 or blank) generated by the formula =IF(AD6>30,"1"," ")

I want to add this row to record how many 1's there are (This is a weather record showing how many Gale days there have been)

Using the usual Sum formula does not add the 1's up.

## Add Row With Formulas

Jul 23, 2008

I've got a generic question here about adding a row with formulas above the subtotal line.

In the table below I have some simple rows of sums and a subtotal row at the bottom.

If a macro is run how can I insert a new row with the same two formulas in the row one above the Subtotal row....

## Tax IF Formulas

Oct 26, 2009

I need help figuring out an IF formula that would allow me to calculate the tax owed. The tax rates are 20%, 25% and 30% and the full bracket total for 20% is 4,000\$ and for 25%, 11,500\$.

In D14, I have as a taxable income, 20,000\$ and In E14, I would need a IF formula that calculates that... but I would need to copy only one formula down the E column to be used on varying taxable incomes...

## Formulas Are Not A Value

Apr 27, 2006

Do
If ActiveCell.Value Is Value Then
ActiveCell.Select
Else
ActiveCell. Offset(1, 0).Select
End If
Loop Until ActiveCell.Value Is Value

For some reason when you have a formula in a cell but no data, it says its greater than zero...but because there is no data in that cell, but only a formula, is there anyway to get this code to work.

## Add New Row With Formulas

Mar 8, 2007

I am working on a sheet that will have a large range of rows used. There is formulas within a few cells in each row specific to that row. When the user enters data into colum A of the last empty row would there be a way to insert two new rows below that row with formatting and formulas? The toughest part for me has been keeping the totals at the bottom updated. I attached the sheet to help explain if I haven't done a very good job at explaining it.

## How To Combine Three Formulas

Mar 29, 2014

Ok, These are the three formula's. How do I combine them or can I.

=IF((B2="GOV"),[@[Gal Billed]]/1000*1.5)

=IF((B2="NPROFIT"),[@[Gal Billed]]/1000*2,0)

=IF((B2="COM"),[@[Gal Billed]]/1000*3)

## Showing 0 For The Min And Max Formulas

Apr 27, 2014

I am currently having an issue with the formula below showing a zero when there is no data, now i am referencing from this cell to another sheet but even with the IF(ISBLANK inside the formula on the other page it still shows a zero which is messing up what i am trying to do on another page.

This is what i am using

=(MAX(P5:P47,P60:P102,P115:P157,P170:P212,P225:P267,P280:P322,P335:P377,P391:P433,P447:P489,
P503:P545,P559:P601,P615:P657,P671:P713,P727:P769,P783:P825,P839:P881,P895:P936)

This is the cell number that has this formula which it is telling me the max number for the column - P938

I am referencing to another sheet like this =IF(ISBLANK('sheet 2'!P938),"",'sheet 2'!P938)

How i get this to stop showing a 0 if there are no data for it to search for the max?

## Use Range In Many Formulas

May 30, 2014

I'd like a user to be able to input a range in a cell and then in the columns next to it other formulas to pick it up. i.e.,

Column A Column B Column C

(1:12) =Sum(Zx:Zx) =Average(Zx:Zx)

when x:x = (1:12)

Not sure if its possible any clever ways around this?

## How To Get Two Formulas In One Cell

Jan 21, 2014

I am trying to get two formulas to work in one cell?

=E2/(1-40%)

and

=If(CEILING(E2,0.05) = CEILING(E2, 0.1), CEILING(E2, 0.05)-0.01, CEILING(E2, 0.05))

## Merging Two IF Formulas

Mar 7, 2014

I am merging the following two IF formulas together.