# 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.

View 9 Replies
ADVERTISEMENT
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.

View 9 Replies
View Related
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.

View 2 Replies
View Related
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?

View 9 Replies
View Related
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?

View 9 Replies
View Related
Apr 30, 2013

I have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.

I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.

I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.

View 2 Replies
View Related
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

View 9 Replies
View Related
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

View 12 Replies
View Related
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..

View 4 Replies
View Related
Jan 14, 2009

I'm looking for a formula that can sum line 3 to X date. The dates are in row 1 starting in column A. So if i put in cell Z3 the date of 2009.Aug then I need it to sum from A3 to H3.

View 4 Replies
View Related
Aug 5, 2008

1. I use a number of x,y pairs

2. From x,y pairs I produce "scatter" type chart

3. From the chart I get trend line and its equation e.g. 5-order polinominal

4. I convert equation into excel formula

5. I apply formula onto the same range of x argument and produce graph

6. The graph is very much different from the trend line!!! I would expect exact match as I use equation/formula for the trend line.

View 9 Replies
View Related
Mar 16, 2007

I have the code below in a userform. I would like to add a line that will insert this formula in column B everytime a new row is inserted....

View 8 Replies
View Related
Jul 27, 2014

I have a two different formulas the return a numbered result(PO Number) in the same column. I then vlookup both of them with the same formula into a pivot table, one returns the result one doesn't. format appears to be the same.

View 6 Replies
View Related
Jan 29, 2010

I like to write multi-line formulas like this:

View 3 Replies
View Related
Dec 9, 2008

I have a problem with an array formula. The file is attached for better comprehension.

I've built a multi-criteria lookup based on INDEX and MATCH.

The idea is in Sheet 1, in column F to put 1 (one) if each record of columns A,B,C and D (all combined) match any record in Sheet DATA. If not then 0 (zero).

The array formula works nice for each of the records. However, I get a ZERO in Zinc! If you check DATA, you can see that Zinc is there, from line 221 to 224!

I can't understand why this happens since i've pasted the formula for each of the lines in column F.

View 3 Replies
View Related
Apr 5, 2007

I'm trying to create some graphs with broken lines from lookup formulae, essentially what is explained at http://www.ozgrid.com/Excel/broken-line.htm. My problem, however, is that my charts are rather more complicated than those outlined, having multiple series that move across the page and overlap a lot of the time. This prevents the suggestion there from being an elegant or even sufficient solution. Does anyone know of a better way of doing this that might work for my situation?

View 9 Replies
View Related
Jan 25, 2010

I'm using the formula '=LEFT(A2,4)+0 to shorten a numbered line of text.Is there a way to adopt that formula to do the same thing to an alphanumeric line of text (i.e. 80A0267AA1 to read 80A0267) or even better substitute a "-" in place of the "A" (i.e. 80A0267AA1 to read 80-0267)? I know this is really picky, but I figure it's worth a shot.

View 10 Replies
View Related
Dec 12, 2008

I would like to use the following line to calculate the formula,

In the format i have already specified.

But to leave the values in the cells apposed to the formula.

This way when i delete the source sheets the results will still be left as values not formulas.

It can probably be done in 3 lines but i have to do this for 9 different rows so was wondering if there is a way of combining the three tasks into one row

View 14 Replies
View Related
Jul 29, 2009

I have text with line breaks in a cell for example:

A1 =

Multiple

App1

App2

App3

App4

App5

When I reference this cell for example in B1 (=A1) the line break list merges together and adds a square (LBC) where the line break is meant to be as follows:

Multiple LBC App1 LBC App2 LBC App3 LBC

Is there a formula that can look at cell A1 and split the lines out so that both A1 and B1 look identical not merged together?

View 9 Replies
View Related
May 28, 2014

I am trying to create a dynamic Excel line chart.

All is well, except for my x-axis (which is a text axis). It currently looks like this: Dynamic axis.jpg

I am using the Offset formula. The formula for my x-axis is =OFFSET(Worksheet1!$D$4,0,-3,COUNT(Worksheet1!$D$4:$D$36)). This is to show the axis if cells in Column D are filled up appropriately.

However, in the cells containing the axis name itself, this formula is used =IF(D9=0,NA(),'Worksheet2'!A6). I've tried removing the formula from these cell, but that didn't solve the problem.

View 5 Replies
View Related
May 18, 2009

I have a workbook with ~80 sheets. One sheet is a summary sheet containing data from each of the other sheets. The formula to display the data is pretty easy, but I need a function to copy the formula but increment the worksheet reference in each cell.

For example:

the formula in one cell is ='17'!$AI$6

The next cell should be ='18'!$AI$6

and so on...

I have about 12 columns like this, so I really don't want to edit each of them individually.

View 5 Replies
View Related
Feb 23, 2013

I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.

View 11 Replies
View Related
Nov 17, 2012

Our small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.

When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:

Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.

The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.

Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.

View 5 Replies
View Related
Mar 30, 2013

I have about 180 workbooks which I need to compile into a Master Schedule.

All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?

Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?

View 1 Replies
View Related
Aug 5, 2014

I'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:

A

B

C

1

First Name:

Last Name:

Address:

John

Smith

123 Harold Street

First Name: John

Last Name: Smith

Address: 123 Harold Street

View 7 Replies
View Related
Aug 20, 2014

I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.

vbaStk.JPG

What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.

I'm new to VBA and I'm not so sure what I'm doing with vba codes

View 1 Replies
View Related
May 19, 2014

I have this function that works on line 3 and if the conditions are met, the result is 1

=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))

Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1

I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,

View 9 Replies
View Related
May 29, 2013

I need to do two things in my Excel spreadsheet: 1) I want it to insert a blank row everytime a value in the 1st column changes. 2) In the blank lines, I want to do a COUNTA for each of columns G through N. If I can get the program to insert the blank rows...they will not be a set # of records apart...some will have 3 records & some might have 17 records. Is there a way to automatically cause #2 to happen instead of having to choose the function icon and then tell Excel the first and last cells in each range?

View 1 Replies
View Related
Feb 6, 2013

I need the Service Order tab to populate with data from the CPR tab based on the IO#. Since there is more than 1 occurrence of an IO# I need a formula to list the 1st, 2nd, 3rd, etc result of the vlookup in the cell under the 1st.

=VLOOKUP($E$2,CPR!$L$4:$AA$12,2,FALSE),

When I drag this down I get the 1st occurrence of the IO# 777 I need all of them.

B C D

Atex ID#(unique id) Campaign name Site

Row 7 1st occurrence of IO# 777

Row 8 2nd occurrence of IO# 777

Row 9 3rd occurrence of IO# 777

View 2 Replies
View Related
Jun 21, 2008

Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.

I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):

This would be the data on sheet 1:

Name Bonus Commision

Tom $45 $50

Mary $25 $75

Mary $30 $80

Tom $60 $50

Tom $90 $25

What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:

Tom's Sheet:

Tom $45 $50

Tom $60 $50

Tom $90 $25

Mary's Sheet:

Mary $25 $75

Mary $30 $80

I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.

Is this possible to do in Excel without using a macro?

View 9 Replies
View Related