Comparing multiple cells
 

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

Comparing multiple cells
 

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"
wrote in message
news:scoobydoo2006.29chc1_1150209003.3321@excelfor um-nospam.com...

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

Comparing multiple cells
 

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

Comparing multiple cells
 

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"
wrote in message
news:scoobydoo2006.29coqn_1150218603.7596@excelfor um-nospam.com...

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