LOOKUP worksheet function

[F. Lawrence Kulchar]

Why does:

=LOOKUP(1.00001,--{"1";"1:"})

return a value of 0.0417 ??

Thanks,

FLKULCHAR

 

[T. Valko]

Believe it or not...

"1:" is being evaluated as 1:00 AM or 0.0417

The lookup_value 1.00001 is greater than any value in the
lookup_vector --{"1";"1:"} so the result is the *last* number in the
lookup_vector that is less than the lookup_value. And, that number is "1:"
(1:00 AM or 0.0417).

I've run across this sort of thing before where you'd least expect it so now
I'm "aware" of the possibility. For example, in certain situations na (10a)
or np (3p) could also be evaluated as "time". This resolves back to Excel's
eagerness to "identify" times/dates.

 

[Lars-Åke Aspelin]

Why does:

=LOOKUP(1.00001,--{"1";"1:"})

return a value of 0.0417 ??

Thanks,

FLKULCHAR

The double minus transforms the string "1:" to a value.
"1:" is seen as short for "01:00:00" which is a time value which is
1/24 or 0.041466666...

Format the output as time and you will see that it displays as
01:00:00

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

The double minus transforms the string "1:" to a value.
"1:" is seen as short for "01:00:00" which is a time value which is
1/24 or 0.041466666...

Format the output as time and you will see that it displays as
01:00:00

Hope this helps / Lars-Åke

Oops. 1/24 or 0.04166666... it should be.

 

[FLKulchar]

Believe it or not...

"1:" is being evaluated as 1:00 AM or 0.0417

The lookup_value 1.00001 is greater than any value in the
lookup_vector --{"1";"1:"} so the result is the *last* number in the
lookup_vector that is less than the lookup_value. And, that number is "1:"
(1:00 AM or 0.0417).

I've run across this sort of thing before where you'd least expect it so
now I'm "aware" of the possibility. For example, in certain situations na
(10a) or np (3p) could also be evaluated as "time". This resolves back to
Excel's eagerness to "identify" times/dates.

 

[FLKulchar]

Thank you very much for your expert analyses!
I was perplexed,
FLKulchar

 

[FLKulchar]

thank you so very much...I wa was perplexed.

FLKulchar

 

[T. Valko]

You're welcome. Thanks for the feedback!