How to find which column or row the Min() value is coming from
 

My data are in 19 columns and 600 rows. I need to find the minimum value in
each row. At the top of the columns are descriptions (e.g. Pen A, Pen B...).
I want to see which column ("Pen X") the minimum for that row came from. I
can get the minimum value in the row using min(), but it is the min value I
need, but the column name that has that value. I have searched for two hours
and cannot figure out how to do this! Can anyone help?

Thanks.

Luanne wrote:
My data are in 19 columns and 600 rows. I need to find the minimum value in
each row. At the top of the columns are descriptions (e.g. Pen A, Pen B...).
I want to see which column ("Pen X") the minimum for that row came from. I
can get the minimum value in the row using min(), but it is the min value I
need, but the column name that has that value. I have searched for two hours
and cannot figure out how to do this! Can anyone help?

Thanks.

Assuming your data, including the column headings, begins in Cell A1 in
a range named "dataRange", you can insert the following formula in Cell
T2 and copy down:

=OFFSET($A$1,0,MATCH(MIN(INDEX(dataRange,ROW(A2),0 )),INDEX(dataRange,ROW(A2),0),0)-1)

Alan Beban

Loanne
If your headers are in a1 to s1
with data in A2:S601
t2 =index(A$1:S$1,1,match(min(A2:S2),A2:S2,0))
Copy T2 to T2:T601
Bob


On Fri, 29 Apr 2005 11:26:05 -0700, Luanne
wrote:

My data are in 19 columns and 600 rows. I need to find the minimum value in
each row. At the top of the columns are descriptions (e.g. Pen A, Pen B...).
I want to see which column ("Pen X") the minimum for that row came from. I
can get the minimum value in the row using min(), but it is the min value I
need, but the column name that has that value. I have searched for two hours
and cannot figure out how to do this! Can anyone help?

Thanks.

This formula will retrun the cell reference

=CELL("address",INDEX($A$1:$E$20,MIN(IF($A$3:$E$20 =MIN($A$3:$E$20),ROW($A$3:$E$20))),MIN(IF($A$3:$E$ 20=MIN($A$3:$E$20),COLUMN($A$3:$E$20)))))

entered with ctrl + shift & enter

Adapt to fit your tables ranges etc, my formula was based on that the
headers were in row2, numeric data started in 3 so note that the endex part
starts from the first row, that way you don't have to offset the results from
the min part

Regards,

Peo Sjoblom

"Luanne" wrote:

My data are in 19 columns and 600 rows. I need to find the minimum value in
each row. At the top of the columns are descriptions (e.g. Pen A, Pen B...).
I want to see which column ("Pen X") the minimum for that row came from. I
can get the minimum value in the row using min(), but it is the min value I
need, but the column name that has that value. I have searched for two hours
and cannot figure out how to do this! Can anyone help?

Thanks.

As an example enter 1 in A1 and 2 in B1, Pen A in A2 and Pen B in B2. Let the
cells A3 to B6 contain data. In cell F3 enter
=SUMPRODUCT($A$1:$B$1,--($A3:$B3=MIN($A3:$B3)))
In cell G3 enter
=HLOOKUP(F3,$A$1:$B$4,2)
Copy cells F3 and G3 down.

"Luanne" wrote:

My data are in 19 columns and 600 rows. I need to find the minimum value in
each row. At the top of the columns are descriptions (e.g. Pen A, Pen B...).
I want to see which column ("Pen X") the minimum for that row came from. I
can get the minimum value in the row using min(), but it is the min value I
need, but the column name that has that value. I have searched for two hours
and cannot figure out how to do this! Can anyone help?

Thanks.