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 |
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 |