Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Divide a number into multiple cells | Excel Discussion (Misc queries) | |||
Problems comparing data from multiple blank cells | Excel Worksheet Functions | |||
count 2 nonblank cells on multiple worksheets | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
how can i ignore blank cells when multiple cells? | Excel Worksheet Functions |