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.

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.