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, 22, 0)
= C2
= 1955
D4: OFFSET($C$2, SMALL(IF((List)>1,ROW(List),”"), ROW(E1))2, 0)
= OFFSET($C$2, 62, 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, 72, 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
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.
 
 

Leave a Reply
14 ways to check if an year is leap year, using Excel [just for fun]  Do you use Pivot Tables? What do you use them for & where do you struggle [Survey] 
12 Responses to “Formula Forensic 014 – Faseeh’s Formula”
Very Cool!
And you can use the SMALL function to then sort the results.
=IFERROR(SMALL(OFFSET(List,0,1),ROW(E2)),”…”)
Use as an array function, and copy down.
Oops… forgot the ROW(E2)1
Here’s the correction:
=IFERROR(SMALL(OFFSET(List,0,1),ROW(E2)1),”…”)
Hi, Faseeh!
Congratulations for deserving a Formula Forensic article.
My best regards!
@SirJB7,
Thank You Sir!
….and thanks to Hui & Oldchippy for explaining & referring it!!
Yours truly
Faseeh
What I usually do is I add another row where I put sequential numbers from 1 to n (even for the blanks).
Select both rows and sort descending by the data row.
Select both rows again, but just where the data is, and sort ascending by sequential number.
Very neat!
Just one question, why wouldn’t “Skip Blank” (under paste special) work? I tried copying the data with blank data, paste special as skip blank but it didn’t work.
I have seen this “Skip Blank” and I thought this would be the place to go but never have a real life chance of using it.
Could someone shed some light on this please? Thanks.
Well, Skip Blank might work, but the cool thing about the formula is that, as you add data to your table (and if your table is set up correctly to expand with new rows) Faseeh’s formula is added in automatically and the blanks are automatically put to the bottom of the list. No need to do any key strokes and repaste in the column of data! Same thing with my use of SMALL() to sort the column.
This is real useful in dashboards. You don’t want to have to go in every time you add data and recreate the dashboard by doing a whole bunch of keystrokes.
Great formula!
And here is the same formula tweaked to avoid the volatile OFFSET function:
=IFERROR(INDEX(A:A,SMALL(IF((list)>0,ROW(list)),ROW(B1))),”…”)
entered using Ctrl+Shift+Enter
(The formula assumes that the list with blanks is in column A, and that this formula is put in column B, starting in cell B1.)
Cheers,
Sajan.
Hi,
Its very nice way of explaining and thanks Chandoo and Faseeh.
I have a query. When I’m using IF(List>1, ROW(List),”") , I am always getting 2 as output. Can you help me where I must be going wrong?
THanks
Krishna
Thanks Faseeh,
I’m using this formula to filter out gaps in a set of data so that I can use the “RSQ” and “LINEST” functions for nonlinear trendline coefficients.
I have managed to use your example to condense the data and remove all the gaps. Any suggestions how to set up a formula that recognizes the “end” of the dataset (i.e, where the data stops)?
For example, how could I modify the general formula below to determine the RSQ for a logarithmic trend of a set of data which I have just condensed with the “Faseeh” formula?
RSQ (logarithmic trend) = RSQ(Y,LN(X))
Really appreciate your help.
Aleks
@Aleks
Normally a formula like:
Xvalues: =Offset(A1,,,counta(a:a),1)
can be used to setup a range of all the data
same for Y
=Offset(B1,,,counta(b:b),1)
or you can offset from the first
=Offset(Xvalues,,1)
Great formula !! Thanks for it and these clear explanations
But I have a small problem with it: if a cell in column D has a value of 1 then it is considered as blank; if you use List>=1 then 0 is considered as blank… Am I wrong somewhere?
Thanks for your help !