Retuning a column number for a specific value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

slot guy - 13 Sep 2005 18:31 GMT

I'm trying to figure out how to have a formula return the column number from
a maximum value in a row. I've tried using =column(max(a1:c1)) to return the
column number but it errors out. Can anyone offer a suggestion?

Thanks

Reply to this Message

bj - 13 Sep 2005 18:41 GMT

try
=sumproduct(--(range =max(range)),Column(range)

note if there can be several equal maxs this won't work.

in this case try
=match(max(range),range,0)+(column number at start of range)-1

> I'm trying to figure out how to have a formula return the column number from
> a maximum value in a row. I've tried using =column(max(a1:c1)) to return the
> column number but it errors out. Can anyone offer a suggestion?
>
> Thanks

Reply to this Message

slot guy - 13 Sep 2005 22:47 GMT

bj,
Thank you for the formula. I uesed the first one since I'm looking for
unique date with no repeats. Can you please tell me what the "--" does in the
function? Also, I don't understand how the column number is actualy returned
using the column function. The function description just states that "column"
returns the column number of a reference. It doesn't ask for any criteria to
use to pick from a reference. Can you explain?

> try
> =sumproduct(--(range =max(range)),Column(range)
[quoted text clipped - 9 lines]
> >
> > Thanks

Reply to this Message

Harlan Grove - 13 Sep 2005 22:54 GMT

bj wrote...
...

>note if there can be several equal maxs this won't work.
>
>in this case try
>=match(max(range),range,0)+(column number at start of range)-1

...

Or

=COLUMN(INDEX(range,MATCH(MAX(range),range,0)))

Reply to this Message