| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP worksheet function
Why does: =LOOKUP(1.00001,--{"1";"1:"}) return a value of 0.0417 ?? Thanks, FLKULCHAR |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup for worksheet | Excel Worksheet Functions | |||
| Worksheet Rank / Lookup function | Excel Worksheet Functions | |||
| how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
| Worksheet lookup | Excel Discussion (Misc queries) | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Linear Mode
