LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A F
If you manually entered the times in A8:A31 and you still got incorrect
results then there's only one thing left that could cause the problem. This
formula isn't returning the time value you think it is
=IF(C32<10,TIME((B32-(H24-J33)),0,0),(J33-TIME(H24,0,0)))
You said that when that formula returns 4:00 you get an inorrect result from
the lookup formula. Test the 2 times to see if they are in fact equal. If
above formula is in cell A1 and 4:00 AM is in cell A12 try comparing them:
Enter this formula *exactly* as shown. The parentheses are critical!
=(A1-A12)=0
If the 2 times are exactly equal that formula will return TRUE.
You're getting a "rounding error" somewhere. If you are certain it isn't
coming from the A8:A31 times then it has to be coming from the
=IF(C32<10.... formula.
--
Biff
Microsoft Excel MVP
"JCC" wrote in message
...
A8:A31 are times from 0:00 to 23:00 from a data query. Already tried
manual
input...thought that was the problem...no luck!
"T. Valko" wrote:
A8:A31
I assume those are the sequential times like:
12:00 AM
1:00 AM
2:00 AM
3:00 AM
...
11:00 PM
How did you fill these times in? If you drag filled them then that's your
problem. Try *manually* re-entering them and see if that clears up the
problem.
--
Biff
Microsoft Excel MVP
"JCC" wrote in message
...
The lookup value I'm using is from the following string.
=IF(C32<10,TIME((B32-(H24-J33)),0,0),(J33-TIME(H24,0,0)))
The result of this string is being displayed as the correct time (4:00)
but
referencing it as a lookup value (=LOOKUP(J24,A8:A31,C8:C31) the result
being
displayed for the lookup function is the value for 3:00 not 4:00. I
have a
column renerating the times (the same basic string of functions) and 10
out
of 12 lookup results are off by one hour? Only 9:00 and 11:00 are
returning
the correct values. All the cells are formated the same as well? Anyone
have
any ideas?
Thanks,
Jerry
|