Hi Nazim ,
I am in the process of explaining this to another person , and I have got as far as this ; please note that this does not refer specifically to your file , and hence the addresses may be different :
If you split it up into its components , it becomes straightforward ; you already know that the INDEX function returns the individual elements from a column or row range , depending on the index number which is specified as its second parameter.
For example , suppose you have a list of names , and you call it Names , referring to a range , say J16:J33.
Now , if you put the following formula in a cell :
=INDEX(Names,1)
it will return the first name in the list i.e. it will return the name from cell J16.
=INDEX(Names,2) will return the name from cell J17 , and so on.
Now , the only issue is to get the sequence of index numbers i.e. 1 , 2 , 3 ,...
This is where the SMALL(IF(...)) construct comes in.
The SMALL function takes in two parameters ; the first parameter is a column range or a row range , while the second parameter is a number such as 1 , 2 , 3 ,... ; using 1 as the second parameter will return the smallest value in the range , 2 will return the second smallest value , and so on.
What can the first parameter be ? It can be a list of numbers , in which case the SMALL function will return the smallest , the next smallest , the third smallest number and so on.
If the first parameter to the SMALL function is a list of dates , it will return the earliest date , the next one after that , the third one and so on.
Let us first consider the following formula =ROW(N1:N10) ; enter this in any unused cell , say A1 , place the cursor in A1 , press F2 and F9 ; you should see ={1;2;3;4;5;6;7;8;9;10}
What this says is that you have created a column array of 10 elements from 1 through 10. The semi-colon ";" is a separator between elements ; the above is an array which has 1 column and 10 rows.
If you want a row array of 10 elements , you would enter =COLUMN(A1:J1) ; enter this in any unused cell , say A1 , place the cursor in A1 , press F2 and F9 ; you should see ={1,2,3,4,5,6,7,8,9,10}
What this says is that you have created a row array of 10 elements from 1 through 10. The semi-colon "," is a separator between elements ; the above is an array which has 1 row and 10 columns.
Let us now consider what is there in your formula : ROW(Sheet2!$E$6:$E$22)
The Sheet2 is not significant here , and can be ignored ; if you enter =ROW($E$6:$E$22) in any unused cell , and see what is there , using F2 and F9 , you will see ={6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22}
What this has given us , is a column array ( since the semi-colon is used to separate the elements ) with 1 column and 17 rows ; what if we now want the array to actually be
={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
This is where the following constructs come in :
MIN(ROW(Sheet2!$E$6:$E$22))+1
The formula MIN(ROW(Sheet2!$E$6:$E$22)) returns the minimum value from the array {6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22} , which is 6. Subtracting this from the above array will result in an array :
={0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}
Adding 1 to the above , will give us what we want , which is :
={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
You will see this construct in a lot of places , so please become familiar with it :
ROW(H19:H57)-MIN(ROW(H19:H57))+1
will result in a column array having 39 elements :
={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39}
br />
The advantage of this construct is that you don't need to know the size of the array ; you can use it with a named range e.g. suppose you have a list of names , in a named range called Employees , you can have :
=ROW(Employees)-MIN(ROW(Employees))+1
The list of employees can be on any sheet , in any range , and have any number of employees , and the above construct will give you an array :
={1;2;3;4;
which will extend to the number of employees.
Let us now consider the following construct :
IF(Sheet2!$F$6:$F$22<>"",ROW(Sheet2!$E$6:$E$22)-MIN(ROW(Sheet2!$E$6:$E$22))+1)
The IF statement has 3 parts : the first parameter specifies the condition which will be checked to see whether it is TRUE or FALSE , the second parameter specifies the result if the checked condition evaluates to TRUE , the third parameter specifies the result if the checked condition evaluates to FALSE.
If we separate the above IF statement into these 3 parts , they are :
Condition to be checked : Sheet2!$F$6:$F$22<>""
Result if condition evaluates to TRUE : ROW(Sheet2!$E$6:$E$22)-MIN(ROW(Sheet2!$E$6:$E$22))+1
Result if condition evaluates to FALSE : omitted
Enter the above formula in any unused cell in a worksheet ; use CTRL SHIFT ENTER to enter it , since you are dealing with arrays. It can be entered in any sheet.
Now , in the Sheet2 tab in your worksheet , put data in the range F6:F22 , leaving blank cells in between.
Place the cursor in the cell which has the above formula , press F2 and F9 ; depending on which cells in the range F6:F22 are blank , you may get an output like this :
= {1;2;FALSE;4;FALSE;6;FALSE;FALSE;FALSE;10;11;12;13;FALSE;FALSE;FALSE;17}
What this means is that the following cells in the above range are blank :
F8 , F10 , F12 , F13 , F14 , F19 , F20 , F21.
What has happened in the remaining elements of the array ? How did they get the numbers 1 , 2 , 4 , 6 ,… ?
That is because of the result that was specified in case the checked condition is TRUE ; remember that the construct ROW(Sheet2!$E$6:$E$22)-MIN(ROW(Sheet2!$E$6:$E$22))+1 results in an array of numbers from 1 to 17 ( there are 17 elements in the array because the range spans 17 cells ).
The IF statement has put in the numbers from this array into the output array where ever the condition evaluated to TRUE ; wherever the condition was FALSE , the IF statement put in FALSE.
The FALSE is because the IF statement specified only 2 parameters , the first and the second ; the third was not specified.
Now , try adding one comma to the IF statement as follows :
IF(Sheet2!$F$6:$F$22<>"",ROW(Sheet2!$E$6:$E$22)-MIN(ROW(Sheet2!$E$6:$E$22))+1,)
The comma has been added at the end. What this does is ask Excel to take the default value if the condition evaluates to FALSE ; the default value is 0.
Now , press F2 and F9 ; you should see :
= {1;2;0;4;0;6;0;0;0;10;11;12;13;0;0;0;17}
The difference in the two versions is that all occurrences of FALSE in the first version have been replaced by the number 0 in the second version. Is this important ? We will see this next.
Narayan
