Hello, I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks

On Sun, 11 Dec 2005 16:06:40 -0000, "Mike H" wrote:

Hello, I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks


Since I see this post in programming, I will assume you want a VBA solution:

====================================
Option Explicit
Function MinCellAdr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)

MinCellAdr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address

End Function
=====================================


--ron

Trying this -- I get #VALUE! as a result...
Any suggestions?
Tks..

"Ron Rosenfeld" wrote in message
...
On Sun, 11 Dec 2005 16:06:40 -0000, "Mike H" wrote:

Hello, I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks


Since I see this post in programming, I will assume you want a VBA solution:

====================================
Option Explicit
Function MinCellAdr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)

MinCellAdr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address

End Function
=====================================


--ron

On Thu, 8 Dec 2005 19:48:12 -0500, "JMay" wrote:

Trying this -- I get #VALUE! as a result...
Any suggestions?
Tks..

An error value in rg will cause that.








"Ron Rosenfeld" wrote in message
.. .
On Sun, 11 Dec 2005 16:06:40 -0000, "Mike H" wrote:

Hello, I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks


Since I see this post in programming, I will assume you want a VBA solution:

====================================
Option Explicit
Function MinCellAdr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)

MinCellAdr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address

End Function
=====================================


--ron


--ron

But a more likely cause is that Find doesn't work in a UDF used in a
worksheet in xl2000 and earlier.

--
Regards,
Tom Ogilvy

"Ron Rosenfeld" wrote in message
...
On Thu, 8 Dec 2005 19:48:12 -0500, "JMay" wrote:

Trying this -- I get #VALUE! as a result...
Any suggestions?
Tks..

An error value in rg will cause that.








"Ron Rosenfeld" wrote in message
.. .
On Sun, 11 Dec 2005 16:06:40 -0000, "Mike H"
wrote:

Hello, I want a function that allows me to enter a range and then
returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks


Since I see this post in programming, I will assume you want a VBA
solution:

====================================
Option Explicit
Function MinCellAdr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)

MinCellAdr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address

End Function
=====================================


--ron


--ron

On Thu, 8 Dec 2005 21:15:05 -0500, "Tom Ogilvy" wrote:

But a more likely cause is that Find doesn't work in a UDF used in a
worksheet in xl2000 and earlier.

--
Regards,
Tom Ogilvy

I've seen that written here before, but completely forgot about it.

We'll see what the OP has to say about his Excel version. Could always do a:

for each c in rg
test it
next c

which hopefully won't be too slow unless rg is huge.


--ron

Ron Rosenfeld wrote...
....
Since I see this post in programming, I will assume you want a VBA solution:

====================================
Option Explicit
Function MinCellAdr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)

MinCellAdr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address

End Function
....

Already pointed out .Find fails when called in udfs in XL97, but
irrelevant to OP.

The OP's message was crossposted to w.f as well, so another formula
approach. If the range in question, which I'll denote RNG, is 1D, then

=CELL("Address",INDEX(RNG,MATCH(MIN(RNG),RNG,0)))

should return the address of the cell containing the first instance of
the minimum value. If RNG could be 2D, then if there could be multiple
instances of the minimum value, which should be considered the first
instance: the one in the leftmost column or the one in the topmost row?

Leftmost column (array formula):
=CELL("Address",INDEX(RNG,MATCH(MIN(RNG),INDEX(RNG ,0,
MIN(IF(RNG=MIN(RNG),COLUMN(RNG)))),0),MIN(IF(RNG=M IN(RNG),COLUMN(RNG)))))

Topmost row (array formula):
=CELL("Address",INDEX(RNG,MIN(IF(RNG=MIN(RNG),ROW( RNG))),
MATCH(MIN(RNG),INDEX(RNG,MIN(IF(RNG=MIN(RNG),ROW(R NG))),0),0)))

On 9 Dec 2005 17:20:12 -0800, "Harlan Grove" wrote:

Ron Rosenfeld wrote...
...
Since I see this post in programming, I will assume you want a VBA solution:

====================================
Option Explicit
Function MinCellAdr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)

MinCellAdr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address

End Function
...

Already pointed out .Find fails when called in udfs in XL97, but
irrelevant to OP.

Any idea why he's having what seems like a problem with the VBA Find function
in XL2003?



The OP's message was crossposted to w.f as well, so another formula
approach. If the range in question, which I'll denote RNG, is 1D, then

=CELL("Address",INDEX(RNG,MATCH(MIN(RNG),RNG,0) ))

I could not get this to work with a two column range. It seems MATCH does not
like a 2 column array (xl2002) on my machine.


should return the address of the cell containing the first instance of
the minimum value. If RNG could be 2D, then if there could be multiple
instances of the minimum value, which should be considered the first
instance: the one in the leftmost column or the one in the topmost row?

Leftmost column (array formula):
=CELL("Address",INDEX(RNG,MATCH(MIN(RNG),INDEX(RN G,0,
MIN(IF(RNG=MIN(RNG),COLUMN(RNG)))),0),MIN(IF(RNG= MIN(RNG),COLUMN(RNG)))))

Topmost row (array formula):
=CELL("Address",INDEX(RNG,MIN(IF(RNG=MIN(RNG),ROW (RNG))),
MATCH(MIN(RNG),INDEX(RNG,MIN(IF(RNG=MIN(RNG),ROW( RNG))),0),0)))


--ron

Hi Ron,

[...]

Any idea why he's having what seems like a problem with the VBA
Find function in XL2003?

This may be a red herring, but I note that Jim switched machines and
possibly, therefore OS.

[...]

I could not get this to work with a two column range. It seems MATCH
does not like a 2 column array (xl2002) on my machine.

Harlan said:

If the range in question, which I'll denote RNG, is 1D, then

which would precluse a 2-column array.

---
Regards,
Norman



"Ron Rosenfeld" wrote in message
...
On 9 Dec 2005 17:20:12 -0800, "Harlan Grove" wrote:

Ron Rosenfeld wrote...
...
Since I see this post in programming, I will assume you want a VBA
solution:

====================================
Option Explicit
Function MinCellAdr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)

MinCellAdr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address

End Function
...

Already pointed out .Find fails when called in udfs in XL97, but
irrelevant to OP.

Any idea why he's having what seems like a problem with the VBA Find
function
in XL2003?



The OP's message was crossposted to w.f as well, so another formula
approach. If the range in question, which I'll denote RNG, is 1D, then

=CELL("Address",INDEX(RNG,MATCH(MIN(RNG),RNG,0)) )

I could not get this to work with a two column range. It seems MATCH does
not
like a 2 column array (xl2002) on my machine.


should return the address of the cell containing the first instance of
the minimum value. If RNG could be 2D, then if there could be multiple
instances of the minimum value, which should be considered the first
instance: the one in the leftmost column or the one in the topmost row?

Leftmost column (array formula):
=CELL("Address",INDEX(RNG,MATCH(MIN(RNG),INDEX(R NG,0,
MIN(IF(RNG=MIN(RNG),COLUMN(RNG)))),0),MIN(IF(RNG =MIN(RNG),COLUMN(RNG)))))

Topmost row (array formula):
=CELL("Address",INDEX(RNG,MIN(IF(RNG=MIN(RNG),RO W(RNG))),
MATCH(MIN(RNG),INDEX(RNG,MIN(IF(RNG=MIN(RNG),ROW (RNG))),0),0)))


--ron

On Sat, 10 Dec 2005 02:22:34 -0000, "Norman Jones"
wrote:

If the range in question, which I'll denote RNG, is 1D, then

which would precluse a 2-column array.

Ah. Now I see that 1D = 1 dimension.

thanks


--ron

If you only want to use out-of -the-box Excel functions...try this:

A1: (some range reference, like B1:L10)
A2:
=ADDRESS(SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*ROW(INDIRECT(A1 ))),SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*COLUMN(INDIRECT (A1))))

That will return the address of the cell that contains the lowest value.
Note 1: If there are duplicate minimum values, It will return the address of
it will return the location of the first.

Note 2: for simplicity sake, the referenced range cannot contain Blank
Cells. If the range may contain blanks, the formula becomes more cumbersome.

Does that help?

***********
Regards,
Ron


"Mike H" wrote:

Hello, I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks

Ron,

A modification is needed to handle duplicate occurrence of the range-minimum.

I also thought of a similar formula first, but realized that the formula
won't work if there are duplicate minimum values; it will not return the
location of the first occurrence of the minimum; it would, on the other hand,
ADD all the row numbers of cells containing the minimum, and similarly add
the corresponding column numbers, and return an incorrect cell address as the
answer. For example, if B1 and B2 contain the range-minimum, the formula
will return "D3" which corresponds to ADDRESS(3,4).

Regards,
B. R. Ramachandran

"Ron Coderre" wrote:

If you only want to use out-of -the-box Excel functions...try this:

A1: (some range reference, like B1:L10)
A2:
=ADDRESS(SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*ROW(INDIRECT(A1 ))),SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*COLUMN(INDIRECT (A1))))

That will return the address of the cell that contains the lowest value.
Note 1: If there are duplicate minimum values, It will return the address of
it will return the location of the first.

Note 2: for simplicity sake, the referenced range cannot contain Blank
Cells. If the range may contain blanks, the formula becomes more cumbersome.

Does that help?

***********
Regards,
Ron


"Mike H" wrote:

Hello, I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks

Good point....and nice catch. Thanks!
My comment about the first min value was incorrect.

I think the point I was trying to make was this:
In it's most vanilla scenario (only one min value), the formula is pretty
darn long and complications would only make it more unwieldy.

Hmmmm....seems like I could have just said that in the first place, eh?

Anyway, since we're on the topic, here's the formula for finding the first
occurrence of the minimum value in a range referenced by text in Cell A1:

=ADDRESS(SUMPRODUCT(MIN(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*ROW(INDIRECT(A1 ))+(((INDIRECT(A1))<MIN(INDIRECT(A1)))*10^99))),S UMPRODUCT(MIN(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*COLUMN(INDIRECT (A1))+(((INDIRECT(A1))<MIN(INDIRECT(A1)))*10^99)) ))

....and THAT only works as long as the minimum is not zero when there are
blank cells in the range (which would equate to zero).

***********
Regards,
Ron


"B. R.Ramachandran" wrote:

Ron,

A modification is needed to handle duplicate occurrence of the range-minimum.

I also thought of a similar formula first, but realized that the formula
won't work if there are duplicate minimum values; it will not return the
location of the first occurrence of the minimum; it would, on the other hand,
ADD all the row numbers of cells containing the minimum, and similarly add
the corresponding column numbers, and return an incorrect cell address as the
answer. For example, if B1 and B2 contain the range-minimum, the formula
will return "D3" which corresponds to ADDRESS(3,4).

Regards,
B. R. Ramachandran

"Ron Coderre" wrote:

If you only want to use out-of -the-box Excel functions...try this:

A1: (some range reference, like B1:L10)
A2:
=ADDRESS(SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*ROW(INDIRECT(A1 ))),SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*COLUMN(INDIRECT (A1))))

That will return the address of the cell that contains the lowest value.
Note 1: If there are duplicate minimum values, It will return the address of
it will return the location of the first.

Note 2: for simplicity sake, the referenced range cannot contain Blank
Cells. If the range may contain blanks, the formula becomes more cumbersome.

Does that help?

***********
Regards,
Ron


"Mike H" wrote:

Hello, I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks

Ron Coderre wrote...
If you only want to use out-of -the-box Excel functions...try this:

A1: (some range reference, like B1:L10)
A2:
=ADDRESS(SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))
*ROW(INDIRECT(A1))),SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))
*COLUMN(INDIRECT(A1))))
....

FWIW, this only works when there's a single instance of the minimum
value.

Please fix your system date. It is frustrating the housekeeping in the
newsgroups.

--
Kind regards,

Niek Otten

"Mike H" wrote in message
...
Hello, I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks

Assuming that the range of interest is a vector (like A2:A9 or B2:F2)...

A different take...


Let column A house the following from A1 on:


Entries
34
23
27
33
34
31
20
12

In B1 enter the label: d-Rank

In B2 enter & copy down:

=RANK(A2,$A$2:$A$9,1)+COUNTIF($A$2:A2,A2)-1

In C1 enter:

=MIN(A2:A9)

In C2 enter: 1 (This manually entered parameter indicates that you want
a Top 1 list.)

In C3 enter:

=MAX(IF(INDEX(A2:A9,MATCH(C2,B2:B9,0))=A2:A9,B2:B9 ))-C2

which you must confirm with control+shift+enter, not just with
enter.

This formula calculates the number of ties that the Min value might have
in the range of interest.

In D2 enter the label: Address

In D2 enter & copy down:

=IF(ROWS(D$2:D2)<=$C$2+$C$3,CELL("Address",INDEX($ A$2:$A$9,MATCH(ROWS(D$2:D2),$B$2:$B$9,0))),""))


Note that the formula is anchored to the first cell (i.e., D2) it is
entered by the ROWS(D$2:D2) bit.

The result list that you get in D consists of:

$A$2
$A$6
$A$9

Mike H wrote:
Hello, I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks