Comparing multiple cells

[scoobydoo2006]

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

Thank you for any help you can offer

[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]...
>
> Hello All!
> I'm trying to do a compare of dollar values in a given row with non
> sequential cells (3). The data format is currency and I am looking for
> the least in price between the three. Some cells do contain $0.00. The
> minimum price > 0 of course, has a code("S","UL",or"US") that needs to
> be replicated in a given cell in that row(column AD). The compare
> columns/row are H,M, & R. I do need to repeat the comparison all the
> way down the worksheet. Worksheet data begins in row 6.
>
> Thank you for any help you can offer
>
>
> --
> scoobydoo2006
> ------------------------------------------------------------------------
> scoobydoo2006's Profile:
> http://www.excelforum.com/member.php...o&userid=35372
> View this thread: http://www.excelforum.com/showthread...hreadid=551446
>

[scoobydoo2006]

Got it fixed. Thanks for all of your help!

[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]...
>
> 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 now
> need to convert that to the given code for that vendors price column
> (A-C). Costs will not exceed $1000.00. So if you can help with the code
> conversion I think we have it. Column A if selected as the Min in Column
> D would convert to "S" in col D instead of the actual cost. Col B
> converts to "UL" and Col C to "US".
>
> Thank You
>
>
> --
> scoobydoo2006
> ------------------------------------------------------------------------
> scoobydoo2006's Profile:
> http://www.excelforum.com/member.php...o&userid=35372
> View this thread: http://www.excelforum.com/showthread...hreadid=551446
>