Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Unexpected result
Hi Folks!
Can someone explain the result I'm getting: A1 = 11 A2 = 12 A3 = 13 A4 = 14 A5 = 15 A10 = empty I want the formula to extract the values in A1:A5 IF A10 = X Formula: (array entered) =INDEX(A$1:A$5,SMALL(IF(A$10="X",ROW($1:$5)),ROW(1 :1))) Copied down 5 cells returns: 11 #NUM! #NUM! #NUM! #NUM! I should get #NUM! in every cell. Here's where I don't understand the result of the first cell return of 11.... Evaluating the formula and stepping through: SMALL(FALSE,{1}) evaluates to SMALL(0,1) = 0 So, =INDEX(A$1:A$5,0) Returns the value in the first position in the array A1:A5, 11. =INDEX(A$1:A$5,1) also returns the value in the first position in the array A1:A5, 11. I would think that there is no zero position in the array and the formula should error. I know that the above #NUM! errors are being generated by the SMALL function but shouldn't INDEX also generate an error based on position zero? I'm confiussed on this! Thanks Biff |
#2
|
|||
|
|||
Hi Biff, Try entering the following in a cell =INDEX(A1:A5,0) Case 1: as an array formula, and Case 2: as a simple formula In the first case, it returns 11 (and if you drag down, the subsequent numbers). In the second case, it returns #VALUE and the same for dragging down. In you main formula, the above part is treated as an array formula and so you get 11 in the first case, whereas in the subsequent cases instead of 0 you get #NUM as the second argument for the index function and so you get #NUM Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=375888 |
#3
|
|||
|
|||
Hi Biff, your question and the solution aaray did not match. Why don't you simply use: =IF($A$10="X",A1,"") and copy down or =IF($A$10="X",A1:A5,"") as an array formula by selecting all the five cells in the column. Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=375888 |
#4
|
|||
|
|||
=INDEX(A1:A5,0)
which is identical to the full version: =INDEX(A1:A5,0,1) means all of the rows of A1:A5. The formula cell will house the result given your sample: ={11;12;13;14;15} with the topleft cell displaying. The foregoing also holds for: =INDEX(A1:A5,{0}) Intermezzo: Invoke =INDEX($A$1:$A$5,0) in a cell in the same worksheet you want to data validate as Source. That cell will show you all of the values from A1:A5. BTW, ROW(1:1) in =INDEX(A$1:A$5,SMALL(IF(A$10="X",ROW($1:$5)),ROW(1 :1))) makes the formula non-robust (therefore incorrect) against row insertions before the formula row. Biff wrote: Hi Folks! Can someone explain the result I'm getting: A1 = 11 A2 = 12 A3 = 13 A4 = 14 A5 = 15 A10 = empty I want the formula to extract the values in A1:A5 IF A10 = X Formula: (array entered) =INDEX(A$1:A$5,SMALL(IF(A$10="X",ROW($1:$5)),ROW(1 :1))) Copied down 5 cells returns: 11 #NUM! #NUM! #NUM! #NUM! I should get #NUM! in every cell. Here's where I don't understand the result of the first cell return of 11.... Evaluating the formula and stepping through: SMALL(FALSE,{1}) evaluates to SMALL(0,1) = 0 So, =INDEX(A$1:A$5,0) Returns the value in the first position in the array A1:A5, 11. =INDEX(A$1:A$5,1) also returns the value in the first position in the array A1:A5, 11. I would think that there is no zero position in the array and the formula should error. I know that the above #NUM! errors are being generated by the SMALL function but shouldn't INDEX also generate an error based on position zero? I'm confiussed on this! Thanks Biff |
#5
|
|||
|
|||
Thanks for your input.
Actually, I was just "playing" around with some stuff and couldn't quite figure out what I was seeing. Biff "mangesh_yadav" wrote in message news:mangesh_yadav.1pzm2e_1117695909.6097@excelfor um-nospam.com... Hi Biff, your question and the solution aaray did not match. Why don't you simply use: =IF($A$10="X",A1,"") and copy down or =IF($A$10="X",A1:A5,"") as an array formula by selecting all the five cells in the column. Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=375888 |
#6
|
|||
|
|||
Ok, that makes sense!
Biff "Aladin Akyurek" wrote in message ... =INDEX(A1:A5,0) which is identical to the full version: =INDEX(A1:A5,0,1) means all of the rows of A1:A5. The formula cell will house the result given your sample: ={11;12;13;14;15} with the topleft cell displaying. The foregoing also holds for: =INDEX(A1:A5,{0}) Intermezzo: Invoke =INDEX($A$1:$A$5,0) in a cell in the same worksheet you want to data validate as Source. That cell will show you all of the values from A1:A5. BTW, ROW(1:1) in =INDEX(A$1:A$5,SMALL(IF(A$10="X",ROW($1:$5)),ROW(1 :1))) makes the formula non-robust (therefore incorrect) against row insertions before the formula row. Biff wrote: Hi Folks! Can someone explain the result I'm getting: A1 = 11 A2 = 12 A3 = 13 A4 = 14 A5 = 15 A10 = empty I want the formula to extract the values in A1:A5 IF A10 = X Formula: (array entered) =INDEX(A$1:A$5,SMALL(IF(A$10="X",ROW($1:$5)),ROW(1 :1))) Copied down 5 cells returns: 11 #NUM! #NUM! #NUM! #NUM! I should get #NUM! in every cell. Here's where I don't understand the result of the first cell return of 11.... Evaluating the formula and stepping through: SMALL(FALSE,{1}) evaluates to SMALL(0,1) = 0 So, =INDEX(A$1:A$5,0) Returns the value in the first position in the array A1:A5, 11. =INDEX(A$1:A$5,1) also returns the value in the first position in the array A1:A5, 11. I would think that there is no zero position in the array and the formula should error. I know that the above #NUM! errors are being generated by the SMALL function but shouldn't INDEX also generate an error based on position zero? I'm confiussed on this! Thanks Biff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert value in word. For Exampe Rs.115.00 convert into word as . | Excel Discussion (Misc queries) | |||
How can i change this VBA project According to Indian Numeric | Excel Discussion (Misc queries) | |||
Conversion | Excel Worksheet Functions | |||
Amount or Numbers in Words | New Users to Excel | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions |