Over Easter, while we were all busy eating our Easter Eggs, over at the Chandoo.org/Forums, Slk213 was worried about how to retrieve the Nth number from a row of data which had gaps in it.
“I am trying to pull the Nth number in a range of numbers.
I have a range of scores from G3 to L3. I am trying to create a formula in cells B3 thru F3. In cell B3, I am looking for the 1st score in range G3 thru L3 excluding blank spaces which would be 45. In C3 I am looking for the 2nd number in range G3 thru L3 excluding blanks spaces which would be 44. This would continue thru finding the 5th number.”
Luckily I had finished my easter eggs and dropped a quick answer in for Slk213.
=INDEX($A$3:$L$3,,SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1))) Ctrl Shift Enter
So today on Formula Forensics we will look at how this formula jumps over gaps in a range to retrieve the next item from the range, as quick as you can finish your Easter Egg.
Retrieving the Nth Number.
As with all Formula Forensics posts you can follow along using a worked example: Download Here.Todays formula to retrieve the Nth Number from a list with gaps is:
=INDEX($A$3:$L$3,,SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1))) Ctrl Shift Enter
We know that Index looks up a Range/Array and retrieves a value from a position in that Range/Array
The Index function has the syntax:
In our formula:
=INDEX($A$3:$L$3,,SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)))
Array: $A$3:$L$3
Row_Num: Nul or Blank = Same Row
Column_Num: SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1))
We can see from above that the Array is a single Row, Row 3, from A3 to L3
And we can see that the Index function Is looking up in the same row as the Row_Num is blank.
So all the work occurs in the Column_Num field of the Index function.
Lets have a look at the Column_Num function:
SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1))
We can see that it is made up of a Small Function
SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1))
The Small function has the syntax
In our example
Array: IF($G3:$L3<>”",COLUMN($G3:$L3))
k: COLUMN()-COLUMN($A$1)
Lets look at the array function IF($G3:$L3<>”",COLUMN($G3:$L3))
In a spare cell B14 enter = IF($G3:$L3<>”",COLUMN($G3:$L3)) press F9 not Enter
Excel responds with ={7,8,9,FALSE,11,12}
This is the Array answer to the formula IF($G3:$L3<>”",COLUMN($G3:$L3))
This can be read:
If the value in the Range G3:L3 is not Blank, return the value of the True component of the If. In this case it is the formula =COLUMN($G3:$L3).
As this is an array formula It will return the 1st value from the True statement COLUMN($G3:$L3) for the first value of the If Function.
It will return the 2nd value from the True statement COLUMN($G3:$L3) for the 2nd value of the If Function. etc
If the value in the Range G3:L3 is Blank, return the value of the False component of the If, which is Blank and so If will return False
From the Excel response ={7,8,9,FALSE,11,12}
We can see that the 1st, 2nd, 3rd, 5th and 6th values are not Blank and so the Column No is returned ie: 7,8,9,11 & 12.
The 4th value in G3:L3 is Blank and so Excel has returned False, as J3=”" (Blank)
If we jump back to the Small function
SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1))
and substitute the array for the If function
The small function is now
SMALL({7,8,9,FALSE,11,12},COLUMN()-COLUMN($A$1))
The second component of the Small function is k
k is the location in the array that you want returned.
In this example k = COLUMN()-COLUMN($A$1)
This is the same as saying
k = Current Column – Column A1
k = Current Column – 1
We need to note that the location of the formula is important as it is measuring the offset using the position of the formula compared to Column A.
Slk213 wanted the formula in B3:F3
So the first cell B3 will return the value of
k = COLUMN()-COLUMN($A$1)
k = COLUMN(B3)-COLUMN(A1)
k = 2 – 1
k = 1
So in Cell B3 the Small function will return the smallest value from the array, which is the lowest column Number or 7
In Cell C3 the Small function will return the 2nd smallest value from the array, which is the 2nd lowest column Number of 8
In Cell D3 the Small function will return the 3rd smallest value from the array, which is the lowest column Number or 9
In Cell E3 the Small function will return the 4th smallest value from the array, which is the 4th lowest column Number of 11
Note: By going from D3 to E3 we have skipped over the Blank cell which has a value in the array of False
We can now look at this Small component
In a spare cell B16 enter
=SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)) Press F9 not Enter,
Excel will respond with a ={7}
Copy the formula to the adjacent cells C16:E16 and evaluate each with F9
Now in B18 try the following
=SMALL({7,8,9,FALSE,11,12},COLUMN()-COLUMN($A$1))
Press F9 not Enter, Excel will respond with a ={7}
Copy the formula to the adjacent cells C18:E18 and evaluate each with F9
Because the formula =SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)) is returning an array as an answer and the array answer is dependent on its location on the worksheet.
We can now use this as the lookup value from the original Index formula.
=INDEX($A$3:$L$3,,SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)))
Remembering that SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)) is dependent on the position of the formula we can substitute the array answers for the Small function
That is in B3:
=INDEX($A$3:$L$3,,SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)))
=INDEX($A$3:$L$3,,{7})
So Index will return the 7th value from the Range A3:L3, which is G3 or 45
In D3:
=INDEX($A$3:$L$3,,SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)))
=INDEX($A$3:$L$3,,{9})
So Index will return the 9th value from the Range A3:L3, which is I3 or 43
In E3:
=INDEX($A$3:$L$3,,SMALL(IF($G3:$L3<>”",COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)))
=INDEX($A$3:$L$3,,{11})
So Index will return the 11th value from the Range A3:L3, which is K3 or 42
Effectively skipping over the blank cells.
Download
You can download a copy of the above file and follow along here: Download Here.Formula Forensics “The Series”
You can learn more about how to pull Excel Formulas apart in the following postsFormula Forensic Series
Formula Forensics Needs Your Help
I urgently need more ideas for future Formula Forensics posts and so I need your help.If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post like above or;
If you have a formula that you would like explained but don’t want to write a post also send it to Chandoo or Hui.
0 comments:
Post a Comment