How do I display the lowest value in a row?
 

I want to show a spreadsheet that will search for the lowest value in the row
and then display the column name at the end. It is for a price list so the
name of the supplier will be accross the top, the product name down the left
hand side. The formula will then review the values and select the cheapest
then display the suppiers name next to the product name? Does anyone know if
this can be done? Thanks for you help?

Alex

One way:

Assuming Suppliers in B1:M1 and products in A2 and down,
enter this formula in N2:

=INDEX($B$1:$M$1,MATCH(MIN(B2:M2),B2:M2,0))

Copy N2 down with the fill handle (the little square in
the lower right corner of the cell).

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Alex" skrev i en meddelelse
...
I want to show a spreadsheet that will search for the lowest value in the
row
and then display the column name at the end. It is for a price list so the
name of the supplier will be accross the top, the product name down the
left
hand side. The formula will then review the values and select the cheapest
then display the suppiers name next to the product name? Does anyone know
if
this can be done? Thanks for you help?

One way which might suffice ..

Assuming supplier names in B1:E1,
unit prices in B2:E2, B3:E3, etc
and no ties in lowest unit prices

Put in F2
=INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0))
Copy down

In the event of a tie(s), the formula will return only the leftmost supplier
...
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Alex" wrote in message
...
I want to show a spreadsheet that will search for the lowest value in the
row
and then display the column name at the end. It is for a price list so the
name of the supplier will be accross the top, the product name down the
left
hand side. The formula will then review the values and select the cheapest
then display the suppiers name next to the product name? Does anyone know
if
this can be done? Thanks for you help?

That worked perfectly thanks!! If no supplier has the stock, and therefor no
prices have been entered can i display 'No Stock'?? Thanks Leo!

"Leo Heuser" wrote:

Alex

One way:

Assuming Suppliers in B1:M1 and products in A2 and down,
enter this formula in N2:

=INDEX($B$1:$M$1,MATCH(MIN(B2:M2),B2:M2,0))

Copy N2 down with the fill handle (the little square in
the lower right corner of the cell).

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Alex" skrev i en meddelelse
...
I want to show a spreadsheet that will search for the lowest value in the
row
and then display the column name at the end. It is for a price list so the
name of the supplier will be accross the top, the product name down the
left
hand side. The formula will then review the values and select the cheapest
then display the suppiers name next to the product name? Does anyone know
if
this can be done? Thanks for you help?

.. If no supplier has the stock, and therefor no
prices have been entered can i display 'No Stock'??

In the interim before Leo responds (do hang around for his response), guess
you could try this slightly revised formula in N2, and copy down:

=IF(COUNTBLANK(B2:M2)=COLUMNS(B2:M2),"No
Stock",INDEX($B$1:$M$1,MATCH(MIN(B2:M2),B2:M2,0)))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Max, your a legend thanks for your help!!!

"Max" wrote:

.. If no supplier has the stock, and therefor no
prices have been entered can i display 'No Stock'??

In the interim before Leo responds (do hang around for his response), guess
you could try this slightly revised formula in N2, and copy down:

=IF(COUNTBLANK(B2:M2)=COLUMNS(B2:M2),"No
Stock",INDEX($B$1:$M$1,MATCH(MIN(B2:M2),B2:M2,0)))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Glad it worked for you !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Alex" wrote in message
...
Max, your a legend thanks for your help!!!

You're welcome, Alex, and thanks for the feedback :-)

Since negative prices and 0 are not an option (unfortunately :-),
here is an alternative to Max's formula:

=IF(SUM(B2:M2)=0,"No stock!",INDEX($B$1:$M$1,MATCH(MINA(B2:M2),B2:M2,0) ))

LeoH


"Alex" skrev i en meddelelse
...
That worked perfectly thanks!! If no supplier has the stock, and therefor
no
prices have been entered can i display 'No Stock'?? Thanks Leo!

"Leo Heuser" wrote:

Alex