I don't understand!!
Posted by Ian Mac on February 01, 2002 3:32 AM
Can any 1 help.
I have the formula =if(B1,B1-A1,"")
looking at the Timevalues 17:00 (A1) and 17:00 (B1).
What it's doing is checking for variance (I'm using 1904 date system so it can handle negative timevalues)
The cell with the formula has Conditional formating, Cell Value Is - Greater Than 0 AND Cell Value Is - Less Than 0. both RED
The problem is that the cell value appears to be 0:00 but using F9 is -3.33066907387547E-16??? odd I thought, which makes the Conditions not work (well they are working really, just the formula isn't)
so in the next cell I simply type =B1-A1 and the answer is 0:00 or 0 (F9).
why would this happen????
One thing to add is that the first number (17:00) is the result of Vlookup and the other is just typed in.
Many thanks
Ian Mac
Posted by IML on February 01, 2002 6:00 AM
Just a guess
Does it work if you add +0 to the end of your vlookup statement?. ie
=vlookup(this,from_here,col,0)+0
Posted by Benny on February 01, 2002 6:31 AM
********* Are your formats the same in the VLOOKUP table and the value cell? Compare the decimal values of the entered cell and the lookup cell (copy, then paste special value each value in another cell, then make it comma format to check decimal values).
Posted by Ian Mac on February 01, 2002 6:33 AM
No.....
No!!
the formula is quite mad but does result in number.
The bit I don't understand is that the -3.33066907387547E-16 is being generated by the IF, take it out and no probs????
Ian Mac
Posted by Ian Mac on February 01, 2002 6:39 AM
See below (NT)
********* Are your formats the same in the VLOOKUP table and the value cell? Compare the decimal values of the entered cell and the lookup cell (copy, then paste special value each value in another cell, then make it comma format to check decimal values).
Posted by Ian Mac on February 01, 2002 6:43 AM
Further....
F9 in the vlookup cell results:
0.708333333333333
general number on the other is:
0.708333333333333
the same! (can't tel1 beyond the no 3's)
but if I change that cell to 17:00 typed in, problem solved??
to ad more confusion into the mix the cell above works fine on EXACTLY the same conditions??
I'm Baffled.
Ian Mac
Posted by Mark W. on February 01, 2002 6:49 AM
Read this...
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q78113
...and consider using this...
=IF(B1,FLOOR(B1,1/1440)-FLOOR(A1,1/1440),"")
...for time precision in minutes. If you need
your time precision to be in seconds, use...
=IF(B1,FLOOR(B1,1/86400)-FLOOR(A1,1/86400),"") Can any 1 help.
Posted by Larry on February 01, 2002 7:07 AM
*** How about a simpel ROUND function for each value? Can any 1 help.
Posted by Ian Mac on February 01, 2002 8:11 AM
Re: Read this...
...and consider using this... =IF(B1,FLOOR(B1,1/1440)-FLOOR(A1,1/1440),"") ...for time precision in minutes. If you need
=IF(J11,FLOOR(J11,1)-FLOOR(H11,1),"")
works for me, just can't understand the need
also re-typing the 17:00 at the lookup source works?? but this isn't really do-able because it's a schedule sheet and it would take ages to type every value.
Ian Mac
Posted by Ian Mac on February 01, 2002 8:17 AM
My mistake!!! =IF(B1,FLOOR(B1,1/1440)-FLOOR(A1,1/1440),"") works
Posted by Mark W. on February 01, 2002 2:44 PM
And, what would you ROUND to?...
...Keep in mind that you can't arbitrarily round
a time and preserve its value!!! For example,
=ROUND("17:00"+0,1) is 16:48
=ROUND("17:00"+0,2) is 17:02
=ROUND("17:00"+0,3) is 16:59
=ROUND("17:00"+0,4) is 16:59
=ROUND("17:00"+0,5) is 17:00
=FLOOR("17:00"+0,1/1440) will ALWAYS round the
internal value down to its nearst displayed
minute.
*** How about a simpel ROUND function for each value? : Can any 1 help.
