Hi
No problem.
MAX ( ) didn't have comma
No reason why it should. Max(A:A), Max(1:1), Max(mydata) where mydata is
a named range would all be very common uses of the Max() function. The
comma would only come into play where you are giving Max() a list of
separate items to compare e.g Max(A1:A4,16, Day(Today()), Counter) where
each argument can be an individual value or a range of values.
ROW ( ) didn't have columns ????
Row() returns the row number of the current row, when used on its own.
Row(1:1) is often used as a way of incrementing a number in a formula,
as it returns a value of 1 when first used, but as it is copied down a
column, it will become Row(2:2) etc and rerun 2, 3 and so on within the
formula. In this case, when used within an array formula, Row(1:3)
returns a vertical array 1,2,3 which, as I said previously, is used to
multiply each of the 4 sets of Boolean values to produce the result.
Column() does exactly the same thing in terms of returning a column
number and again Column (A:A) would step up as one copied across a row.
Column(A
) used within an array formula, would return a horizontal
array of 1,2,3,4 and would have been used if the labels being Indexed
in the table were in the first row, and we were trying to determine
which column had produced our result.
Try it out, insert a new row 1 above the data block from the example we
have been using and put Epinn, Roger, Fred and Harry in B1:E1. Then use
{=INDEX(B1:E1,MAX(COLUMN(A
)*(A7=B2:E4)))}
and you would find that the Pen was not only a writing device, but
belonged to Roger!!!
I must take * + -- beyond SUMPRODUCT
Yes. Whilst Sumproduct is a useful function, it is but one of many in
the whole range available in Excel and all of the arithmetic operators
are used both on their own and within all of the functions. If you
forget Sumproduct for a while, the tress may become a little
clearer<vbg>
--
Regards
Roger Govier
Roger,
I just want to let you know that your support means a lot to me. I read
the Boolean writeup soon after you posted it. Most of it sank in. I
was going to reread it again the next day but unfortunately I didn't do
it before I read Kevin's formula. The "*" part from the Boolean post
didn't quite sink in and I was thrown off by the fact that MAX ( )
didn't have comma and ROW ( ) didn't have columns. So, I asked about
"*" again. Please don't be disappointed with me. Give me time. It is
still hard for me to pick up Boolean operators when I don't see
SUMPRODUCT ( ). You know I have been thinking about Pavlov and the
dogs. Conditioned is the word. I must take * + -- beyond SUMPRODUCT.
Please put up with me in the meantime. If I slip again, just shout
"Boolean" and I shall remember.
I enjoyed your explanation and I especially like the part on duplicates.
Thanks so much.
http://en.wikipedia.org/wiki/Ivan_Pavlov
Epinn
Hi
It is multiplication.
With Pen entered in cell A6
A6=B1:E3 will return an array of
False, True, False, False
False, False, False, False
False, False, False, False
When this is multiplied by the 3 row numbers, 1, 2 and 3
The first row becomes 0,1,0,0 and all the others are all 0.
The MAX of these 12 values is therefore 1
INDEX(A1:A3,1) = A1 = Writing Device
If Pen existed in cell D3 as well, then the third row of the array would
become
0,0,3,0
And the Max would be 3, hence the result will be A3 = Fastener
So, if there are duplicates, it will return the highest row number (and
Heading) that has the value.
If there are no duplicates it will return the row and Heading that
contains the value.
--
Regards
Roger Govier
Kevin,
This looks great. But I don't understand the formula. Do you still
have the link to Bob's paper as I want to learn too? When I see "*" I
think of multiplication and coercing. Don't know what "*" represents
here?
Wonder how we adjust the formula to take care of A6 being blank or
having an entry (e.g. business card) not found in the array.
Appreciate guidance.
Epinn
This array entered formula (entered using ctrl-shift-enter rather than
just
enter) appears to do the trick:
=INDEX(A1:A3,MAX(ROW(1:3)*(A6=B1:E3)))
Note: I took the max(row(... concept from a paper by Bob Umlas on using
array formulas and slapped on an Index function.