|
LOOKUP worksheet function
Why does: =LOOKUP(1.00001,--{"1";"1:"}) return a value of 0.0417 ?? Thanks, FLKULCHAR |
LOOKUP worksheet function
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. -- Biff Microsoft Excel MVP "F. Lawrence Kulchar" wrote in message ... Why does: =LOOKUP(1.00001,--{"1";"1:"}) return a value of 0.0417 ?? Thanks, FLKULCHAR |
LOOKUP worksheet function
On Sat, 26 Jul 2008 20:05:00 -0700, F. Lawrence Kulchar
wrote: 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 |
LOOKUP worksheet function
On Sun, 27 Jul 2008 03:28:03 GMT, Lars-Åke Aspelin
wrote: On Sat, 26 Jul 2008 20:05:00 -0700, F. Lawrence Kulchar wrote: 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 Oops. 1/24 or 0.04166666... it should be. |
LOOKUP worksheet function
"T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "F. Lawrence Kulchar" wrote in message ... Why does: =LOOKUP(1.00001,--{"1";"1:"}) return a value of 0.0417 ?? Thanks, FLKULCHAR |
LOOKUP worksheet function
Thank you very much for your expert analyses!
I was perplexed, FLKulchar "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "F. Lawrence Kulchar" wrote in message ... Why does: =LOOKUP(1.00001,--{"1";"1:"}) return a value of 0.0417 ?? Thanks, FLKULCHAR |
LOOKUP worksheet function
thank you so very much...I wa was perplexed.
FLKulchar "Lars-Åke Aspelin" wrote in message ... On Sat, 26 Jul 2008 20:05:00 -0700, F. Lawrence Kulchar wrote: 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 |
LOOKUP worksheet function
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "FLKulchar" wrote in message ... Thank you very much for your expert analyses! I was perplexed, FLKulchar "T. Valko" wrote in message ... 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. -- Biff Microsoft Excel MVP "F. Lawrence Kulchar" wrote in message ... Why does: =LOOKUP(1.00001,--{"1";"1:"}) return a value of 0.0417 ?? Thanks, FLKULCHAR |
| All times are GMT +1. The time now is 10:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com