If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Index from TM
Hi,
Looking at an answer from Teethless Mama to someone else's question, raised yet another question. (what a surprise) The formula was: =OR(INDEX(A1:A3=B2,)) I was curious about the use of Index with a blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? And if you know that, perhaps you'll also know how to explain the whole thing to me. Regards - Dave. |
#2
|
|||
|
|||
Index from TM
And if you know that...
Sure do! perhaps you'll also know how to explain the whole thing to me. My specialty, but I'll wait to see if TM responds. I would be interested to read their explanation of both questions. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Looking at an answer from Teethless Mama to someone else's question, raised yet another question. (what a surprise) The formula was: =OR(INDEX(A1:A3=B2,)) I was curious about the use of Index with a blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? And if you know that, perhaps you'll also know how to explain the whole thing to me. Regards - Dave. |
#3
|
|||
|
|||
Index from TM
Ok Biff, I'll wait patiently.
Dave. "T. Valko" wrote: And if you know that... Sure do! perhaps you'll also know how to explain the whole thing to me. My specialty, but I'll wait to see if TM responds. I would be interested to read their explanation of both questions. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Looking at an answer from Teethless Mama to someone else's question, raised yet another question. (what a surprise) The formula was: =OR(INDEX(A1:A3=B2,)) I was curious about the use of Index with a blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? And if you know that, perhaps you'll also know how to explain the whole thing to me. Regards - Dave. . |
#4
|
|||
|
|||
Index from TM
OK, TM seems to be happy for you to proceed...
Dave. "T. Valko" wrote: And if you know that... Sure do! perhaps you'll also know how to explain the whole thing to me. My specialty, but I'll wait to see if TM responds. I would be interested to read their explanation of both questions. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Looking at an answer from Teethless Mama to someone else's question, raised yet another question. (what a surprise) The formula was: =OR(INDEX(A1:A3=B2,)) I was curious about the use of Index with a blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? And if you know that, perhaps you'll also know how to explain the whole thing to me. Regards - Dave. . |
#5
|
|||
|
|||
Index from TM
Ok, this is going to take me a while (real slow typer!) so check back later
this evening. -- Biff Microsoft Excel MVP "Dave" wrote in message ... OK, TM seems to be happy for you to proceed... Dave. "T. Valko" wrote: And if you know that... Sure do! perhaps you'll also know how to explain the whole thing to me. My specialty, but I'll wait to see if TM responds. I would be interested to read their explanation of both questions. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Looking at an answer from Teethless Mama to someone else's question, raised yet another question. (what a surprise) The formula was: =OR(INDEX(A1:A3=B2,)) I was curious about the use of Index with a blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? And if you know that, perhaps you'll also know how to explain the whole thing to me. Regards - Dave. . |
#6
|
|||
|
|||
Index from TM
OK,
I'm in Perth, Australia; so I'm not sure when 'later this evening' really is. But I'll keep checking. And thanks in advance for your time. Regards - Dave "T. Valko" wrote: Ok, this is going to take me a while (real slow typer!) so check back later this evening. -- Biff Microsoft Excel MVP "Dave" wrote in message ... OK, TM seems to be happy for you to proceed... Dave. "T. Valko" wrote: And if you know that... Sure do! perhaps you'll also know how to explain the whole thing to me. My specialty, but I'll wait to see if TM responds. I would be interested to read their explanation of both questions. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Looking at an answer from Teethless Mama to someone else's question, raised yet another question. (what a surprise) The formula was: =OR(INDEX(A1:A3=B2,)) I was curious about the use of Index with a blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? And if you know that, perhaps you'll also know how to explain the whole thing to me. Regards - Dave. . . |
#7
|
|||
|
|||
Index from TM
I was curious about the use of Index with a
blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? Enter these values in A1:A9 - 10,20,30,40,50,60,70,80,90 The reason you got the result from cell A3 is because you (initially) entered the formula in some cell on ROW 3. Try this, enter the formula in cell C1 and copy down to C9. Notice the pattern of results. Now, enter the formula in cell A15. What happened? Why is it now returning an error? Let's start with a general overview of function arguments then we'll get into the mechanics of the formula(s). Functions return results based on what the particular function does and based on the arguments the function processes. Some arguments are mandatory and some are optional depending on the particular function. If you have Excel version 2002 or later and you have Function Tool Tips enabled, when you start typing in a formula Excel shows you the syntax of the functions used in the formula. For INDEX it would look like this: INDEX(array,row_num,[column_num]) INDEX(reference,row_num,[column_num],[area_num]) The arguments are separated by commas (or semi-colons depending on your regional settings) and the optional arguments, if any, are enclosed in the [ ] brackets. These arguments can be either defined, empty or omitted. A defined argument is simply an argument that specifies the conditions or parameters of that argument. For example: =INDEX(A1:A9,5) 5 is the row_num argument so that argument is defined. An empty argument is one that has been "allocated" but hasn't been defined. As noted before, arguments are separated by commas so, if a comma appears then it is assumed that the argument has been "allocated" and what follows the comma is an argument. If the argument has been "allocated" but it isn't defined then it's evaluated as being empty. So, in your test formula: =INDEX(A1:A9,) The array_ argument has been defined, A1:A9. It's followed by a comma meaning that what follows is an argument, the row_num argument. The row_num argument has been "allocated" but it's not defined so it's empty. An empty argument will be evaluated as 0. If we were to include that argument: =INDEX(A1:A9,0) =INDEX(A1:A9,) Both of those do/mean the same thing. Now we need to understand omitted arguments. Omitted arguments are the [optional_arguments] that some functions have. If you don't use the optional arguments some of them have default parameters. That is, even if you don't define the optional argument it will evaluate to a default setting. These parameters and the default setting depend on the specific function. Let's look at the test INDEX function again and compare it to the syntax: INDEX(array,row_num,[column_num]) INDEX(A1:A9,) In the test formula we've got the defined array_argument and the empty row_num argument, both of which are mandatory arguments. We didn't use the optional column_num argument so in this application it defaults to 0. OK, now let's take a look at the mechanics of the formula. INDEX(A1:A9,) INDEX(A1:A9,0) In this application we're using a one dimensional array as the array_argument. The indexed array values are considered to be in a relative sequential position. That is: A1 = position 1 A2 = position 2 A3 = position 3 A4 = position 4 etc etc A9 = position 9 If the indexed array was C22:C25 the relative sequential positions would be the same: C22 = position 1 C23 = position 2 C24 = position 3 C25 = position 4 Typically, we use INDEX to return a value from the array_argument and we use the row_num and/or the column_num argument to define which value from the array we want returned. When the array is a vertical one dimensional array (as in the test formula) we would use the row_num argument to define which value we want. =INDEX(A1:A9,5) Return the value from position 5 of the range (indexed array) A1:A9. Ok, that's pretty simple. So, how do these empty arguments come into play here and what exactly do they do? Remember, an empty argument evaluates to 0. However, if the indexed array is considered to have relative sequential positions starting with 1, what does 0 do? There is no relative position 0. In this application 0 means to return the *entire* indexed array. So: =INDEX(A1:A9,) Returns the entire array of values from A1:A9. Since the formula is returning an array of values we need either another function that can handle an array of values for further processing or we need to enter the formula in an array of cells. However, if the formula is entered in a single cell that cell can only hold a single result. This is what you did, entered the formula in a single cell. Depending on where the formula cell is located and how the formula was entered will determine what result the test formula returns. This is the factor that led to you entering the formula in a cell and getting the value form A3. You had to have initially entered the formula in a cell on ROW 3. Because the formula, as written, is returning an array of results but wasn't using another function to further process the array of results, and/or the formula wasn't entered in an array of cells, you got the result that corresponds to the implicit intersection of the indexed array and the location of the formula cell. Let's assume the formula was normally entered in cell C3. Cell A3 is within the indexed array and cell C3 forms a direct intersection to cell A3. So, the result of the formula is the value from cell A3. This is the reason you got the results you did when I suggested (at the very beginning of this post) you enter the formula in C1 and copy down to C9. The formula entered in A15 returned an error because cell A15 is not within an implicit intersection of the indexed array *and* the formula was not entered as an array. If you initially enter the formula within an implicit intersection and then move the formula by dragging to a location outside the implicit intersection the result will be the same until Excel recalculates. Ok, now let's look at TM's formula and see how it works. Let's assume this is the data: A1 = 1 A2 = 2 A3 = 3 B2 = 3 =OR(INDEX(A1:A3=B2,)) A1:A3=B2 will return an array of either TRUE or FALSE: 1 = 3 = FALSE 2 = 3 = FALSE 3 = 3 = TRUE These logical values make up the array_argument to INDEX: INDEX({FALSE;FALSE;TRUE},) Because the row_num argument is empty it's evaluated as 0 so INDEX returns the *entire* array of logical values. The array of logical values is passed to the OR function: OR({FALSE;FALSE;TRUE}) OR can handle arrays so this is why the formula doesn't have to be array entered**. OR evaluates the array of logical values and if *any* of them are TRUE then the result of OR = TRUE. If none of the logical values is TRUE then OR = FALSE. So: =OR(INDEX(A1:A3=B2,)) =TRUE Array entered vs. normally entered... The test formula as written is returning an array of results: INDEX(A1:A9,) As noted before we either have to pass those results to another function for further processing or array enter** the formula in an array of cells the same size as the indexed range A1:A9 = 9 cells. To array enter the formula we would select the range of cells say, C1:C9, type the formula =INDEX(A1:A9,) but instead of hitting the Enter key like you normally would we would use a combination of keys, CTRL,SHIFT,ENTER. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key. With this particular formula the results would be the same as normally entering the formula (just hit Enter) in cell C1 and drag copying down to C9. Now, remember that formula that's in cell A15? It's currently returning an error because the formula is returning an array of results but the formula is in a single cell and this cell is not within an implicit intersection. Try this... Select cell A15 Hit function key F2 Now, array enter the formula. Hold down both the CTRL key and the SHIFT key then hit the ENTER key. See what happens? The formula now returns the *first* result of the indexed array. Well, that's all I got! exp101 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, this is going to take me a while (real slow typer!) so check back later this evening. -- Biff Microsoft Excel MVP "Dave" wrote in message ... OK, TM seems to be happy for you to proceed... Dave. "T. Valko" wrote: And if you know that... Sure do! perhaps you'll also know how to explain the whole thing to me. My specialty, but I'll wait to see if TM responds. I would be interested to read their explanation of both questions. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Looking at an answer from Teethless Mama to someone else's question, raised yet another question. (what a surprise) The formula was: =OR(INDEX(A1:A3=B2,)) I was curious about the use of Index with a blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? And if you know that, perhaps you'll also know how to explain the whole thing to me. Regards - Dave. . |
#8
|
|||
|
|||
Index from TM
Wow! If that's *all* you've got, then I've got nothing!
Thanks for your time and effort. The empty argument equating to zero, and the zero then returning the entire array, is the secret to it - I say secret, because it's not immediately obvious - not to me anyway. And the intersection of my chosen cell and the A1:A9 range was an interesting red herring. You are a very patient explainer. Regards - Dave. "T. Valko" wrote: I was curious about the use of Index with a blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? Enter these values in A1:A9 - 10,20,30,40,50,60,70,80,90 The reason you got the result from cell A3 is because you (initially) entered the formula in some cell on ROW 3. Try this, enter the formula in cell C1 and copy down to C9. Notice the pattern of results. Now, enter the formula in cell A15. What happened? Why is it now returning an error? Let's start with a general overview of function arguments then we'll get into the mechanics of the formula(s). Functions return results based on what the particular function does and based on the arguments the function processes. Some arguments are mandatory and some are optional depending on the particular function. If you have Excel version 2002 or later and you have Function Tool Tips enabled, when you start typing in a formula Excel shows you the syntax of the functions used in the formula. For INDEX it would look like this: INDEX(array,row_num,[column_num]) INDEX(reference,row_num,[column_num],[area_num]) The arguments are separated by commas (or semi-colons depending on your regional settings) and the optional arguments, if any, are enclosed in the [ ] brackets. These arguments can be either defined, empty or omitted. A defined argument is simply an argument that specifies the conditions or parameters of that argument. For example: =INDEX(A1:A9,5) 5 is the row_num argument so that argument is defined. An empty argument is one that has been "allocated" but hasn't been defined. As noted before, arguments are separated by commas so, if a comma appears then it is assumed that the argument has been "allocated" and what follows the comma is an argument. If the argument has been "allocated" but it isn't defined then it's evaluated as being empty. So, in your test formula: =INDEX(A1:A9,) The array_ argument has been defined, A1:A9. It's followed by a comma meaning that what follows is an argument, the row_num argument. The row_num argument has been "allocated" but it's not defined so it's empty. An empty argument will be evaluated as 0. If we were to include that argument: =INDEX(A1:A9,0) =INDEX(A1:A9,) Both of those do/mean the same thing. Now we need to understand omitted arguments. Omitted arguments are the [optional_arguments] that some functions have. If you don't use the optional arguments some of them have default parameters. That is, even if you don't define the optional argument it will evaluate to a default setting. These parameters and the default setting depend on the specific function. Let's look at the test INDEX function again and compare it to the syntax: INDEX(array,row_num,[column_num]) INDEX(A1:A9,) In the test formula we've got the defined array_argument and the empty row_num argument, both of which are mandatory arguments. We didn't use the optional column_num argument so in this application it defaults to 0. OK, now let's take a look at the mechanics of the formula. INDEX(A1:A9,) INDEX(A1:A9,0) In this application we're using a one dimensional array as the array_argument. The indexed array values are considered to be in a relative sequential position. That is: A1 = position 1 A2 = position 2 A3 = position 3 A4 = position 4 etc etc A9 = position 9 If the indexed array was C22:C25 the relative sequential positions would be the same: C22 = position 1 C23 = position 2 C24 = position 3 C25 = position 4 Typically, we use INDEX to return a value from the array_argument and we use the row_num and/or the column_num argument to define which value from the array we want returned. When the array is a vertical one dimensional array (as in the test formula) we would use the row_num argument to define which value we want. =INDEX(A1:A9,5) Return the value from position 5 of the range (indexed array) A1:A9. Ok, that's pretty simple. So, how do these empty arguments come into play here and what exactly do they do? Remember, an empty argument evaluates to 0. However, if the indexed array is considered to have relative sequential positions starting with 1, what does 0 do? There is no relative position 0. In this application 0 means to return the *entire* indexed array. So: =INDEX(A1:A9,) Returns the entire array of values from A1:A9. Since the formula is returning an array of values we need either another function that can handle an array of values for further processing or we need to enter the formula in an array of cells. However, if the formula is entered in a single cell that cell can only hold a single result. This is what you did, entered the formula in a single cell. Depending on where the formula cell is located and how the formula was entered will determine what result the test formula returns. This is the factor that led to you entering the formula in a cell and getting the value form A3. You had to have initially entered the formula in a cell on ROW 3. Because the formula, as written, is returning an array of results but wasn't using another function to further process the array of results, and/or the formula wasn't entered in an array of cells, you got the result that corresponds to the implicit intersection of the indexed array and the location of the formula cell. Let's assume the formula was normally entered in cell C3. Cell A3 is within the indexed array and cell C3 forms a direct intersection to cell A3. So, the result of the formula is the value from cell A3. This is the reason you got the results you did when I suggested (at the very beginning of this post) you enter the formula in C1 and copy down to C9. The formula entered in A15 returned an error because cell A15 is not within an implicit intersection of the indexed array *and* the formula was not entered as an array. If you initially enter the formula within an implicit intersection and then move the formula by dragging to a location outside the implicit intersection the result will be the same until Excel recalculates. Ok, now let's look at TM's formula and see how it works. Let's assume this is the data: A1 = 1 A2 = 2 A3 = 3 B2 = 3 =OR(INDEX(A1:A3=B2,)) A1:A3=B2 will return an array of either TRUE or FALSE: 1 = 3 = FALSE 2 = 3 = FALSE 3 = 3 = TRUE These logical values make up the array_argument to INDEX: INDEX({FALSE;FALSE;TRUE},) Because the row_num argument is empty it's evaluated as 0 so INDEX returns the *entire* array of logical values. The array of logical values is passed to the OR function: OR({FALSE;FALSE;TRUE}) OR can handle arrays so this is why the formula doesn't have to be array entered**. OR evaluates the array of logical values and if *any* of them are TRUE then the result of OR = TRUE. If none of the logical values is TRUE then OR = FALSE. So: =OR(INDEX(A1:A3=B2,)) =TRUE Array entered vs. normally entered... The test formula as written is returning an array of results: INDEX(A1:A9,) As noted before we either have to pass those results to another function for further processing or array enter** the formula in an array of cells the same size as the indexed range A1:A9 = 9 cells. To array enter the formula we would select the range of cells say, C1:C9, type the formula =INDEX(A1:A9,) but instead of hitting the Enter key like you normally would we would use a combination of keys, CTRL,SHIFT,ENTER. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key. With this particular formula the results would be the same as normally entering the formula (just hit Enter) in cell C1 and drag copying down to C9. Now, remember that formula that's in cell A15? It's currently returning an error because the formula is returning an array of results but the formula is in a single cell and this cell is not within an implicit intersection. Try this... Select cell A15 Hit function key F2 Now, array enter the formula. Hold down both the CTRL key and the SHIFT key then hit the ENTER key. See what happens? The formula now returns the *first* result of the indexed array. Well, that's all I got! exp101 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, this is going to take me a while (real slow typer!) so check back later this evening. -- Biff Microsoft Excel MVP "Dave" wrote in message ... OK, TM seems to be happy for you to proceed... Dave. "T. Valko" wrote: And if you know that... Sure do! perhaps you'll also know how to explain the whole thing to me. My specialty, but I'll wait to see if TM responds. I would be interested to read their explanation of both questions. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Looking at an answer from Teethless Mama to someone else's question, raised yet another question. (what a surprise) The formula was: =OR(INDEX(A1:A3=B2,)) I was curious about the use of Index with a blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? And if you know that, perhaps you'll also know how to explain the whole thing to me. Regards - Dave. . . |
#9
|
|||
|
|||
Index from TM
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Dave" wrote in message ... Wow! If that's *all* you've got, then I've got nothing! Thanks for your time and effort. The empty argument equating to zero, and the zero then returning the entire array, is the secret to it - I say secret, because it's not immediately obvious - not to me anyway. And the intersection of my chosen cell and the A1:A9 range was an interesting red herring. You are a very patient explainer. Regards - Dave. "T. Valko" wrote: I was curious about the use of Index with a blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? Enter these values in A1:A9 - 10,20,30,40,50,60,70,80,90 The reason you got the result from cell A3 is because you (initially) entered the formula in some cell on ROW 3. Try this, enter the formula in cell C1 and copy down to C9. Notice the pattern of results. Now, enter the formula in cell A15. What happened? Why is it now returning an error? Let's start with a general overview of function arguments then we'll get into the mechanics of the formula(s). Functions return results based on what the particular function does and based on the arguments the function processes. Some arguments are mandatory and some are optional depending on the particular function. If you have Excel version 2002 or later and you have Function Tool Tips enabled, when you start typing in a formula Excel shows you the syntax of the functions used in the formula. For INDEX it would look like this: INDEX(array,row_num,[column_num]) INDEX(reference,row_num,[column_num],[area_num]) The arguments are separated by commas (or semi-colons depending on your regional settings) and the optional arguments, if any, are enclosed in the [ ] brackets. These arguments can be either defined, empty or omitted. A defined argument is simply an argument that specifies the conditions or parameters of that argument. For example: =INDEX(A1:A9,5) 5 is the row_num argument so that argument is defined. An empty argument is one that has been "allocated" but hasn't been defined. As noted before, arguments are separated by commas so, if a comma appears then it is assumed that the argument has been "allocated" and what follows the comma is an argument. If the argument has been "allocated" but it isn't defined then it's evaluated as being empty. So, in your test formula: =INDEX(A1:A9,) The array_ argument has been defined, A1:A9. It's followed by a comma meaning that what follows is an argument, the row_num argument. The row_num argument has been "allocated" but it's not defined so it's empty. An empty argument will be evaluated as 0. If we were to include that argument: =INDEX(A1:A9,0) =INDEX(A1:A9,) Both of those do/mean the same thing. Now we need to understand omitted arguments. Omitted arguments are the [optional_arguments] that some functions have. If you don't use the optional arguments some of them have default parameters. That is, even if you don't define the optional argument it will evaluate to a default setting. These parameters and the default setting depend on the specific function. Let's look at the test INDEX function again and compare it to the syntax: INDEX(array,row_num,[column_num]) INDEX(A1:A9,) In the test formula we've got the defined array_argument and the empty row_num argument, both of which are mandatory arguments. We didn't use the optional column_num argument so in this application it defaults to 0. OK, now let's take a look at the mechanics of the formula. INDEX(A1:A9,) INDEX(A1:A9,0) In this application we're using a one dimensional array as the array_argument. The indexed array values are considered to be in a relative sequential position. That is: A1 = position 1 A2 = position 2 A3 = position 3 A4 = position 4 etc etc A9 = position 9 If the indexed array was C22:C25 the relative sequential positions would be the same: C22 = position 1 C23 = position 2 C24 = position 3 C25 = position 4 Typically, we use INDEX to return a value from the array_argument and we use the row_num and/or the column_num argument to define which value from the array we want returned. When the array is a vertical one dimensional array (as in the test formula) we would use the row_num argument to define which value we want. =INDEX(A1:A9,5) Return the value from position 5 of the range (indexed array) A1:A9. Ok, that's pretty simple. So, how do these empty arguments come into play here and what exactly do they do? Remember, an empty argument evaluates to 0. However, if the indexed array is considered to have relative sequential positions starting with 1, what does 0 do? There is no relative position 0. In this application 0 means to return the *entire* indexed array. So: =INDEX(A1:A9,) Returns the entire array of values from A1:A9. Since the formula is returning an array of values we need either another function that can handle an array of values for further processing or we need to enter the formula in an array of cells. However, if the formula is entered in a single cell that cell can only hold a single result. This is what you did, entered the formula in a single cell. Depending on where the formula cell is located and how the formula was entered will determine what result the test formula returns. This is the factor that led to you entering the formula in a cell and getting the value form A3. You had to have initially entered the formula in a cell on ROW 3. Because the formula, as written, is returning an array of results but wasn't using another function to further process the array of results, and/or the formula wasn't entered in an array of cells, you got the result that corresponds to the implicit intersection of the indexed array and the location of the formula cell. Let's assume the formula was normally entered in cell C3. Cell A3 is within the indexed array and cell C3 forms a direct intersection to cell A3. So, the result of the formula is the value from cell A3. This is the reason you got the results you did when I suggested (at the very beginning of this post) you enter the formula in C1 and copy down to C9. The formula entered in A15 returned an error because cell A15 is not within an implicit intersection of the indexed array *and* the formula was not entered as an array. If you initially enter the formula within an implicit intersection and then move the formula by dragging to a location outside the implicit intersection the result will be the same until Excel recalculates. Ok, now let's look at TM's formula and see how it works. Let's assume this is the data: A1 = 1 A2 = 2 A3 = 3 B2 = 3 =OR(INDEX(A1:A3=B2,)) A1:A3=B2 will return an array of either TRUE or FALSE: 1 = 3 = FALSE 2 = 3 = FALSE 3 = 3 = TRUE These logical values make up the array_argument to INDEX: INDEX({FALSE;FALSE;TRUE},) Because the row_num argument is empty it's evaluated as 0 so INDEX returns the *entire* array of logical values. The array of logical values is passed to the OR function: OR({FALSE;FALSE;TRUE}) OR can handle arrays so this is why the formula doesn't have to be array entered**. OR evaluates the array of logical values and if *any* of them are TRUE then the result of OR = TRUE. If none of the logical values is TRUE then OR = FALSE. So: =OR(INDEX(A1:A3=B2,)) =TRUE Array entered vs. normally entered... The test formula as written is returning an array of results: INDEX(A1:A9,) As noted before we either have to pass those results to another function for further processing or array enter** the formula in an array of cells the same size as the indexed range A1:A9 = 9 cells. To array enter the formula we would select the range of cells say, C1:C9, type the formula =INDEX(A1:A9,) but instead of hitting the Enter key like you normally would we would use a combination of keys, CTRL,SHIFT,ENTER. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key. With this particular formula the results would be the same as normally entering the formula (just hit Enter) in cell C1 and drag copying down to C9. Now, remember that formula that's in cell A15? It's currently returning an error because the formula is returning an array of results but the formula is in a single cell and this cell is not within an implicit intersection. Try this... Select cell A15 Hit function key F2 Now, array enter the formula. Hold down both the CTRL key and the SHIFT key then hit the ENTER key. See what happens? The formula now returns the *first* result of the indexed array. Well, that's all I got! exp101 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Ok, this is going to take me a while (real slow typer!) so check back later this evening. -- Biff Microsoft Excel MVP "Dave" wrote in message ... OK, TM seems to be happy for you to proceed... Dave. "T. Valko" wrote: And if you know that... Sure do! perhaps you'll also know how to explain the whole thing to me. My specialty, but I'll wait to see if TM responds. I would be interested to read their explanation of both questions. -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi, Looking at an answer from Teethless Mama to someone else's question, raised yet another question. (what a surprise) The formula was: =OR(INDEX(A1:A3=B2,)) I was curious about the use of Index with a blank 2nd argument. So breaking it down, I entered data into cells A1 thru A9, then in another cell entered: =INDEX(A1:A9,) This always returned the data from the 3rd cell (A3) Can anyone tell me why please? And if you know that, perhaps you'll also know how to explain the whole thing to me. Regards - Dave. . . |
Thread Tools | |
Display Modes | |
|
|