Function to find the address of a cell

  • Thread starter Thread starter Ron Rosenfeld
  • Start date Start date
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:
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
Click to expand...

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
 
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
 
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
 
Back
Top