Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to display the column or cell the MIN() result came from?
Hello,
I have an Excel sheet with product prices from diffrent suppliers; each supplier has a column (with their company name in the first cel), each row represents a price for a product. There are many suppliers and many products (about 20 columns and 200 rows). I need to find out which supplier has the lowest price for each product, so in the last column, I am using the Min() function to determine the lowest price per row; BUT I need a way to display which cell or better yet which column or supplier the Min() result came from. Can anyone help me out please? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to display the column or cell the MIN() result came from?
=match(min(4:4),4:4,false) would tell you the first column (by number) that
has the smallest value in row 4. "Fadi" wrote: Hello, I have an Excel sheet with product prices from diffrent suppliers; each supplier has a column (with their company name in the first cel), each row represents a price for a product. There are many suppliers and many products (about 20 columns and 200 rows). I need to find out which supplier has the lowest price for each product, so in the last column, I am using the Min() function to determine the lowest price per row; BUT I need a way to display which cell or better yet which column or supplier the Min() result came from. Can anyone help me out please? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to display the column or cell the MIN() result came from?
Fadi, here is one way, change range to your range, if two valves are the min
will show the first one =CELL("address",INDEX(A2:J2,MATCH(MIN(A2:J2),A2:J2 ,0))) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Fadi" wrote in message ... Hello, I have an Excel sheet with product prices from diffrent suppliers; each supplier has a column (with their company name in the first cel), each row represents a price for a product. There are many suppliers and many products (about 20 columns and 200 rows). I need to find out which supplier has the lowest price for each product, so in the last column, I am using the Min() function to determine the lowest price per row; BUT I need a way to display which cell or better yet which column or supplier the Min() result came from. Can anyone help me out please? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to display the column or cell the MIN() result came from?
=INDIRECT("R1C"&MATCH(MIN(B2:E2),B2:E2,0)+1,FALSE)
here is a formula for the first line in the following example. Adjust to encompasses all of your data, and then copy it down. You will notice if two are equal it returns the first company in the list. apple brown cat dog a1x 1 2 3 4 apple bv4 4 3 1 2 cat 5th 3 2 2 4 brown tyu 2 1 3 3 brown i97 4 3 2 1 dog "Fadi" wrote: Hello, I have an Excel sheet with product prices from diffrent suppliers; each supplier has a column (with their company name in the first cel), each row represents a price for a product. There are many suppliers and many products (about 20 columns and 200 rows). I need to find out which supplier has the lowest price for each product, so in the last column, I am using the Min() function to determine the lowest price per row; BUT I need a way to display which cell or better yet which column or supplier the Min() result came from. Can anyone help me out please? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to display the column or cell the MIN() result came from?
Thanks guys, it worked!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Currency to Text | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |