Explain a formula

[Pofski]

Hello,

i was wondering if somebody could explain a formula to me.
I have been using this formula for almost a year now, and to be honest, whenever i have to re use it, i often wonder how it works.

The formula i am speaking of is:

{=INDEX($C$67:$C$121,SMALL(IF($C$67:$C$121<>0,ROW($C$67:$C$121),1000),ROW()-ROW($C$67)+1)-ROW($C$67)+1)}

What it does is the following. It's an array formula, and it gives a list of all the results in a certain area that are different from the stated amount (0 in this instance)

Now, although i have used this formula several times now, i can not get to understand it.

For example, The formula works great, if it starts in the same row as it describes (in this case, row 67) If you pull it down, starting at that row, you get all the correct results. But what if i want to change its location, and put it in row 5 for example.

Further more, what if i want the result that is next to the result (lets say from column B)

If anybody could break this appart for me, i would be really gratefull.
Sincerely,

 

[Somendra Misra]

Hi Pofski,

First you must understand that INDEX() is a lookup function, i.e. it can be use to extract data from a range (One or Two dimensional like VlookUP). Now INDEX() require basically 3 arguments.
1. A range
2. Row Number in that range
3. Column Number in that range

Now coming to your formula:
{=INDEX($C$67:$C$121,SMALL(IF($C$67:$C$121<>0,ROW($C$67:$C$121),1000),ROW()-ROW($C$67)+1)-ROW($C$67)+1)}


Here in this Blue is one dimensional range of data from where you are extracting your data C67:C121.

Red part is for getting row number.
Now we are using SMALL() function to get row number. SMALL() has two argument array and which number small you want.

So, wherever your range value is not equal to 0 it will give row number of that row number and if it is 0 or blank it will return 1000. Now here you can get the array ranging from 67 to 121 or anything in between 67 to 121 and some 1000.

Now ROW()-ROW($C$67)+1 this will return 1, 2,3...if you enter in row 67 (67-67+1)=1 and so on.

So your SMALL function will give you 67,68 and so on . Now from this you will deduct ROW(C67) and than add 1, so finally you will get 1, 2 and so on.

Now the problem with this construct is if you put this in say row 5 you will get error because than it will 5-67+1 which is negative.

SO to overcome this try using below formula.

=INDEX(C$67:C$121,SMALL(IF(C$67:C$121<>0,ROW(C$67:C$121)-ROW(C$67)+1),ROWS(C$67:C67)))

This you can use starting from any row.

Now if you drag this formula to the left you will get Column B data.

Hope this will clear your doubt.

Just advise if any issue in this.

Regards,

 

[Pofski]

Fantastic Somendra,

now i understand this.
And your code is actually a lot simpler.

Thank you

 

[Somendra Misra]

Welcome back anytime.