Excel

[AlanBaze]

I'm Completely ignorant in Excel
Can I make a formula in a cell to automatically bring up a speed number by importing, or typing manually a time in that cell? Example Time of 1;12.3= A speed rating of 80. If so any one willing to help. Thanks

[JJMartin]

as in getting the final time from the charts? You could do it.

[AlanBaze]

Final time as well as internal fractions from charts.

[JJMartin]

Don't really need internal fracs just final time, track record and lengths off finish for basic speed fig

[AlanBaze]

I have my own speed charts for internal fractions, and final times that I wish to apply as numbers instead of the fractions.

[whodoyoulike]

If I understand your question, see if the vlookup or hlookup functions will help you.

[HUSKER55]

indexing, once you get the hang of it, is way more flexible.

[Tom]

I have my own speed charts for internal fractions, and final times that I wish to apply as numbers instead of the fractions.

That can be done easily.
I'll give you an example a little later on.

[Ray2000]

Because horse times usually end in fifths not tenths, it can get tricky trying to convert. It's best to use a user defined function in VBA macro but this formula ...

=LEFT(A2,FIND(":",A2)-1)*60+MID(A2,FIND(":",A2)+1,2)+(RIGHT(A2,1)*2)/10

will work providing
1. the times are consistant with colon (":") separator between minutes and seconds
2. must use a leading zero if time is less than a minute
3. seconds must use "0" if less than 10 ..02, 06
4. right hand digit must not be tenths or you'll need to change the formula

Make sure the times you import or manually type, go into cells formatted as "TEXT"
(if you're referencing a time in another cell then use the text function to bring it in)

After you get the time expressed as total seconds you can then use it in your formula for Speed number or use a vlookup table

Attached spreadsheet shows some examples

[AlanBaze]

:confused:

When typing the times in the VBA Macros is there a quick way to insert the times without doing them all manual?

[Ray2000]

sometimes :)


sorry, couldn't resist that


I need to know if you're referring to entering times in the cells of a worksheet or in the VBA macro.

assuming it's cells in a worksheet
If you have a "time" value in a cell like......................... 1:10
and the next increment of time in the adjoining cell like .........1:15

select the 2 cells, grab the small black square in lower right corner with the crosshair cursor and drag down (or accross) as far as you want to fill. Excel will give you a sequence of steps in time.


Otherwise, if you are importing times from another source, copy paste should work.

[raybo]

:confused:

When typing the times in the VBA Macros is there a quick way to insert the times without doing them all manual?

This is why so many people use comma delimited data files. You import the data file then write formulas in cells that reference and manipulate that data.

But, if you are manually typing the times in, then just type them in without the colon, but if they include fifths of a second, change them to decimals, like .0, .2, .6, etc. (make sure you use a decimal even if it is a 0).

Then you use the "LEN(" function to count the number of characters, and if the count is less than 5 (59.6 for example), then leave the time as it is, and if the count is 5 then subtract 40 or 80 (112.6 - 40 = 72.6 or 203.2 - 80 = 123.2).

So, if you type the time in cell "A1" then you could type the following formula in cell B1:

=IF(LEN(A1)<5,A1,IF(LEFT(A1,1)=2,A1-80,A1-40)

This will assure that all your times are in seconds and tenths of a second. Then you can create a list of possible times (in seconds and tenths) and their corresponding speed figures and use the "VLOOKUP(" function to grab the figure for any time that appears in the list.

[raybo]

OOps, make sure the 2 in the above formula is in quotes and that the cell you type the time in is formatted as TEXT:

=IF(LEN(A1)<5,A1,IF(LEFT(A1,1)="2",A1-80,A1-40)