Max Offset

[Voodoodan]

Hi,

I've got a range of numbers, J5:J28. I can get the maximum value
using:

=max(j5:j28)

However, I want to find that max number and find the offset value 1
column to the left.

Is this possible within a formula?

I've tried all sorts of combos with MAX and OFFSET but can't get this
one.

Any help would be appreciated.

Thanks,
Dan.

 

[Bob Phillips]

=OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,1)

or

=INDEX(K5:K28,MATCH(MAX(J5:J28),J5:J28,0))

 

[mangesh_yadav]

Use:
=OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1)

Manges

 

[Voodoodan]

Blimey, any quicker guys and you'll be inventing time travel!

Thanks very much, that's worked perfectly.

Dan

 

[mangesh_yadav]

Manges

 

[Guest]

I would recommend using
=OFFSET(J5,MATCH(MAX(J5:J28,0),J5:J28,0)-1,1)
or
=INDEX(K5:K28,MATCH(MAX(J5:J28,0),J5:J28,0))

While it is not needed specifically in this case, I would use the ,0 in a
match whenever I do not definately know if the data is in ascending order.

Is there any concern that there might be several cells with the max value?

=OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,1)

or

=INDEX(K5:K28,MATCH(MAX(J5:J28),J5:J28,0))

 

[Jim May]

Why the "-1" in the row argument?
When I from the formula bar highlite the
MATCH(MAX(J5:J28),J5:J28,0) only and F9
it return the correct position WITHOUT the need for the -1. (*&%$^%$???
TIA,
Jim May

"mangesh_yadav" <[email protected]>
wrote in message
news:[email protected]...

 

[Bob Phillips]

I think you got confused here bj, I did put a ,0 for an exact match in the
formulae. You have added it to the max, which does absolutely nothing

 

[Bob Phillips]

Jim,

It applies to the OFFSET column, not to the MATCH.

Mangesh is looking left for the offset value. I (wrongly) looked right, so I
used 1 (+1).

--
HTH

Bob Phillips

Why the "-1" in the row argument?
When I from the formula bar highlite the
MATCH(MAX(J5:J28),J5:J28,0) only and F9
it return the correct position WITHOUT the need for the -1. (*&%$^%$???
TIA,
Jim May

"mangesh_yadav"

wrote in message

 

[Guest]

the "-1" is needed for the offset function.
the reference cell is "0" for offset so to get to the third cell below the
reference, this would result in a match of 4, but need a row of three input
to the offset.
in lookup fumctions (v and h and just lookup) the first cell is 1 so you do
not need to compensate. (I get bit by this occasionally)

Why the "-1" in the row argument?
When I from the formula bar highlite the
MATCH(MAX(J5:J28),J5:J28,0) only and F9
it return the correct position WITHOUT the need for the -1. (*&%$^%$???
TIA,
Jim May

"mangesh_yadav" <[email protected]>
wrote in message

 

[Guest]

oops you wanted to go to the right
=OFFSET(J5,MATCH(MAX(J5:J28,0),J5:J28,0)-1,-1)
or
=INDEX(I5:I28,MATCH(MAX(J5:J28,0),J5:J28,0))

I would recommend using
=OFFSET(J5,MATCH(MAX(J5:J28,0),J5:J28,0)-1,1)
or
=INDEX(K5:K28,MATCH(MAX(J5:J28,0),J5:J28,0))

While it is not needed specifically in this case, I would use the ,0 in a
match whenever I do not definately know if the data is in ascending order.

Is there any concern that there might be several cells with the max value?

 

[Guest]

Opps I meant to say you wanted to go to the left

oops you wanted to go to the right
=OFFSET(J5,MATCH(MAX(J5:J28,0),J5:J28,0)-1,-1)
or
=INDEX(I5:I28,MATCH(MAX(J5:J28,0),J5:J28,0))

 

[Jim May]

=OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1)

Bob:

My Q does not have to do with the LAST "-1" << which is the Column
argument (I understand that one;

My Q has to do with the "-1" at the end of the Row argument, that is ...
J5:J28,0)-1, <<<
WHY - See more extensive results below (in my orig write-in)..

Tks,

Jim

 

[Bob Phillips]

Jim,

That is because OFFSET is zero based, MATCH is 1 based, so you have to
subtract 1 from the matched row to get the number of rows to OFFSET.

 

[Jim May]

Bob,
I got it !!! Your explanation - well phrased!!
a bit "thick" here at times,
appreciate your patience.
Jim

 

[Bob Phillips]

No problem Jim.

I know that when you ask a question, you have thought about it, so it helps
me to get the right level of explanation.

It's symbiotic

Regards

Bob

 

[mangesh_yadav]

This thread spawned one big discussion..!

Mangesh

 

[Voodoodan]

Hi,

I seemed to have stirred up a right hornets' nest! But I have one more
question to add to the original post.

Using the formula you gave me
*"=OFFSET(J5,MATCH(MAX(J5:J28),J5:J28,0)-1,-1)"* is there any way to
return duplicates, i.e., if two or more numbers equal the maximum
figure?

Thanks,
Dan.

 

[mangesh_yadav]

This is bit difficult, but you can try the following:

In column K, starting row 5, enter the following formula and copy
down.
=IF(J5=MAX($J$5:$J$28),I5,"")

In column L, enter the following formula and copy down:
=IF(ISNUMBER(SMALL($K$5:$K$28,ROW()-ROW($K$5)+1)),SMALL($K$5:$K$28,ROW()-ROW($K$5)+1),"")


Mangesh

 

[Voodoodan]

That's excellent, thanks very much!

Dan.