How do I display the lowest value in a row?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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).
 
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
...
 
That worked perfectly thanks!! If no supplier has the stock, and therefor no
prices have been entered can i display 'No Stock'?? Thanks Leo!
 
.. If no supplier has the stock, and therefor no
prices have been entered can i display 'No Stock'??
Click to expand...

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)))
 
Max, your a legend thanks for your help!!!

Max said:
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)))
Click to expand...
 
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
 
Back
Top