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.
View 2 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
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
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
Jun 1, 2007
I have formed data arrays in VBA after running a time series simulation model. The array is m simulations x n periods. Small example: ...
View 6 Replies
View Related
Mar 30, 2009
I want to manipulate team statistics and having a bit of trouble trying to figure out how to do it. I am relatively new to excel and am using Excel 2007.
I have attached the excel file for your reference (the same file).
What I need to do is first find out the team number using the table on first sheet named "Teams". User will enter team name on call B4 of sheet "Team entry" then in cell C4 there should be a formula to find the team number (is beside name on sheet "Teams) and displays it (on C4). Then on cell D4 of the same sheet "Team entry" there should be a formula that displays cell A1 (shows a statistic for that team) of the team sheet. Each team has its own stat sheet named by its team number (easier for me to keep track). So basically D4 should show cell A1 of the sheet that shows stats of the team entered. In addition cell E4 of "Team entry" should display stat2 (of the sheet "stat2")for the team entered.
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
Dec 23, 2013
excel 2010. This workbook has 4 worksheet(Process Engineer,OSBL,OSA,Lab Operator) I want to know what is the best excel formula/function to summary this 4 worksheet.
Example:I want a formula/function to summary all the statement from 4 worksheets and total number of answer "1" per statement from 4 worksheet.
Sample Statement below
"Demonstrate Interpersonal (People-to-People-) Skills" Question:What is the formula if above statement contains this statement in 4 worksheet?As i checked the total is 4 then What is the formula to get all total answered ICC on this statement from 4 worksheet?
View 2 Replies
View Related
Aug 23, 2007
Been searching the archives but not finding my answers. I downloaded historical lottery numbers and want to run statistics on them- just no clue where to start. I guess the 1st and most important thing is to figure out which #s have come up most often out of all of them. I assume it's some sort of COUNT or LOOKUP code- but not sure. I have the #s arranged in a sheet like so ...
View 6 Replies
View Related
Jun 27, 2008
want to make a macro to do a 'descriptive statistics data analysis' of column b and put the results in m1 to n18.
basically doing this but in a macro.
add in= tools /data analysis/ descriptive stats
I have recorded it doing this, but it for some reason can't do what it wrote.
View 9 Replies
View Related
Mar 9, 2009
On Sheet1 I have 2 cells one has the date 1-mar-09 and the other has 7-mar-09. I need to find this range on Sheet2 which has all the dates for the year in ColumnA and then total the amounts found in Sheet2!ColumnB for the specified date range. Is this possible without VB?
View 9 Replies
View Related
Jan 27, 2009
I'm trying to pull some statistics from a workbook I've been sent and am having some trouble working out how to achieve this.
I have a small interface that allows the user to choose a possible answer (drop-down menu) from a questionnaire (Yes, No, Maybe)(cell: C6). On the interface the user also picks the question they wish to see the stats for (cell: C5)
The data I have been sent has been set up with the questions along in row A, and the answers below in each column (the answers run across row A from column G - AH).
So, what I'm basically after is a formula that first looks up the question specified in C5 (I've used HLOOKUP to pull some other data), and then counts how many times the Yes, No or Maybe answers appears in the column where the question data is held.
View 4 Replies
View Related
Dec 3, 2009
I have two rows in a sheet with random numbers (1 till 90).
Those numbers in those two rows I change them every 5 minutes with new one still from 1 to 90
I need a "way", in one side of my sheet (lets say in column B) to count how many times a number was repeated after finish my work (example after 2 hour's)
Is that possible with excel 2007?
View 7 Replies
View Related
Oct 9, 2002
I have been looking everywhere for this. In SPSS, it is very easy to recode data, but how do you do this in Excel? For example, researchers often "reverse" their questions on surveys with Likert scales to eliminate respondent errors. In those questions, you want 1=5, 2=4, 3=3, 4=2, 5=1. Is there a plug-in or some other formula that is out there?
View 9 Replies
View Related
May 16, 2008
I have a table of contents page. The TOC is updated each time some one opens the sheet (this is a must have based on the requirements). I'm trying to include some statistics next to each item in the TOC. These statistics are on each worksheet and just need to be copied to the TOC.
Now my problem.
The worksheets are NOT keep in alphabetical order, so After the TOC is created a sort is done to put the TOC links in alphabetical order. After the order is set, I need a formula that will read the worksheet name from a cell on the TOC (which is really a hyper link to a worksheet).
I have this already:
ActiveCell.FormulaR1C1 = "=Address!R[-2]C[-2]"
If "=Address!" (Where address is the name of one of the sheets) could be replaced with a cell reference that (I think) would do the trick.
My TOC is a MACRO not a cell formula so if this can not be done with a cell formula but can be done through VBA that would be fine.
View 6 Replies
View Related
Jul 6, 2008
I have running total of bowling scores current & going back from when I first started.
What I’m trying to archive is a formula that will count the most recent 96 scores and still allow me to add new scores each week in keeping the current running total of 96 games.
The statistics are listed from A4:C215 that may have 1 or 2 scores not listed. (missed games)
Each week new statistics will be added in cell A4:C4 anywhere’s of 1-3 games.
View 6 Replies
View Related
Sep 11, 2008
Back to test you with a few problems as I work through a project I'm carrying out, based on past results data of the English Premier League.
I have some of the solutions/formulas I require already, but there are still some gaps to be filled in!
***
the spreadsheet is set out as follows:
Key Columns / Sub-Title (Remarks)
B Date (the date a match was played - the s.sheet is sorted by this column, A>Z)
C Home Team
D Away Team
E Home team goals scored
F Away team goals scored
I-M Indicates via U or O whether the match contained under or over 0.5,1.5,2.5,3.5,4.5 goals
N Points obtained for Home team (i.e. 3 for a win, 1 for a draw, and 0 for a loss)
O Points obtained for Away team (i.e. 3 for a win, 1 for a draw, and 0 for a loss)
There are 381 rows in total for each worksheet (season) - 1 for the titles, and 2-381 for each match played in the season...................
View 9 Replies
View Related
Mar 3, 2007
I try to predict some macro economic statistics but any attempt till now didn't make sense. the attached file. Note: when i used the FORECAST function the predicted values showed an unlogical drop while there seems to be a positive trend.
View 4 Replies
View Related
Mar 14, 2008
I am creating a workbook to manage golfer scores, teams, winnings, handicap, ect. I am having trouble with the statistics sheet. I Need A Sub To:
1. Column "B" , take the average of the lowest 5 numbers in columns "AB" though "AU"
2. Column "C" , take the average of the lowest 10 numbers in columns "G" through "Z" divided by 0.96
3. Column "D" , take the average of the lowest 10 numbers in columns "AW" through "BP"
this needs to happen for each row where there is a name. (names added daily)
I have included a similar sheet as the one i am working with along with some command buttons typically not included. Should show some of the problems I am having.
View 5 Replies
View Related
Apr 1, 2008
I need to record on Sheet 2 how many times enteries in Coloum B on Sheet 1 appear per date range.
View 3 Replies
View Related
Oct 6, 2006
I have a simple spreadsheet recording games played on my football table.
Each match is the first to 10 goals. So each result is a simple: ...
View 7 Replies
View Related
Sep 30, 2008
I have a scenario where by Column A is date incident opened and Colum B is date incident closed.
And Column C shows the days between them. I got this and its fine. However, how do i
make a table or in any how define it as if the number is 1 - 3 days then it should be A
1-3 days = A
4-6 days = B
7-8 days = C
9 days onwards = D
This is because i need to make a chart and graph of the turn around time and the best is to define into a character alphabet so the chart will show the statistics of how many A, B, C and D But how do i do that.
View 9 Replies
View Related
May 8, 2014
I am tracking baseball batting statistics. The first column in each row is the player's name and the next column is the date. Several columns of statistics follow. I enter the batting stats game by game. I know I can use the SUBTOTAL function combined with AutoFilter to view each player's statistics individually totalled, but what I'd like is to see that subtotal line permanently displayed for each player.
View 2 Replies
View Related
Jan 31, 2013
I am trying to create and calculate player statistics using Vlookup in Excel. This is what I am trying to do:
BCD
5PlayerGoalsAssists
6Jordan Eberle00=VLOOKUP(TEXT(B6,"@"),B13:G14,4) used in C6
7James Neal 00=VLOOKUP(B7,B13:G14,4) used in C7
cell format is "general" in B6 thru B14
Lookup TableCol ECol FCol G
Player NameTeamGPGAPts
13James NealPITT4325
14Jordan EberleEDM1404
Imported Data from Yahoo Sports and have sorted in ascending order a-b-c-d
(pasted as text and than changed to General in Excel formatting)
I have tried using 2 different vlookup formulas structures, but no success. I have tried using numeric reference (i.e using numeric 1 in for both in the lookup table and the original cell) and that works.
View 3 Replies
View Related
May 9, 2009
I am auto-importing statistics from the web via web query. This information imported has a number in the form of a decimal stated as either x.1, or x.2. x.1 represents x-1/3 (one-third) and x.2 represents x-2/3 (two-thirds). I need to use these numbers in a calculation after importing them, but using x.1 or x.2 obviously does not give me accurate results as the numbers should actually be x.3333333 and x.6666666 respectively. What is the easiest way to convert the imported numbers to their actual decimals?...
View 2 Replies
View Related
Dec 6, 2011
I am improving an old spreadsheet that contains a tab for inputting the following data, all using data validation:
date
employee (who did the work)
team (for whom work was done)
department (for whom work was done)
type (of work done)
Project (which property worked on)
new/revision (was this a new project or revision of old)
time (spent working on this)
I have a sheet with a drop down in which you can sort by employee and the associated chart and graphs adjust the information accordingly.
I need to create a chart and graph in which I can track the trailing month as well as year to date. Ideally, I would like to sort by the following:
- This month (i.e., the 1st of the month to present)
- previous month (etc., be able to select all previous months for which data was entered, i.e. Jan., Feb. March. etc.)
- Year to date
I know there is a way to do this - I thought maybe it would be through some formula incorporating =today() and subtracting back to what you need using numeric dates?
View 1 Replies
View Related
Apr 14, 2009
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
View 3 Replies
View Related
