#N/A errors when matching time values
 

Hi fellows
can sombody figure this out ? :-)

http://pmexcelent.dk/Mappe11.xls

Excel 2003 DK

#N/A errors when matching time values
 

"excelent" skrev:

Hi fellows
can sombody figure this out ? :-)

http://pmexcelent.dk/Mappe11.xls

Excel 2003 DK

timevalues in A2:A20 timevalue in B2:B20
A2 and A3 is put in manual all values is put in manual
then rest is fill/copy down
A2=00:02 B2=00:02
A3=00:03 B3=00:03
and so on
then formatted as numbers with 19 decimals

as u can c they r not equal all the vay down - why?

A B
0,0013888888888888900 0,0013888888888888900
0,0020833333333333300 0,0020833333333333300
0,0027777777777777700 0,0027777777777777800
0,0034722222222222200 0,0034722222222222200
0,0041666666666666600 0,0041666666666666700
0,0048611111111111100 0,0048611111111111100
0,0055555555555555500 0,0055555555555555600
0,0062500000000000000 0,0062500000000000000
0,0069444444444444400 0,0069444444444444400
0,0076388888888888800 0,0076388888888888900
0,0083333333333333300 0,0083333333333333300
0,0090277777777777700 0,0090277777777777800
0,0097222222222222200 0,0097222222222222200
0,0104166666666667000 0,0104166666666667000
0,0111111111111111000 0,0111111111111111000
0,0118055555555555000 0,0118055555555556000
0,0125000000000000000 0,0125000000000000000
0,0131944444444444000 0,0131944444444444000
0,0138888888888888000 0,0138888888888889000

#N/A errors when matching time values
 

Its a quirk in excel and the way it stores values. There have been many
threads discussing this, If you search for Jerry Lewis he seems to be
the person who understands it most

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=564195

#N/A errors when matching time values
 

ok tks Dav
i got the address on this page to help out the problem looks fine

http://support.microsoft.com/kb/211830/en-us


"Dav" skrev:


Its a quirk in excel and the way it stores values. There have been many
threads discussing this, If you search for Jerry Lewis he seems to be
the person who understands it most

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=564195

#N/A errors when matching time values
 

well, I use something MUCH better!!!!!!
I use ROUND function and everything works just fine - take value and with
ROUND function you can round number to 10 decimal places (that's maximum
which excel uses for date/time format) and after that every calculation or
comparison will work just fine!!!!!
There is great difference between round function and number formatting

ENJOY!
"excelent" wrote in message
...
ok tks Dav
i got the address on this page to help out the problem looks fine

http://support.microsoft.com/kb/211830/en-us


"Dav" skrev:


Its a quirk in excel and the way it stores values. There have been many
threads discussing this, If you search for Jerry Lewis he seems to be
the person who understands it most

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile:
http://www.excelforum.com/member.php...o&userid=27107
View this thread:
http://www.excelforum.com/showthread...hreadid=564195

#N/A errors when matching time values
 

Excel stores time as a decimal fraction of a day. For example, 0:01 is
stored as 1/24/60 = 2^-5*3^-2*5^-1. 1/3 has no exact representation in
either decimal or binary, and 1/5 has no exact representation in binary, so 1
minute must unavoidably be approximated (the only "simple" times with exact
representations are integer multiples of 675 seconds, 45 minutes, or 3
hours). Since your fill increment is only approximately what you intended,
it should be no surprise when the resulting fill values only approximately
equal the values that you would get by manually entering those times.

The only thing here that may be remotely unique to the Excel/Lotus family of
spreadsheets is the use of decimal fractions of a day as a time storage
format. The subsequent issues of finite precision math are common to all
numeric software except for symbolic packages like Maple and Mathematica.

You can get "full" accuracy time values by using =ROW()/24/60 in cells
A2:A20 instead of trying to fill with an approximate increment.

You see trailing zeros when you try to display 19 decimal places because
Excel (as documented in Help) displays no more than 15 significant figures.
If you really want to see the actual decimal values of these approximations,
you should use my D2D function from
http://groups.google.com/group/micro...06871cf92f8465

Jerry

"excelent" wrote:



"excelent" skrev:

Hi fellows
can sombody figure this out ? :-)

http://pmexcelent.dk/Mappe11.xls

Excel 2003 DK

timevalues in A2:A20 timevalue in B2:B20
A2 and A3 is put in manual all values is put in manual
then rest is fill/copy down
A2=00:02 B2=00:02
A3=00:03 B3=00:03
and so on
then formatted as numbers with 19 decimals

as u can c they r not equal all the vay down - why?

A B
0,0013888888888888900 0,0013888888888888900
0,0020833333333333300 0,0020833333333333300
0,0027777777777777700 0,0027777777777777800
0,0034722222222222200 0,0034722222222222200
0,0041666666666666600 0,0041666666666666700
0,0048611111111111100 0,0048611111111111100
0,0055555555555555500 0,0055555555555555600
0,0062500000000000000 0,0062500000000000000
0,0069444444444444400 0,0069444444444444400
0,0076388888888888800 0,0076388888888888900
0,0083333333333333300 0,0083333333333333300
0,0090277777777777700 0,0090277777777777800
0,0097222222222222200 0,0097222222222222200
0,0104166666666667000 0,0104166666666667000
0,0111111111111111000 0,0111111111111111000
0,0118055555555555000 0,0118055555555556000
0,0125000000000000000 0,0125000000000000000
0,0131944444444444000 0,0131944444444444000
0,0138888888888888000 0,0138888888888889000

#N/A errors when matching time values
 

as I said - excel uses 10 decimal places for time/date not 15! for regular
numbers he uses 15 but with date/time only 10 -- 5 is good for calculation
or comparison (unless you're quantum physics...)

"Jerry W. Lewis" wrote in message
...
Excel stores time as a decimal fraction of a day. For example, 0:01 is
stored as 1/24/60 = 2^-5*3^-2*5^-1. 1/3 has no exact representation in
either decimal or binary, and 1/5 has no exact representation in binary,
so 1
minute must unavoidably be approximated (the only "simple" times with
exact
representations are integer multiples of 675 seconds, 45 minutes, or 3
hours). Since your fill increment is only approximately what you
intended,
it should be no surprise when the resulting fill values only approximately
equal the values that you would get by manually entering those times.

The only thing here that may be remotely unique to the Excel/Lotus family
of
spreadsheets is the use of decimal fractions of a day as a time storage
format. The subsequent issues of finite precision math are common to all
numeric software except for symbolic packages like Maple and Mathematica.

You can get "full" accuracy time values by using =ROW()/24/60 in cells
A2:A20 instead of trying to fill with an approximate increment.

You see trailing zeros when you try to display 19 decimal places because
Excel (as documented in Help) displays no more than 15 significant
figures.
If you really want to see the actual decimal values of these
approximations,
you should use my D2D function from

http://groups.google.com/group/micro...06871cf92f8465

Jerry

"excelent" wrote:



"excelent" skrev:

Hi fellows
can sombody figure this out ? :-)

http://pmexcelent.dk/Mappe11.xls

Excel 2003 DK

timevalues in A2:A20 timevalue in B2:B20
A2 and A3 is put in manual all values is put in manual
then rest is fill/copy down
A2=00:02 B2=00:02
A3=00:03 B3=00:03
and so on
then formatted as numbers with 19 decimals

as u can c they r not equal all the vay down - why?

A B
0,0013888888888888900 0,0013888888888888900
0,0020833333333333300 0,0020833333333333300
0,0027777777777777700 0,0027777777777777800
0,0034722222222222200 0,0034722222222222200
0,0041666666666666600 0,0041666666666666700
0,0048611111111111100 0,0048611111111111100
0,0055555555555555500 0,0055555555555555600
0,0062500000000000000 0,0062500000000000000
0,0069444444444444400 0,0069444444444444400
0,0076388888888888800 0,0076388888888888900
0,0083333333333333300 0,0083333333333333300
0,0090277777777777700 0,0090277777777777800
0,0097222222222222200 0,0097222222222222200
0,0104166666666667000 0,0104166666666667000
0,0111111111111111000 0,0111111111111111000
0,0118055555555555000 0,0118055555555556000
0,0125000000000000000 0,0125000000000000000
0,0131944444444444000 0,0131944444444444000
0,0138888888888888000 0,0138888888888889000

#N/A errors when matching time values
 

Your statement is not precise. Excel clearly does use full double precision
for time/date values, as the original post illustrates. It is true that only
5 decimal places are needed to resolve seconds (1/24/60/60 =
0.0000115740740740741 ~ 1E-5) or 6 decimal places to resolve tenths of a
second (the highest resolution in a time format). However, you can get
higher resolution time values for timing code
http://support.microsoft.com/?kbid=172338

Jerry

"Sasa Stankovic" wrote:

as I said - excel uses 10 decimal places for time/date not 15! for regular
numbers he uses 15 but with date/time only 10 -- 5 is good for calculation
or comparison (unless you're quantum physics...)