Subtract a max value from a row then copy that columns title?

[Guest]

Hi,

I've been trying this with variations of the =MAX function but cant get it
to work... :-(

I have a row of text entries as column titles in every second column, e.g:
A1, C1, E1, G1 etc

Below those text titles, I have numeric values. e.g: A2, C2, E2, G2 etc

I want to get the highest number from row 2, and then display the column
title from that row......

.......any ideas?

 

[Roger Govier]

Hi

Try
=INDEX(A1:G1,MATCH(MAX(A2:G2),A2:G2))

 

[Bob Phillips]

In case they are not ordered

=INDEX(A1:G1,MATCH(MAX(A2:G2),A2:G2,0))



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

That.....kinda.....worked!

Thanks folks. I got Rogers formula working fine, but the values are not in
order, they skip every second column, so I tried adapting Bob's formula to
get that working and typed in this:

=INDEX(B24,D24,F24,MATCH(MAX(B25,D25,F25),B24,D24,F24,0))

Unfortunately it doesnt work.....sorry for throwing questions at you all but
your help is very much appreciated.

Any ideas how to get this working?

 

[Guest]

Try this:

=INDEX(B24:F24,MATCH(MAX(B25,D25,F25),IF(MOD(COLUMN(B25:F25),2)=0,B25:F25),0))

ctrl+shift+enter, not just enter

 

[RagDyeR]

Try this *array* formula:

=INDEX(B24:F24,MATCH(MAX((MOD(COLUMN(B25:F25),2)=0)*(B25:F25)),B25:F25,0))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

If/when you need to extend the range, simply revise all the reference to
Column F to Column "whatever".
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


That.....kinda.....worked!

Thanks folks. I got Rogers formula working fine, but the values are not in
order, they skip every second column, so I tried adapting Bob's formula to
get that working and typed in this:

=INDEX(B24,D24,F24,MATCH(MAX(B25,D25,F25),B24,D24,F24,0))

Unfortunately it doesnt work.....sorry for throwing questions at you all but
your help is very much appreciated.

Any ideas how to get this working?

 

[Guest]

Thanks folks, that seems to be working now. I really have to get my head
around this whole Ctrl-Sft-Entr thing....when to use it, when not to use
it.....what makes it different and work...etc

 

[T. Valko]

See this:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

 

[RagDyeR]

And we appreciate the feed-back.

 

[Guest]

Great, thanks.

L8rs.