Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Alex
 
Posts: n/a
Default 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?
  #2   Report Post  
Leo Heuser
 
Posts: n/a
Default

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?



  #3   Report Post  
Max
 
Posts: n/a
Default

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?



  #4   Report Post  
Alex
 
Posts: n/a
Default

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?




  #5   Report Post  
Max
 
Posts: n/a
Default

.. 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
----




  #6   Report Post  
Alex
 
Posts: n/a
Default

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



  #7   Report Post  
Max
 
Posts: n/a
Default

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!!!



  #8   Report Post  
Leo Heuser
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Workaround for Cell Display Limitation in Excel 2000 Kevin Excel Discussion (Misc queries) 5 April 20th 05 11:33 PM
Range vs. lowest #, 2nd lowest #, 3rd lowest #, etc jwebb Excel Discussion (Misc queries) 2 March 9th 05 01:38 PM
Conditional display of a .jpeg file? sbhogle Excel Discussion (Misc queries) 2 November 30th 04 06:57 AM
How can I get the lowest price, second lowest etc. from a range o. Robin Excel Worksheet Functions 2 November 9th 04 01:23 PM
Combine & Display “Fixed” & “Automatically Updated” Date Parts texcel Excel Worksheet Functions 1 November 1st 04 06:38 PM


All times are GMT +1. The time now is 09:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"