G
G
Guest
"excelent" skrev:
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
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
D
Dav
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
threads discussing this, If you search for Jerry Lewis he seems to be
the person who understands it most
Regards
Dav
G
Guest
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:
i got the address on this page to help out the problem looks fine
http://support.microsoft.com/kb/211830/en-us
"Dav" skrev:
S
Sasa Stankovic
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!
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!
G
Guest
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/microsoft.public.excel/msg/b106871cf92f8465
Jerry
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/microsoft.public.excel/msg/b106871cf92f8465
Jerry
S
Sasa Stankovic
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...)
numbers he uses 15 but with date/time only 10 -- 5 is good for calculation
or comparison (unless you're quantum physics...)
G
Guest
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
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