See inline...
there are gaps in my knowledge base... <<
Don't feel like the Lone Ranger. We're ALL self-taught.
Yeah, but I'm guessing you and the other regulars here have been self-taught
longer than me. said:
It's exposure to great resources, like this group, that helps us
expand our skill set.
No question about that... I have learned a lot over this past year of my
volunteering in this newsgroup by just reading the various posted answers
here.
Regarding the use of the INDEX function....When the 2nd argument
is zero (0), it evaluates the first arguement and returns the
array into a series of values that worksheet functions can use
(most of the time).
Quick example:
=MAX({2,4,6,5}+{1,2,3,4}) returns 9
=MAX({2,4,6,5}+COLUMN(A
)) returns 7 (huh? wrong!)
=MAX(INDEX({2,4,6,5}+COLUMN(A
),0)) returns 9 (correct)
That second formula really is a "huh", isn't it? If you highlight
COLUMN(A
) and press F9, it shows the expansion as {1,2,3,4} as one would
expect. If you highlight {2,4,6,5}+COLUMN(A
) which is the argument to the
MAX function, and press F9, it shows the expansion as {3,6,9,9}, again, as
would be expected. But then, instead of MAX choosing one of the values from
{3,6,9,9}, it goes and makes up a value of 7?!!? While the F9 expansions
show the array values correctly, the MAX function appears to be ignoring the
array evaluations of COLUMN(A
) and simply using the first value in the
array list, 1, and adding it to the first array's values before selecting
the maximum value of 7 (6+1). If you change your formula to use COLUMN(B:E)
instead, the formula will return 8 which appears to be the 6 from the first
array grouping plus the 2 (B's equivalent as a column number) from the array
expansion of COLUMN(B:E). The mechanism at work here seems to be akin to the
one at work in a formula like this...
=FIND({"a","b"},<<SomeText>>)
When <<SomeText>> is the word "about", the formula works and returns 1; but,
when <<SomeText>> is the word "bout", the formula errors out... and it does
that whether you array-enter it or not. Some functions seem able to tolerate
an array without actually being able to use anything past its first
argument... this appears to be the case with your second formula.
Rick