Hi ,
I will explain the following formula , assuming that it is the one you have used. If not , hopefully the explanation will help in understanding the other versions.
The formula is :
=INDEX(SlNo,MATCH(1,SIGN((COUNTIF($F$1:$F1,SlNo)<SUMIF(SlNo,SlNo,RepeatAmt))),0))
entered as an array formula , using CTRL SHIFT ENTER.
SlNo is a named range , which refers to the text strings that will be repeated ; in the example workbook , these strings are Right , Left , North , East , in the cells A2:A5.
RepeatAmt is a second named range , which refers to the numeric values which will used to define the number of times the above text strings will be repeated ; in the example workbook these values are 2 , 1 , 4 , 2 in the cells B2:B5.
With the above input data , the final output is supposed to be :
Right
Right
Left
North
North
North
North
East
East
The posted formula is not supposed to start in row 1 i.e. it cannot be entered in any cell in row 1 , since it refers to a cell in that row. It can be entered in any unused cell in row 2 downwards , assuming that the cells above it do not contain the text strings which have been entered in SlNo.
The outermost INDEX is simple enough to understand ; if we can generate a set of values to give us the following formula in the different cells in column F , then we will get what we want.
=INDEX(SlNo , 1)
=INDEX(SlNo , 1)
=INDEX(SlNo , 2)
=INDEX(SlNo , 3)
=INDEX(SlNo , 3)
=INDEX(SlNo , 3)
=INDEX(SlNo , 3)
=INDEX(SlNo , 4)
=INDEX(SlNo , 4)
These highlighted values are being generated by the following part of the formula :
MATCH(1,SIGN((COUNTIF($F$1:$F1,SlNo)<SUMIF(SlNo,SlNo,RepeatAmt))),0)
The key functions here are the SIGN , COUNTIF and SUMIF functions.
The SIGN function takes in a numeric value , and returns one of the following 3 values :
-1 if the numeric value is negative
0 if the number is 0
1 if the number is positive
The SUMIF function generates an array of values corresponding to the repeat amounts for each text string.
You may ask why we use a SUMIF function to generate these values , when we already have the named range RepeatAmt which stores these values ; this will be clear when we consider a different situation later on.
The point is that the SUMIF function generates an array of values corresponding to the repeat amounts for each text string , and in the example , it will return the array {2;1;4;2}.
The COUNTIF function normally returns a single scalar value , which is a count of how many times a particular value occurs in a range ; the range is the first parameter , and the value whose occurrence in the range is to be counted is the second parameter.
However , if the second parameter is a range , instead of a single value , then the COUNTIF function returns an array of the same length. In the present case , because we are using :
COUNTIF($F$1:$F1,SlNo)
it will return an array with as many elements as the named range SlNo ; for the example data in the workbook , it will return an array of 4 elements.
For the first formula , since F1 does not contain a text string from SlNo , the return array will be :
{0;0;0;0}
When we compare this array with the array returned by the SUMIF function , we will have {0;0;0;0} < {2;1;4;2} , which will return an array :
{TRUE;TRUE;TRUE;TRUE}
The SIGN function converts this to numeric values , as follows :
{1;1;1;1}
The MATCH function then returns the scalar value 1 , since 1 has been found in the very first position.
The INDEX function now puts the text string Right in the first cell where the formula has been entered.
When we come to the second cell , the comparison now becomes :
{1;0;0;0} < {2;1;4;2}
which again returns the same array as before. This results in the second cell also having the text string Right.
In the third cell , the comparison becomes :
{2;0;0;0} < {2;1;4;2}
Now , the first element of the array on the left , 2 , is no longer less than the first element of the array on the right , 2.
Thus , the array returned by the above comparison becomes :
{FALSE;TRUE;TRUE;TRUE}
The SIGN function converts this to numeric values , as follows :
{0;1;1;1}
The MATCH function now returns 2 , and the INDEX function puts the text string Left in the third cell.
In the fourth cell , the comparison becomes :
{2;1;0;0} < {2;1;4;2}
Now , the first and second elements of the array on the left , 2 and 1 , are no longer less than the first and second elements of the array on the right , 2 and 1.
Thus , the array returned by the above comparison becomes :
{FALSE;FALSE;TRUE;TRUE}
The SIGN function converts this to numeric values , as follows :
{0;0;1;1}
The MATCH function now returns 3 , and the INDEX function puts the text string North in the fourth cell.
And so it goes till all the elements have been listed the given number of times.
And so we come to the question , why do we use the SUMIF function ?
Narayan
