How do I find the cell reference that contains the minimum or maximum
 

Hello
I want to know how do I find the cell reference that contains the
minimum value between a range of cells.
I know I can use the function =min(A1:A100) but this returns the
minimum value.
I want to know where this minimum is.
And, if I know it's A50 how do I use the cell B50, let me explain: I
have some dates in B cells and some values in A cells. I want to know
when the minimum did occur (this should be a date in B cells)
Thanks a lot


--
fadsnet
------------------------------------------------------------------------
fadsnet's Profile: http://www.excelforum.com/member.php...o&userid=37254
View this thread: http://www.excelforum.com/showthread...hreadid=569726

How do I find the cell reference that contains the minimum or maximum
 

Hi!

So, you want to find the MIN in col A and return the corresponding value
from col B?

Try this:

=INDEX(B1:B100,MATCH(MIN(A1:A100),A1:A100,0))

Note: if there are duplicate MIN's the formula will match the FIRST
instance.

Biff

"fadsnet" wrote in
message ...

Hello
I want to know how do I find the cell reference that contains the
minimum value between a range of cells.
I know I can use the function =min(A1:A100) but this returns the
minimum value.
I want to know where this minimum is.
And, if I know it's A50 how do I use the cell B50, let me explain: I
have some dates in B cells and some values in A cells. I want to know
when the minimum did occur (this should be a date in B cells)
Thanks a lot


--
fadsnet
------------------------------------------------------------------------
fadsnet's Profile:
http://www.excelforum.com/member.php...o&userid=37254
View this thread: http://www.excelforum.com/showthread...hreadid=569726

How do I find the cell reference that contains the minimum or maximum
 

Ok this worked.Thanks a lot


--
fadsnet
------------------------------------------------------------------------
fadsnet's Profile: http://www.excelforum.com/member.php...o&userid=37254
View this thread: http://www.excelforum.com/showthread...hreadid=569726

How do I find the cell reference that contains the minimum or maximum
 

You're welcome. Thanks for the feedback!

Biff

"fadsnet" wrote in
message ...

Ok this worked.Thanks a lot


--
fadsnet
------------------------------------------------------------------------
fadsnet's Profile:
http://www.excelforum.com/member.php...o&userid=37254
View this thread: http://www.excelforum.com/showthread...hreadid=569726

How do I find the cell reference that contains the minimum or maximum
 

fadsnet wrote:
Hello
I want to know how do I find the cell reference that contains the
minimum value between a range of cells.
I know I can use the function =min(A1:A100) but this returns the
minimum value.
I want to know where this minimum is.
And, if I know it's A50 how do I use the cell B50, let me explain: I
have some dates in B cells and some values in A cells. I want to know
when the minimum did occur (this should be a date in B cells)
Thanks a lot


Try with this:

=ADDRESS(ROW()-1+MATCH(MIN(A9:A24),A9:A24,0),1,4)

In this example I have some random numbers in A9:A24 and the formula gives
the address of the cell with the minimum value.

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy