Help: how to return the col & row of result cell

  • Thread starter Thread starter cocobig
  • Start date Start date
C

cocobig

Hello,

This is my first post here. I have a question about Excel. Please hel
me. :(

Assume cells A1:A8 has numbers

Let's B1 contains "=MIN(A1:A8)"

We know that B1 will return the minimum value from cells A1:A8.

However, what if I want to know what cell has minimum value?

I am quite new to Excel, please help me. Thank you
 
Hi
to get the row number of this cell you may try
=MATCH(MIN(A1:A8),A1:A8,0)

will return the first row number (if you have two values which are
equal to the minimum you'll only get the first one)
 
=INDEX(MATCH(MIN(A:A),A:A,0),)
or
=INDEX(MATCH(MIN(A1:A8),A1:A8,0),)

Regards

Trevor
 
Hi Trevor
this won't return the cell address (or row number) but is in both cases
equla to
=MIN(A1:A8)
or
=MIN(A:A)

You have to skip the INDEX part of your formulas :-)
 
Frank, this works only when rows 1:8 are used in the formula (e.g. won't
return row # when range is A21:A25).

Just tryin to help :)
 
Dave said:
Frank, this works only when rows 1:8 are used in the formula (e.g.
won't return row # when range is A21:A25).

Just tryin to help :)
Click to expand...

Dave
correct :-)
for the OP: In this case you have to subtract an approbiate value :-9
Frank
 
Sorry to beat a dead horse Frank, but what would that value be?

=ADDRESS(MIN(IF($B$3:$B$15=MIN(B3:B15),ROW(3:15))),2)

(entered with CTRL-SHIFT-Entr)

this formula was very slightly modified from one given by Tom Ogilvy on
6/14/01 in this NG, and does return the first cell address with the lowest
number in B3:B15.
 
Dave said:
Sorry to beat a dead horse Frank, but what would that value be?

=ADDRESS(MIN(IF($B$3:$B$15=MIN(B3:B15),ROW(3:15))),2)

(entered with CTRL-SHIFT-Entr)

this formula was very slightly modified from one given by Tom Ogilvy
on 6/14/01 in this NG, and does return the first cell address with
the lowest number in B3:B15.
Click to expand...

:-)
much better!
Frank
 
Let A1:A9 house:

{"Values";33;37;33;28;35;28;37;32}

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 ( N = 1 for Top N based on the lowest value)

In C3 control+shift+enter, that is, confirm the formula with
control+shift+enter instead of just enter...

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

In D2 enter & copy down as far as needed:

=IF(ROW()-ROW(D$2)+1<=$C$3+$C$2,CELL("Address",INDEX($A$2:$A$9,MATCH(ROW()-R
OW(D$2)+1,$B$2:$B$9,0))),"")

Note that the ROW($D$2) bit anchors this formula to the first cell it is
entered to.

The results area in D will show:

{"$A$5";"$A$7";"";"";"";"";"";""}
 
Frank

in my testing, it returned the row number I expected. However, it does
suffer from the problem that it only works in relation to row 1. Not very
flexible.

Regards

Trevor
 
Hi Trevor
you're right. It would return the row number - my fault :-)
But still the INDEX function is not required. The MATCH function is
sufficient (though with the row problem not solved)
 
Back
Top