Formula Forensic 014 – Faseeh’s Formula

Formula Forensic 014 – Faseeh’s Formula:
In early February Subhapratimdas asked a question at the Chandoo.org Forums.
“I have a large list which includes blanks, I want to retrieve the list without the blanks“
Faseeh, responded with a neat array formula
=IFERROR(OFFSET($C$2,SMALL(IF((List)>1,ROW(List),”"),ROW(E1))-2,0),”…”)
Old Chippy thought it was neat and nominated it for a Forensic examination.
So today we will pull Faseeh’s Formula apart to see what’s inside.

Faseeh’s Formula

As usual we will work through this formula using a sample file for you to follow along. Download Here.
Faseeh’s formula is an Array Formula:
=IFERROR(OFFSET($C$2,SMALL(IF((List)>1,ROW(List),”"),ROW(E1))-2,0),”…”)
This is the formula taken from Cell D2. This is important but we will come back to it later.
A quick look shows that it is an Offset Formula surrounded in an IFERROR wrapper
=IFERROR(OFFSET($C$2,SMALL(IF((List)>1,ROW(List),”"),ROW(E1))-2,0),”…”)
The IFERROR() function is a new function introduced in Excel 2007. Its use it to look at its first component and return its value, but if the first component returns an error then return the value in the second component, in this case “…”
So the real formula is
OFFSET($C$2,SMALL(IF((List)>1,ROW(List),”"),ROW(E1))-2,0)
And if this returns an error, then IFERROR will kick in and return a “…” instead of an error.
Lets look at the main part
OFFSET($C$2,SMALL(IF((List)>1,ROW(List),”"),ROW(E1))-2,0)
The OFFSET function has the syntax
OFFSET(reference, rows, cols, [height], [width])
In our example
OFFSET($C$2,SMALL(IF((List)>1,ROW(List),”"),ROW(E1))-2,0)
So the Offset Formula is using
Reference: $C$2
Rows: SMALL(IF((List)>1,ROW(List),”"),ROW(E1))-2
Columns: 0
So Offset is looking at a Reference of C2 and then looking down SMALL(IF((List)>1, ROW(List), “”), ROW(E1))-2 rows and across 0 cells to return a new reference.

So what is SMALL(IF((List)>1,ROW(List),”"),ROW(E1))-2 doing.
Next thing to notice is that there is a Named Formula “List” used twice in the formula
Looking at the name manager (Keyboard Shortcut – Ctrl F3), we can see that List is a straight range reference to C2:C10000
List : =Sheet1!$C$2:$C$10000
Back to
SMALL(IF((List)>1,ROW(List),”"),ROW(E1))-2
If we highlight the components
SMALL(IF((List)>1,ROW(List),”"),ROW(E1))-2
We can now see that the formula is using the SMALL() function
The syntax of the SMALL function is:
SMALL(array, k)
So this shows that the SMALL(IF((List)>1,ROW(List),”"),ROW(E1))-2
Will get the item number Row(E1) from the array IF((List)>1,ROW(List),”") and then subtract 2 from it.
The Row(E1) is important as you will remember way back at the start I mentioned that this is the formula from D2, so Row(E1) is getting the Row number 1 cell above the current Row.
This means that in Row 2, it is getting the 1st item (Row 1) from the Small function, in Row 3 it is getting the 2nd and in row 10 it is getting the 9th item from the Small function, etc.
So what array is Small() looking up?
IF((List)>1,ROW(List),”")
This is a very neat and quick method of setting up an array
The Named Formula “List” is an Array which has received the values from Sheet1!$C$2:$C$10000, so it is a 9999 rows by 1 column array
A quick way to check this is to enter in F5:F10, =List>1 and press Ctrl Enter

When the corresponding row has a Value > 1, =List>1 returns TRUE
When the corresponding row has a Blank cell, =List>1 returns FALSE
Stepping out
=IF((List)>1,ROW(List),”")
If we step out to the If formula we see that if ( the value in the Array >1, put the Row(List) else “”

So in H2:H10 array enter =IF((List)>1,ROW(List),”") Ctrl Shift Enter
You can see that this is creating an Array of the Row Numbers that aren’t blank
Back to our SMALL() function
SMALL(IF((List)>1, ROW(List), “”), ROW(E1))-2
This is now reading like, return the smallest Row(Current Row -1) from the array of items which has removed the blanks

So If we step out again, and look at 3 cells: D2:D4
D2 will return the 1^st smallest Number from the array Row(E1), which is a Value of 2
D3 will return the 2^nd smallest Number from the array Row(E2), which is a Value of 6
D4 will return the 3^rd smallest Number from the array Row(E2), which is a Value of 7
But these values are the Row Offset in the original Offset function
OFFSET($C$2, SMALL(IF((List)>1,ROW(List),”"), ROW(E1))-2, 0)
So in:
D3: OFFSET($C$2, SMALL(IF((List)>1,ROW(List),”"), ROW(E1))-2, 0)
= OFFSET($C$2, 2-2, 0)
= C2
= 1955
D4: OFFSET($C$2, SMALL(IF((List)>1,ROW(List),”"), ROW(E1))-2, 0)
= OFFSET($C$2, 6-2, 0)
= OFFSET($C$2, 4, 0)
= C6
=730
D5:      OFFSET($C$2, SMALL(IF((List)>1,ROW(List),”"), ROW(E1))-2, 0)
= OFFSET($C$2, 7-2, 0)
= OFFSET($C$2, 5, 0)
= C7
= 318
Once the formula tries to retrieve values past the end of the data the formula returns an error and the IFERROR() function returns a “…”

Download

You can download a copy of the above file and follow along, Download Here.

Formula Forensics “The Series”

You can learn more about how to pull Excel Formulas apart in the following posts
Formula Forensic Series

We Need Your Help

I have received a few more ideas since last week and these will feature in coming weeks.
I do need more ideas though 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.