Explaining the answer in Post #2
Take the formula in B2
=IF(B2:B11=E2,A2:A11,FALSE)
For the normal formula case this would look like
=IF(B2=E2,A2,FALSE)
So IF B2 = E2 , the value from A2 is returned which is "a". Otherwise FALSE is returned
Because the formula, {IF(B2:B11=E2,A2:A11,FALSE)}, has been entered in the CSE way, (type 2 way for output over the range D2:D11 ), Excel does the formula 10 times. Each time it uses the next value from the range B2:B11 and also the corresponding value from A2:A11 or FALSE as the returned output. Note the values from the same position in each of the ranges, D2:D11 and A2:A11 are used in the calculation. So the formulas evaluated by Excel are:
IF(B2=E2,A2,FALSE) = a
IF(B3=E2,A3,FALSE) = FALSE
IF(B4=E2,A4,FALSE) = c
etc...
Consider the formula in F2
We are using the Row( ) Function. This returns an Array of integer numbers corresponding to the row numbers of a range given.
So ROW(A1) returns just
1
ROW(A2:A11) returns
2
3
4
5
or {2;3;4;.....}
etc...
Because the formula { IF(B2:B11=E2,ROW(A2:A11)-ROW(A1),FALSE)}, has been entered in the CSE way, (type 2 way for output over the range F2:F11 ), Excel does the formula evaluation 10 times. Each time it uses the next value from the range B2:B11 and also the corresponding value from the Array given by ROW(A2:A11)
So the formulas evaluated by Excel are:
IF(B2=E2,2-1,FALSE) = 1
IF(B3=E3,3-1,FALSE) = FALSE
IF(B4=E4,2-1,FALSE) = 3
etc....
Consider the formula in G2
We are using the SMALL Function. This has two arguments
SMALL({Array}, kth)
The first argument is an Array. Note: This Array is specific for use in this function. This full Array is used regardless of if the function appears in a normal formula or one that has been given Arrays or ranges where single values are used in normal formulas. If Excel does any evaluations in CSE entered formulas, it still uses the full {Array}each time. Usually K is a single value such as 1, 2 or 3 etc...
The SMALL Function works as follows: The kth next smallest value in the {Array} is returned.
For example
SMALL({7;3;10}, 2) = 7 ' 7 is the second smallest number
SMALL({7;3;10}, 1) = 3 ' 3 is the first smallest number. In other words the smallest number
SMALL({7;3;10}, 4) = __ 'This will error as there is no 4th smallest number
Usually in normal formula usage k is a single number. But we have given it an Array. This is the Array returned by ROW(F1:F10) which is
1
2
3
or {1;2;3...}
etc..
Because the formula { SMALL(F2:F11,ROW(F1:F10))} has been entered in the CSE way, (type 2 way for output over the range G2:G11), Excel does the formula evaluation 10 times. Each time it uses the next value from the Array given by ROW(F1:F10), but considers each time the entire Array of the range F2:F11.
So the formulas evaluated by Excel are
SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 1) = 1
SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 2) = 3
SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 3) = 4
SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 4) = 7
SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 5) = 9
SMALL( {1;FALSCH;3;4;FALSCH;FALSCH;7;FALSCH;9;FALSCH}, 6) = ' This will error
etc.
Consider the formula in H2
We are using the INDEX Function. , =INDEX( {Array}, row, column ). This returns us the value in its first argument {Array} given at the intersection of its second argument, row, coordinate, and its third argument, column, coordinate.
( Note: row and column are that in the Array {Array}, not the row and column number in the worksheet).
Note also: Array {Array} is specific for use in this function. This full Array is used regardless of if the function appears in a normal formula or one that has been given Arrays or ranges where single values are used in normal formulas. If Excel does any evaluations in CSE entered formulas, it still uses the full {Array}each time.
{Array} is chosen to be A2:A11 which is
a
b
c
d
etc..
or {a;b;c;d;e;f;g;.....}
{Array} is in this case a 1 column multi row Array, so column is always = 1
Normally row would be a single value. But we are using for our CSE type 2 entered formula an Array given by the range G2:G11 which is
1
2
4
7
9
Error
etc..
or {1;3;4;7;9;error;error......}
Because the formula { INDEX(A2:A11,G2:G11,1)} has been entered in the CSE way, (type 2 way for output over the range H2:H11), Excel does the formula 10 times. Each time it uses the next value for row from the Array given by G2:G11, but considers each time the entire Array of the range A2:A11.
So the formulas evaluated by Excel are
INDEX({a;b;c;d;e;f;g;.....},1,1) = a
INDEX({a;b;c;d;e;f;g;.....},3,1) = c
INDEX({a;b;c;d;e;f;g;.....},4,1) = d
INDEX({a;b;c;d;e;f;g;.....},7,1) = g
INDEX({a;b;c;d;e;f;g;.....},9,1) = i
INDEX({a;b;c;d;e;f;g;.....},1,1) = ' This will error
etc..
Consider the formula in I2
For a normal formula this Formula would look like
= IF(ISERROR(H2),"",H2)
So if the value in cell H2 was an error then an empty string would be returned. ( An empty string "" is Excels way of showing no value ). Else If cell H2 does not contain an error then the value in cell H2 is returned.
Because the formula { IF(ISERROR(H2:H11),"",H2:H11)}, has been entered in the CSE way, (type 2 way for output over the range I2:I11 ), Excel does the formula 10 times. Each time it uses the next value from the range H2:H11
So the formulas evaluated by Excel are:
IF(ISERROR(a),"",a)=a
IF(ISERROR(c),"",c)=c
IF(ISERROR(d),"",d)=d
IF(ISERROR(g),"",g)=g
IF(ISERROR(i),"",i)=i
IF(ISERROR( 'This cell has an error shown ),"",error)="" ' The returned value appears as nothing to us, that is to say we see nothing in the cell
IF(ISERROR( 'This cell has an error shown ),"",error)="" ' The returned value appears as nothing to us, that is to say we see nothing in the cell
etc...
That's it !
A final formula can be achieved by firstly substituting the formula from H2 in place of H2:H11 in the final formula, and then substituting the formula in G2 in place of G2:G11, and then substituting the formula in F2 in place of F2:F11. ( The formula in D2 was not used
Reply With Quote
Bookmarks