Hi John ,
In addition to explaining the formulae , I will describe the process of trying to understand so that you can use the same process to understand almost any formula.
1. In any formula which has IFERROR as the outermost function , eliminate it , since what it does is quite straightforward :
If the overall formula is :
=IFERROR( inner formula , "" )
what this does is evaluate the
inner formula ; if it evaluates to a proper result , return the result ; if it evaluates to any one of the error results such as #N/A , #DIV/0! , #REF! , it returns a blank.
When we eliminate this from your first formula , we have :
=INDEX(CHARTASSIGN!$C$3:$C$30,SMALL(IF(LEFT(CHARTASSIGN!$C$3:$C$30,3) <> "DR.",0,100)+IF(CHARTASSIGN!$C$3:$C$30 > "",0,100)+ROW(CHARTASSIGN!$S$3:$S$30)-2,ROWS(CHARTASSIGN!$C$3:$C3)),0)
2. If there are sheet references which refer to just one sheet , and if there no other references , then this indicates that the formula is not using references on more than one sheet ; let me explain this with an example :
Suppose we have a simple formula such as :
=$A$1+Sheet3!$B$1
Here the first reference does not include the Sheet reference ; the cell reference $A$1 might refer to a cell on Sheet3. Excel will accept this as a valid formula on Sheet3.
Suppose the above formula were on Sheet2 ; then we can conclude that the reference to $A$1 is the cell on Sheet2 , since if a sheet reference is not used , the cell referred to is on the same sheet as the formula.
In your formula , all the references are using a sheet reference , and all of them refer to the same sheet ; therefore we will not sacrifice anything if we omit all the sheet references ; what this does is at least reduce the length of the formula !
We now have :
=INDEX($C$3:$C$30,SMALL(IF(LEFT($C$3:$C$30,3) <> "DR.",0,100)+IF($C$3:$C$30 > "",0,100)+ROW($S$3:$S$30)-2,ROWS($C$3:$C3)),0)
3. This is a straightforward construct :
=INDEX(range reference , SMALL(IF(condition to be checked,array to be returned if true),ROW(A1)))
What this construct does is return the smallest element from the range reference ; as it is copied downwards , it will return progressively the second smallest element , the third smallest element and so on.
If we correlate the construct with your stripped down formula , we get :
Range reference : $C$3:$C$30
Condition to be checked : LEFT($C$3:$C$30,3) <> "DR." , $C$3:$C$30 > ""
Array to be returned if true : ROW($S$3:$S$30)-2
ROW(A1) : ROWS($C$3:$C3)
4. Before we proceed further , why don’t you try out the stripped down formula :
=INDEX($C$3:$C$30,SMALL(IF(LEFT($C$3:$C$30,3) <> "DR.",0,100)+IF($C$3:$C$30 > "",0,100)+ROW($S$3:$S$30)-2,ROWS($C$3:$C3)),0)
by entering some data ; enter the following data in the range C3:C30 :
Code:
CR.
DR.
CR.
CR.
CR.
CR.
DR.
DR.
CR.
CR.
DR.
CR.
CR.
CR.
CR.
DR.
CR.
DR.
CR.
CR.
DR.
DR.
CR.
DR.
5. Now , enter the formula in any column , say D3 , as an array formula , using CTRL SHIFT ENTER ; copy the formula down , till D30.
6. You can see what the formula does. Essentially , it has removed all entries which started with “DR.” , as well as all those entries which were blank. There are 13 such entries.
7. The range D3:D30 is 28 cells ; 15 of those will have proper data in them , and the remaining 13 will be having error values #REF! ; the reason for the IFERROR wrapper is now clear viz. to replace these error values by blanks.
8. If things are clear so far , we can proceed to discussing how the INDEX & SMALL combination works.
Narayan
