Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula auditing/evaluate formula
I have:
In Cell A7: A55 In Cell B7: =LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7))))) When I perform 'formula auditing'/evaluate formula...I get: for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456789 ") the following: ({4,6,7,8,2,10,11,12,13}) Why do I NOT SIMPLY GET: 4 Thanks, FLKulchar |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula auditing/evaluate formula
Why would you, that's why min is wrapped around that part
-- Regards, Peo Sjoblom "F. Lawrence Kulchar" wrote in message ... I have: In Cell A7: A55 In Cell B7: =LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7))))) When I perform 'formula auditing'/evaluate formula...I get: for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456789 ") the following: ({4,6,7,8,2,10,11,12,13}) Why do I NOT SIMPLY GET: 4 Thanks, FLKulchar |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula auditing/evaluate formula
When I perform 'formula auditing'/evaluate formula...I get:
for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345678 9") the following: ({4,6,7,8,2,10,11,12,13}) Why do I NOT SIMPLY GET: 4 Because your processing an array of values: SEARCH({0,1,2,3,4,5,6,7,8,9} So, you get an array of values in return: {4,5,6,7,8,2,10,11,12,13} -- Biff Microsoft Excel MVP "F. Lawrence Kulchar" wrote in message ... I have: In Cell A7: A55 In Cell B7: =LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7))))) When I perform 'formula auditing'/evaluate formula...I get: for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456789 ") the following: ({4,6,7,8,2,10,11,12,13}) Why do I NOT SIMPLY GET: 4 Thanks, FLKulchar |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula auditing/evaluate formula
On Sat, 26 Jul 2008 19:10:01 -0700, F. Lawrence Kulchar
wrote: I have: In Cell A7: A55 In Cell B7: =LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7))))) When I perform 'formula auditing'/evaluate formula...I get: for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345678 9") the following: ({4,6,7,8,2,10,11,12,13}) Why do I NOT SIMPLY GET: 4 Thanks, FLKulchar I suspect you have a typo and that you really get back: {4,5,6,7,8,2,10,11,12,13} The find_text argument within the SEARCH function is an array, so SEARCH returns an array showing the locations of all of the find_text arguments. Your within_text string will be A7&"0123456789" or "A550123456789" The first find_text argument is "0" which is in position four. The next find_text argument is "1" which is in position five. And so forth. It is one of life's mysteries as to why some Excel functions return arrays and others do not. Some are documented to do so and some are not. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula auditing/evaluate formula
I fully understand; thank you very much!
FLKulchar "Ron Rosenfeld" wrote in message ... On Sat, 26 Jul 2008 19:10:01 -0700, F. Lawrence Kulchar wrote: I have: In Cell A7: A55 In Cell B7: =LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7))))) When I perform 'formula auditing'/evaluate formula...I get: for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"01234567 89") the following: ({4,6,7,8,2,10,11,12,13}) Why do I NOT SIMPLY GET: 4 Thanks, FLKulchar I suspect you have a typo and that you really get back: {4,5,6,7,8,2,10,11,12,13} The find_text argument within the SEARCH function is an array, so SEARCH returns an array showing the locations of all of the find_text arguments. Your within_text string will be A7&"0123456789" or "A550123456789" The first find_text argument is "0" which is in position four. The next find_text argument is "1" which is in position five. And so forth. It is one of life's mysteries as to why some Excel functions return arrays and others do not. Some are documented to do so and some are not. --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula auditing/evaluate formula
I fully understand; thank you very much!
FLKulchar "T. Valko" wrote in message ... When I perform 'formula auditing'/evaluate formula...I get: for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"01234567 89") the following: ({4,6,7,8,2,10,11,12,13}) Why do I NOT SIMPLY GET: 4 Because your processing an array of values: SEARCH({0,1,2,3,4,5,6,7,8,9} So, you get an array of values in return: {4,5,6,7,8,2,10,11,12,13} -- Biff Microsoft Excel MVP "F. Lawrence Kulchar" wrote in message ... I have: In Cell A7: A55 In Cell B7: =LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7))))) When I perform 'formula auditing'/evaluate formula...I get: for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456789 ") the following: ({4,6,7,8,2,10,11,12,13}) Why do I NOT SIMPLY GET: 4 Thanks, FLKulchar |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula auditing/evaluate formula
You're welcome!
-- Biff Microsoft Excel MVP "FLKulchar" wrote in message ... I fully understand; thank you very much! FLKulchar "T. Valko" wrote in message ... When I perform 'formula auditing'/evaluate formula...I get: for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456 789") the following: ({4,6,7,8,2,10,11,12,13}) Why do I NOT SIMPLY GET: 4 Because your processing an array of values: SEARCH({0,1,2,3,4,5,6,7,8,9} So, you get an array of values in return: {4,5,6,7,8,2,10,11,12,13} -- Biff Microsoft Excel MVP "F. Lawrence Kulchar" wrote in message ... I have: In Cell A7: A55 In Cell B7: =LOOKUP(9.9E+307,--MID(A7,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"012345 6789")),ROW(INDIRECT("1:"&LEN(A7))))) When I perform 'formula auditing'/evaluate formula...I get: for....SEARCH({0,1,2,3,4,5,6,7,8,9},A7&"0123456789 ") the following: ({4,6,7,8,2,10,11,12,13}) Why do I NOT SIMPLY GET: 4 Thanks, FLKulchar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Auditing | Excel Discussion (Misc queries) | |||
Formula Auditing | Excel Discussion (Misc queries) | |||
Formula auditing | Excel Discussion (Misc queries) | |||
Formula Auditing | Excel Discussion (Misc queries) | |||
FORMULA AUDITING | Excel Worksheet Functions |