Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MIN Function
How do I attach the corresponding vendor name (column header) to a MIN
function value when comparing data in the same row horizontally from left to right? I am trying to find the minimum price for 300 identical item names(rows) from bids entered by 25 different vendors (columns). I am trying to show the corresponding vendor name next to the minimum price. My spreadsheet structu Item Name, Vendor 1 Price, Vendor 2 Price,..., Vendor 25 Price Item 1, 0.10, 0.25,..., 0.07 .... Item 300, 0.99, 0.76,..., 0.85 Desired Result: Item Name, Min Price, Min Vendor Name Item 1, 0.07, Vendor 25 .... Item 300, 0.73, Vendor 2 Appreciate any info. Thanks. |
#2
|
|||
|
|||
Hi Wondering0407,
This should help. Assuming your bid table starts in cell A1 of Sheet1, try the following formulas - In the first cell below your desired result headers (Item name): =Sheet1!A2 In the next cell to the right (Min Price): =MIN(Sheet1!2:2) And in the next cell (Min Vedor Name): =INDEX(Sheet1!$1:$1,1,MATCH(B2,Sheet1!2:2,0)) I hope this helps, Gary (DropTwoThruFiveForDirext) |
#3
|
|||
|
|||
This might suffice ..
Assuming the sample table below is in Sheet1, in A1:E6 ItemN Vend1 Vend2 Vend3 Vend4 Item1 1.66 1.14 1.16 1.59 Item2 1.94 1.67 1.15 1.00 Item3 166 147 150 109 Item4 166 155 163 116 Item5 194 125 105 180 In Sheet2 ------------ Assume the 5 items are listed in col A, A2 down in the *same order* that they appear in Sheet1, viz.: ItemN MinPrice MinVend Item1 Item2 Item3 Item4 Item5 Put in B2: =MIN(Sheet1!B2:E2) Put in C2: =INDEX(Sheet1!$B$1:$E$1,MATCH(B2,Sheet1!B2:E2,0)) Select B2:C2 and copy down You'll get: ItemN MinPr MinVend Item1 1.14 Vend2 Item2 1.00 Vend4 Item3 109.00 Vend4 Item4 116.00 Vend4 Item5 105.00 Vend3 Adapt to suit .. (Note that its assumed there won't be any ties in the minimum prices for any row) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Wondering0407" wrote in message ... How do I attach the corresponding vendor name (column header) to a MIN function value when comparing data in the same row horizontally from left to right? I am trying to find the minimum price for 300 identical item names(rows) from bids entered by 25 different vendors (columns). I am trying to show the corresponding vendor name next to the minimum price. My spreadsheet structu Item Name, Vendor 1 Price, Vendor 2 Price,..., Vendor 25 Price Item 1, 0.10, 0.25,..., 0.07 ... Item 300, 0.99, 0.76,..., 0.85 Desired Result: Item Name, Min Price, Min Vendor Name Item 1, 0.07, Vendor 25 ... Item 300, 0.73, Vendor 2 Appreciate any info. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
Trim function doesn't clean out ASCII Code 160 (Space) | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |