Comparing multiple cells

[scoobydoo2006]

Hello All!
I'm trying to do a compare of dollar values in a given row with no
sequential cells (3). The data format is currency and I am looking fo
the least in price between the three. Some cells do contain $0.00. Th
minimum price > 0 of course, has a code("S","UL",or"US") that needs t
be replicated in a given cell in that row(column AD). The compar
columns/row are H,M, & R. I do need to repeat the comparison all th
way down the worksheet. Worksheet data begins in row 6.

Thank you for any help you can offe

 

[Bernard Liengme]

Assuming your data has no values larger than a million, this gives the value
of the non-zero minimum
=MIN(H6+(H6=0)*1000000,M6+(M6=0)*1000000,R6+(R6=0)*1000000)

But we cannot wrap this in a MATCH formula because H6, M6 and R6 are
non-contiguous.
1) can you be sure that intervening cells will not contain numbers?
or (best solution)
2) could you use a 'helper' sheet with formulas such as =Sheet1!H6,
=Sheet1!M6, =Sheet1!R6 is cells A1:C1 and copied down the rows as needed?

Let us know and a solution might suggest itself.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"scoobydoo2006" <[email protected]>
wrote in message
news:[email protected]...

 

[scoobydoo2006]

Actually I can use a helper sheet and make the column sequential (A-C)
The Min formula you supplied does identify the least cost item. I no
need to convert that to the given code for that vendors price colum
(A-C). Costs will not exceed $1000.00. So if you can help with the cod
conversion I think we have it. Column A if selected as the Min in Colum
D would convert to "S" in col D instead of the actual cost. Col
converts to "UL" and Col C to "US".

Thank Yo

 

[Bernard Liengme]

Let's say the real data in Sheet1
On Sheet2, row 5 , start with A5, enter your code S, UL, US
In A6 enter =Sheet1!H6, in B6 =Sheet1!M6, in C6 =Sheet1!R6
Copy A6:C6 down the sheet as far as needed
On Sheet1 (real data sheet) in the cell where you want the code for 6, enter
=INDEX(Sheet2!$A$5:$C$5,1,MATCH(MIN(Sheet2!A6:C6),Sheet2!A6:C6,0))
Copy this down the column as far as needed
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"scoobydoo2006" <[email protected]>
wrote in message
news:[email protected]...