Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#N/A errors when matching time values
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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...) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fun with Time values | Excel Discussion (Misc queries) | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions | |||
need help with - time values... | Excel Discussion (Misc queries) | |||
Time Values | Excel Discussion (Misc queries) | |||
How do you find the difference between two time values when one i. | Excel Discussion (Misc queries) |