In this post we would like to illustrate the strange behaviour of OFFSET function described by Dick Kusleika in the Array with offset article.
The feature we are focusing is: “...when an array is used as the 2nd argument of OFFSET... an array of RANGES is returned.” The statement may be generalized: arrays could be used in the rows, cols, height or width parameter too. For example this formula uses array for the rows parameter:
=OFFSET( A1:A10 , {0;1;2} , ,3 )
if array-entered, results these 3-element ranges: A1:A3 A2:A4 A3:A5 While this one uses array for height:
=OFFSET( A1:A10 , , , {2;3;4} ) and the result is: A1:A2 A1:A3 A1:A4 Unfortunately Excel is not capable to display array of ranges, and only a few formula is able to operate correctly by them. One of these formulas is SUBTOTAL - using one of it’s 11 different functions, it is able to evaluate the ranges separately and gives the expected result.
## Darts challenge exampleThe first example will illustrate how OFFSET function could be applied for the Darts challenge posted by us a few months ago.
The base question was how to calculate the total square of the sum of k-element groups. Knowing the above mentioned fact about OFFSET the question is answered. We only need the k-element arrays and SUBTOTAL them with parameter 9. than square, sum and finished. The only little trick is that we need “circular” k-element groups, so for example in case k=3 we should have a group where we add the very last element of the range and the first two, and another group where we add the last two elements and the very first. In general, this “circulation” applies for the last k-1 elements of the range. This is why two subtotals are used in our solution. One will summarize k-element groups without circulation, so will not add the elements from the beginning of the list. The second subtotal will create additional groups from the beginning of the list - only for the elements where “circulation” needed. Adding this two array-results together to have the sum of the k-element groups. E voilá, the formula: `=SUM( (SUBTOTAL(9,OFFSET(rng,MAX(ROW(rng))-ROW(rng),,k)) + IF( ROW(rng)-MIN(ROW(rng))<k-1 , SUBTOTAL(9,OFFSET(rng,,,k-ROW(INDIRECT("1:"&k)))) ) )^2)` First subtotal with parameter 9 to sum k-element groups. The second (rows) parameter of OFFSET is an array, it will go through the range from the bottom to the top and create array of k-element ranges.
Second subtotal with pRank formula examplearameter 9 to sum the elements needed for the circular ranges for the last k-1 rows - nested into an IF function. The array is in the 3rd (height) parameter of OFFSET, this will create the additional groups needed for the “circulation”. You can check it out in the example file. ## Rank formula exampleAnother function capable to deal with the array of ranges is COUNTIF. This formula is not available via the parameters of subtotal, but fortunately it could work together beautifully with offset.
Sometimes it is a problem that RANK function does not take into account ties, so duplicate numbers will have the same rank - similarly as in the Olympic Games: in case of tie on the first place, two gold medals are distributed, but no silver, so rank 2 will not appear.
In Excel 2010 MS introduced two new versions of Rank: RANK.AVG uses the average rank in case of duplicated values, RANK.EQ returns the top rank of the set of the duplicated numbers (as it is in the old RANK). None of these functions solve the case when you want to take the ties into account in a way looking the position of the number within the range. The task is very easy, only need to add a correction to rank: count how many times the number appears in the list before actual cell. In the below example the formula in B12 will be: `=RANK(B2,n_data,1)+COUNTIF( $B$2:B2 , B2 )-1` However many times we need the result in array, so this copy-formula is not a solution. Using the features of OFFSET we can create an array formula which will result an array of the adjusted rank numbers. The formula is array-entered to the range B14:G14 `=RANK(n_data,n_data,1) + COUNTIF( OFFSET(n_data,,,,COLUMN(n_data)-MIN(COLUMN(n_data))+1), OFFSET(n_data,,COLUMN(n_data)-MIN(COLUMN(n_data)),,1) )-1` The first offset creates the array of ranges as the $B$2:B2 part works when copying the first formula across the columns. Array is used in the width parameter of OFFSET. The second offset picks up the values one by one from the range similarly as B2 goes through the range. Array is used in the column parameter of OFFSET and creates one element ranges. You can learn more from the example file where you can also find the “descending” version of the modified rank formula. by The FrankensTeam formulas:r words: Kris picture: István Orosz http://utisz-utisz.blogspot.hu/ |