Check out this link of Chip Pearson's for a good intro to arrays:
http://www.cpearson.com/excel/ArrayFormulas.aspx
As to Index ... it can be complicated, since it can return *either* a value
or a reference, depending on it's use in a formula.
And it can reference either a one dimensional array, a two dimensional
array, or a number of different, non-adjacent arrays.
Say D6 to D10 contained the numbers 3 to 7 respectively;
E6 to E10 contained 500 to 900 respectively, and
F6 to F10 contained 2000 to 6000 respectively.
In it's simplest form of returning values:
From a one dimensional array,
=Index(D6
10,2)
Would return the value 4,
=Index(D6:F6,2)
Would return the value 500
For 2 dimensions:
=Index(D6:F10,3,2)
Returns 700
=Index(D6:F10,2,3)
Returns 3000
Needles to say, arriving at the 2nd and 3rd arguments (,3,2 or ,2,3) is
usually accomplished using other functions.
This is mostly seen in the Index - Match combination, which is commonly used
in place of Vlookup, where the lookup value is *not* in the left-most
position of a datalist.:
=Index(D6
10,Match(700,E6:E10,0))
To return 5.
=Index(D6:F10,Match(5000,F6:F10,0),Match(500,D6:F6,0))
To return 800.
As to it's use as a reference, which is how it's being used in this thread:
=Sum(D7:F9)
Returns 14115
=Sum(D7:Index(F6:F10,Match(5000,F6:F10,0)))
Returns 14115
=Sum(Index(D6
10,Large(D6
10,1)-5):Index(F6:F10,Match(5000,F6:F10,0)))
Returns 14115
AND, of course, we could just as easily replace the Sum() function with the
Average() function:
=Average(Index(D6
10,Large(D6
10,1)-5):Index(F6:F10,Match(5000,F6:F10,0)))
Which returns 1568.3
The same as:
=Average(D7:F9)
*NOTE* ... the Large() function in the above example formula is *NOT* being
used in the same way as in the Average formula in this thread.
You'll notice that *none* of the above formulas are *array* formulas,
needing a CSE entry.
I would suggest that you read through Chip's link on arrays before you
continue with the rest of this explanation.
In the formula we're discussing here, we're simply trying to determine how
to arrive at one of the arguments in the Index function which, in this case,
is being used to return one of the references for the Average function.
We started off with using A100 at the beginning of the Average formula.
It could just as well have been placed at the end of the formula, which
would be it's normal placement in the usual course of events.
=AVERAGE(INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100<>""),20)):A100)
I use it there simply to easily visually mark the end of the range.
Since I assume you now have some understanding of Index, we're now looking
at this part of the formula:
INDEX(A1:A100,LARGE(ROW(1:100)*(A1:A100<>""),20))
To see how we arrived at the second argument in Index, which determines the
starting location (reference) in the Average function.
Select the cell containing the entire formula.
In the formula bar, select:
Row(1:100)
And hit <F9>
You'll see the array of 1 to 100.
Now hit <Esc>, so that you don't destroy the formula.
Select
(A1:A100<>"")
And hit <F9>
You'll see an array of True and False, depending on which rows have, or
don't have, data in them.
Hit <Esc> again.
Now select both together,
Row(1:100)*(A1:A100<>"")
And hit <F9>
You'll see an array of row numbers that contain data, and zeroes for those
that don't.
This is what the Large function is looking at and working on (not the values
in those rows), and why you must use CSE, so that these arrays can be
accessed.
So, count the 20th largest *row* number, and you'll see how Index arrives at
the row reference that starts the range for Average to calculate.
Of course, less then 20 rows of data starts the Average function at the
beginning of the referenced range (A1).
