LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU
 

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

LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU
 

Pl. list the contents of A8:A31 and C8:C31...
How are the cells formatted? Pl. share the formatting details.

"JCC" wrote:

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

LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU
 

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

LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A F
 

A8:A31 are times from 0:00 to 23:00 and are part of a data query. The cells
are formatted as custom h:mm. C8:C31 are data from the same query although I
changed the data manually to check the results of the "lookup" (700 for 7:00,
705 or 5:00 aso) formatted as general. Also tried manually inputting some of
the times from A8:A31...no luck? Any thoughts, I'm stumped!

Jerry

"Sheeloo" wrote:

Pl. list the contents of A8:A31 and C8:C31...
How are the cells formatted? Pl. share the formatting details.

"JCC" wrote:

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

LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A F
 

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

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