V look up and data that goes out when copied in range
 

Hi I have a SS that calculates points automatically for athletics based on
times/distances etc
I use the following Vlookup to look up the time and then return the points
=IF(ISNA(VLOOKUP($I15,Tables!$G:$H,2,FALSE)),0,VLO OKUP($I15,Tables!$G:$H,2,FALSE))
This works fine for most events but where I have run into a problem is I
have tested on small amount of data but now have the real thing to add. The
1500metres has been created by someone else using a time function to display
the correct time. I have worked around this by using 4 decimal places eg
5.0934 (effectively 5.09.34) . There are 21,000 entries that I need to
convert and have done this by using custom format ??.???? What happens (even
with out this format) is that I can only copy 20 lines by highlighting 3
times to increase using that pattern. After 20 it starts adding more than 4
places eg
4.4420 is starting time goes through with 4 places until 4.44429999999999.
The lookup can't find the time if it is more than 4 dp. I don't want to have
to go through and 21,000 highlighting 20 at a time.
Is there anyway I can get rid of the extra digits and have only 4 dp.I am
not experienced with programming.

Many thanks in advance Lyn

V look up and data that goes out when copied in range
 

Hi

I have found the trunc function and that works on individual cells. If I
copy down it doesn't pick up each cell just the 1. How can I copy formuala
without affecting cell contents. I tried with all the data in the column and
highlighting and then using trunc but it only worked for 1st cell. This may
still not be best way to do it. Any help appreciated

Cheers Lyn

"Lyndy Lou" wrote:

Hi I have a SS that calculates points automatically for athletics based on
times/distances etc
I use the following Vlookup to look up the time and then return the points
=IF(ISNA(VLOOKUP($I15,Tables!$G:$H,2,FALSE)),0,VLO OKUP($I15,Tables!$G:$H,2,FALSE))
This works fine for most events but where I have run into a problem is I
have tested on small amount of data but now have the real thing to add. The
1500metres has been created by someone else using a time function to display
the correct time. I have worked around this by using 4 decimal places eg
5.0934 (effectively 5.09.34) . There are 21,000 entries that I need to
convert and have done this by using custom format ??.???? What happens (even
with out this format) is that I can only copy 20 lines by highlighting 3
times to increase using that pattern. After 20 it starts adding more than 4
places eg
4.4420 is starting time goes through with 4 places until 4.44429999999999.
The lookup can't find the time if it is more than 4 dp. I don't want to have
to go through and 21,000 highlighting 20 at a time.
Is there anyway I can get rid of the extra digits and have only 4 dp.I am
not experienced with programming.

Many thanks in advance Lyn