Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Function to find the address of a cell

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
  #2   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Function to find the address of a cell

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



  #3   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default Function to find the address of a cell

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



  #4   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
B. R.Ramachandran
 
Posts: n/a
Default Function to find the address of a cell

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



  #5   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Function to find the address of a cell

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





  #6   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
JMay
 
Posts: n/a
Default Function to find the address of a cell

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



  #7   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Function to find the address of a cell

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
  #8   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Tom Ogilvy
 
Posts: n/a
Default Function to find the address of a cell

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



  #9   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Function to find the address of a cell

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
  #10   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
JMay
 
Posts: n/a
Default Function to find the address of a cell

I'm running excel 2003.
I referenced a small range A2:A8; containing
4,6,9,7,5,8,2=MinCellAddr(A2:A8)


Function MinCellAddr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)
MinCellAddr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address
End Function

"Ron Rosenfeld" wrote in message
...
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





  #11   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Function to find the address of a cell

On Fri, 9 Dec 2005 07:47:30 -0500, "JMay" wrote:

I'm running excel 2003.
I referenced a small range A2:A8; containing
4,6,9,7,5,8,2=MinCellAddr(A2:A8)


Function MinCellAddr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)
MinCellAddr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address
End Function


I don't know why you are getting the VALUE error.

I typed your numbers into A2:A8

I copied the code you just posted, and pasted into a module.

To open the module, I ensured the proper project was highlighted in the Project
Explorer window of the VB Editor. I then selected Insert/Module from the top
menu bar, and just pasted in the code you posted (which is a copy of mine).

I then pasted the =MinCellAddr(A2:A8) function into A1 and it returned $A$8

I'm not sure what's happening at your machine. But try this code with an
expanded FIND function:

Function MinCellAddr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)
MinCellAddr = rg.Find(What:=MinNum, _
LookIn:=xlValues, LookAt:=xlWhole, _
searchorder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False, matchbyte:=False).Address
End Function

If that doesn't work, we'll have to debug the code on your machine, to see
where it's going wrong.

As a first step, you could place a breakpoint next to the "End Function" line
to see if the function is even completing. If it is, then there may be
something unexpected about your data. If it is not completing. then place
breakpoints next to each line to narrow down the location of the issue.


--ron
  #12   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Jim May
 
Posts: n/a
Default Function to find the address of a cell

Ron:
Thanks so much for the explanation. I tried everything you recommended,
(even the expanded code - This time on my AT-WORK PC - Orig is on my Home
PC);But all without success. Looks like time to
"we'll have to debug the code on your machine, to see
where it's going wrong" << I even (already) set the Breakpoints as you
suggest <<before End Function, but get no unusual results.
Hummmmm....
Tks,
Jim

"Ron Rosenfeld" wrote:

On Fri, 9 Dec 2005 07:47:30 -0500, "JMay" wrote:

I'm running excel 2003.
I referenced a small range A2:A8; containing
4,6,9,7,5,8,2=MinCellAddr(A2:A8)


Function MinCellAddr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)
MinCellAddr = rg.Find(what:=MinNum, _
LookIn:=xlValues, lookat:=xlWhole).Address
End Function


I don't know why you are getting the VALUE error.

I typed your numbers into A2:A8

I copied the code you just posted, and pasted into a module.

To open the module, I ensured the proper project was highlighted in the Project
Explorer window of the VB Editor. I then selected Insert/Module from the top
menu bar, and just pasted in the code you posted (which is a copy of mine).

I then pasted the =MinCellAddr(A2:A8) function into A1 and it returned $A$8

I'm not sure what's happening at your machine. But try this code with an
expanded FIND function:

Function MinCellAddr(rg As Range) As String
Dim MinNum As Double
MinNum = Application.WorksheetFunction.Min(rg)
MinCellAddr = rg.Find(What:=MinNum, _
LookIn:=xlValues, LookAt:=xlWhole, _
searchorder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False, matchbyte:=False).Address
End Function

If that doesn't work, we'll have to debug the code on your machine, to see
where it's going wrong.

As a first step, you could place a breakpoint next to the "End Function" line
to see if the function is even completing. If it is, then there may be
something unexpected about your data. If it is not completing. then place
breakpoints next to each line to narrow down the location of the issue.


--ron

  #13   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Function to find the address of a cell

On Fri, 9 Dec 2005 07:34:03 -0800, "Jim May"
wrote:

Ron:
Thanks so much for the explanation. I tried everything you recommended,
(even the expanded code - This time on my AT-WORK PC - Orig is on my Home
PC);But all without success. Looks like time to
"we'll have to debug the code on your machine, to see
where it's going wrong" << I even (already) set the Breakpoints as you
suggest <<before End Function, but get no unusual results.
Hummmmm....
Tks,
Jim


When you write "get no unusual results" do you mean that the code stopped at
each breakpoint, or not?

If it did stop at each breakpoint, after this line:

MinNum = Application.WorksheetFunction.Min(rg)

has been executed (in other words, when the code has stopped on the following
line), float your cursor over the MinNum and see what the value is.

If it says MinNum=0, then the problem is that the values in rg are text.

If you manually entered those values, then possibly the cells were formatted as
text before you entered your values (and changing the format will not change
this).

If the contents are the result of a formula, then we should look at the
formula.

If the contents were imported from an html document, web page, or some other
database, then we should determine what, exactly, is in there so as to clean it
up in the simplest fashion.


--ron
  #14   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Jim May
 
Posts: n/a
Default Function to find the address of a cell

Ron:
I set up both Screens side-by-side - Spreadsheet and VBE
On the original Code (Macro) I placed the Breakpoint next to the End
Function Line
On the spreadsheet Cell A1 (where =MinCellAddr(A2:A8) is the content I did an
Edit (F2) and re-entered it. AS I did the Cursor jumped to Cell A2, as
expected.
I then reset the Breakpoint up one line (that is, to the beginning of the
multiline code using the continuing (space/underscore). Then on Cell A1
again - Edit (F2) and the same code line code just set turned YELLOW
(indicating of course that it is the next line To BE RUN..) Placing my
cursor over the variable MinNum in the line before and also in the current
line --tooltip showed 2 (my correct minimum #).

In Cell G10 I entered =ISNUMBER(A2) and copied down 6 rows - ALL displayed
TRUE -- The values in A2:A8 are all numbers - constants (I think this is
proper
termonology)

In cell H4 if I enter =MIN(A2:A8) - 2 displays (Which is correct))

Wow, what else is there (to do)?
Appreciate your assistance,
Jim



"Ron Rosenfeld" wrote:

On Fri, 9 Dec 2005 07:34:03 -0800, "Jim May"
wrote:

Ron:
Thanks so much for the explanation. I tried everything you recommended,
(even the expanded code - This time on my AT-WORK PC - Orig is on my Home
PC);But all without success. Looks like time to
"we'll have to debug the code on your machine, to see
where it's going wrong" << I even (already) set the Breakpoints as you
suggest <<before End Function, but get no unusual results.
Hummmmm....
Tks,
Jim


When you write "get no unusual results" do you mean that the code stopped at
each breakpoint, or not?

If it did stop at each breakpoint, after this line:

MinNum = Application.WorksheetFunction.Min(rg)

has been executed (in other words, when the code has stopped on the following
line), float your cursor over the MinNum and see what the value is.

If it says MinNum=0, then the problem is that the values in rg are text.

If you manually entered those values, then possibly the cells were formatted as
text before you entered your values (and changing the format will not change
this).

If the contents are the result of a formula, then we should look at the
formula.

If the contents were imported from an html document, web page, or some other
database, then we should determine what, exactly, is in there so as to clean it
up in the simplest fashion.


--ron

  #15   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Function to find the address of a cell

On Fri, 9 Dec 2005 15:47:02 -0800, "Jim May"
wrote:

Ron:
I set up both Screens side-by-side - Spreadsheet and VBE
On the original Code (Macro) I placed the Breakpoint next to the End
Function Line
On the spreadsheet Cell A1 (where =MinCellAddr(A2:A8) is the content I did an
Edit (F2) and re-entered it. AS I did the Cursor jumped to Cell A2, as
expected.
I then reset the Breakpoint up one line (that is, to the beginning of the
multiline code using the continuing (space/underscore). Then on Cell A1
again - Edit (F2) and the same code line code just set turned YELLOW
(indicating of course that it is the next line To BE RUN..) Placing my
cursor over the variable MinNum in the line before and also in the current
line --tooltip showed 2 (my correct minimum #).

In Cell G10 I entered =ISNUMBER(A2) and copied down 6 rows - ALL displayed
TRUE -- The values in A2:A8 are all numbers - constants (I think this is
proper
termonology)

In cell H4 if I enter =MIN(A2:A8) - 2 displays (Which is correct))

Wow, what else is there (to do)?
Appreciate your assistance,
Jim



If I understand you correctly, the first time through, the routine did NOT stop
at the End Function line. That means the routine exited prematurely on the
"Find" line.

For some reason, it seems that the FIND function is not working on your
machine. Very strange. And since I have XL2002, I can't replicate that.

Try this modification:

=============================
Function MinCellAddr(rg As Range) As String
Dim MinNum As Double
Dim c As Range
MinNum = Application.WorksheetFunction.Min(rg)

For Each c In rg
If c.Value = MinNum Then
MinCellAddr = c.Address
Exit Function
End If
Next c
End Function
===============================

Using a For/Next loop will be slower than using the FIND function, but it
should work OK. You'd probably only notice the slow down on a very large
range.

I don't know why the FIND function isn't working. If the For/Next loop is too
slow, one thing you might try is to record a macro using the Find operation in
Excel, and, using the code that is recorded as a baseline, make the appropriate
modifications to use it in this routine.


--ron


  #16   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default Function to find the address of a cell

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)))

  #17   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default Function to find the address of a cell

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.

  #18   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Function to find the address of a cell

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
  #19   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Norman Jones
 
Posts: n/a
Default Function to find the address of a cell

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



  #20   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Function to find the address of a cell

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


  #21   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Jim May
 
Posts: n/a
Default Function to find the address of a cell

Ron:
That worked (the new looping code) in a flash!!
Thanks, I'll spend more time with it tomorrow
and be back in touch with you.
Tks again for your perserverance (sp?);
Jim May

"Ron Rosenfeld" wrote:

On Fri, 9 Dec 2005 15:47:02 -0800, "Jim May"
wrote:

Ron:
I set up both Screens side-by-side - Spreadsheet and VBE
On the original Code (Macro) I placed the Breakpoint next to the End
Function Line
On the spreadsheet Cell A1 (where =MinCellAddr(A2:A8) is the content I did an
Edit (F2) and re-entered it. AS I did the Cursor jumped to Cell A2, as
expected.
I then reset the Breakpoint up one line (that is, to the beginning of the
multiline code using the continuing (space/underscore). Then on Cell A1
again - Edit (F2) and the same code line code just set turned YELLOW
(indicating of course that it is the next line To BE RUN..) Placing my
cursor over the variable MinNum in the line before and also in the current
line --tooltip showed 2 (my correct minimum #).

In Cell G10 I entered =ISNUMBER(A2) and copied down 6 rows - ALL displayed
TRUE -- The values in A2:A8 are all numbers - constants (I think this is
proper
termonology)

In cell H4 if I enter =MIN(A2:A8) - 2 displays (Which is correct))

Wow, what else is there (to do)?
Appreciate your assistance,
Jim



If I understand you correctly, the first time through, the routine did NOT stop
at the End Function line. That means the routine exited prematurely on the
"Find" line.

For some reason, it seems that the FIND function is not working on your
machine. Very strange. And since I have XL2002, I can't replicate that.

Try this modification:

=============================
Function MinCellAddr(rg As Range) As String
Dim MinNum As Double
Dim c As Range
MinNum = Application.WorksheetFunction.Min(rg)

For Each c In rg
If c.Value = MinNum Then
MinCellAddr = c.Address
Exit Function
End If
Next c
End Function
===============================

Using a For/Next loop will be slower than using the FIND function, but it
should work OK. You'd probably only notice the slow down on a very large
range.

I don't know why the FIND function isn't working. If the For/Next loop is too
slow, one thing you might try is to record a macro using the Find operation in
Excel, and, using the code that is recorded as a baseline, make the appropriate
modifications to use it in this routine.


--ron

  #22   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default Function to find the address of a cell

On Fri, 9 Dec 2005 19:10:02 -0800, "Jim May"
wrote:

Ron:
That worked (the new looping code) in a flash!!
Thanks, I'll spend more time with it tomorrow
and be back in touch with you.
Tks again for your perserverance (sp?);
Jim May


Glad to hear that!

By the way, if you need to handle multiple occurrences of the Minimum Value, it
would be simple to modify the routine to return them, either as a single string
in one cell, separated by spaces, commas or whatever; or as an array (vertical
or horizontal) with one entry per cell.



--ron
  #23   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default Function to find the address of a cell

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


  #24   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
Mike H
 
Posts: n/a
Default Function to find the address of a cell

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I find the cell address of the 2nd largest of a set? Mr. Snrub Excel Discussion (Misc queries) 4 May 30th 05 12:53 PM
Function syntax to compare cell contents ES Excel Worksheet Functions 2 May 18th 05 03:53 PM
How do I obtain the address of a cell using the vlookup function? Spock Excel Worksheet Functions 2 May 16th 05 06:35 PM
Function to determine if any cell in a range is contained in a given cell [email protected] Excel Worksheet Functions 3 February 7th 05 05:19 PM
Is there a function for "not isblank" (find a cell that has a val. Jim Excel Worksheet Functions 3 December 8th 04 08:29 AM


All times are GMT +1. The time now is 08:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"