Trevor ... (Happy morning)

Back at it again ... And "Yes" ... Without question, the intimate knowledge
of Excel known by the Excel Magicians that support these boards is AWESOME.

Thanks for the guidance ... Kha

"Trevor Shuttleworth" wrote:

Ken

sorry, that would be a "no". Good though isn't it !?

Best way is to Array Enter the formula into cell Q3 as suggested, then drag
it across and look at how the formula changes in each column.

You could also take any of the "inner" formulae and put them in a separate
cell to evaluate them.

Best I can suggest, I'm afraid. Perhaps Biff can be persuaded to clarify ?

Regards

Trevor


"Ken" wrote in message
...
Trevor ... (Happy morning)

It has been a long week so I am just getting back to all of this ... Since
I
have had no further response from T. Valko & if you are still listening
...
Would you be able to explain formula Valko provided in same way you
explained
yours (line item step by step) ... Not only is this helpful for me to
better
understand the specific functions, but also the combination of functions &
order of calculations etc ... Formula is:


=IF(COLUMNS($A:A)<=COUNTIF($B3:$P3,MAX($B3:$P3)),I NDEX($B$2:$P$2,SMALL(IF($B3:$P3=MAX($B3:$P3),COLUM N($B3:$P3)-MIN(COLUMN($B3:$P3))+1),COLUMNS($A:A))),"")

Thank you in advance ... Kha


"Trevor Shuttleworth" wrote:

Thanks for the feedback. You have a great day too


"Ken" wrote in message
...
Trevor ... (Happy morning)

I placed values in the applicable Range of a Blank WorkBook & worked
this
formula thru to help me better understand the Formulas & sequence of
calculations (excellent explanation) ... I have learned much from those
that
are intimate with Excel & support these boards ... Please know I
appreciate
the time you have taken to provide this guidance for one of my many
Excel
short-comings.

My Thanks ... Have a Happy & Safe Day ... Kha


"Trevor Shuttleworth" wrote:

OK

MAX(B3:P3)
returns the maximum value in the range B3 to P3

MATCH(MAX(B3:P3),B3:P3,0)
returns the offset of the maximum value within the range

MATCH(MAX(B3:P3),B3:P3,0)+1
calculates the column number (taking into account we start in
column
B)

ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)
turns that into an address ... row 2, column whatever we just
calculated

=INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1))
gets the value from the calculated address

So, let's say the maximum value of 9 was in cell L3 which is column 12

=MAX(B3:P3) = 9
=MATCH(MAX(B3:P3),B3:P3,0) = MATCH(9,B3:P3,0) = 11
=MATCH(MAX(B3:P3),B3:P3,0)+1 = MATCH(9,B3:P3,0)+1 = 12
=ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1) = ADDRESS(2, 12) = $L$2
=INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1)) = INDIRECT($L$2) =
column
12 or whatever

Regards

Trevor


"Ken" wrote in message
...
Trevor ... Formula works flawlessly (Thanks) ... That said ... Can
you
take a
moment to give me a step by step (in order) line-item breakdown of
how
this
formula works? I need to ask T. Valko same thing ... His formula is
flawless
too.

Thank you for the guidance ... Kha

"Trevor Shuttleworth" wrote:

One way:

=INDIRECT(ADDRESS(2,MATCH(MAX(B3:P3),B3:P3,0)+1))

If there are multiple maximum values, this will return the name of
the
first
one.

Regards

Trevor


"Ken" wrote in message
...
Excel2003 ...

Range B2:P2 ... Names
Range B3:P42 ... Values

Looking for Formula in Range Q3:Q42 to return:

Q3 ... "Name" associated with MAX Value found in Range B3:P3
Q4 ... "Name" associated with MAX Value found in Range B4:P4
Q5 ... "Name" associated with MAX Value found in Range B5:P5

Note: also need to know how to handle if multiple "Names" found
with
same
MAX Value in the Range???

Thanks ... Kha