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
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
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
"slot guy" wrote:
> 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
the "--(" changes the logical true false to a numerical 1 0.
if you had column(C3) the response would be 3 since column C is the 3rd
column
in the formula
sumproduct(--(A1:B1=max(A1:B1),Column(A1:B1))
would be similar to
(A1=max(A1:B1)*Column(A1) +(B1=max(A1:B1))*column(B1
orif B1 were greater than A1
(0)*(1)+(1)*(2) = 2
"slot guy" wrote:
> 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?
>
>
> "bj" wrote:
>
> > 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
> >
> > "slot guy" wrote:
> >
> > > 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
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?
"bj" wrote:
> 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
>
> "slot guy" wrote:
>
> > 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
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)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks