May 23 2021 08:42 PM
please help me out, i need a formula to select column name, on basis of finding the min value .
i need a formula to selection-1 and selction-2 for selecting vendor with min values
place | vendor-1 | vendor-2 | vendor-3 | vendor-4 | selection-1 | selection-2 |
USA | 5000 | 7000 | 5000 | 8000 | vendor-1 | vendor-3 |
India | 9000 | 8000 | 8000 | 9500 | vendor-2 | vendor-3 |
May 23 2021 09:15 PM
* What happens if there are 3 or more vendors that match the minimum value?
* You will be much better off if you can reorganize the data to the format
place | vendor | value |
usa | vendor-1 | 4000 |
usa | vendor-2 | 2000 |
etc. |
If you can do that, there are fairly straightforward approaches to consider.
May 23 2021 11:12 PM
If you wish to retain the crosstab layout and have Excel 365 you can return a sorted list of vendors using the SORTBY function
= LET(
valuesRanked, SORTBY(Values[#Headers],Values[@]),
INDEX(valuesRanked, {1,2}))
With older versions of Excel one is faced with ranking the values, using COUNTIFS as a tie-breaker, and then using MATCH/INDEX to lookup the header.
Note. I paced the value columns in a Table in order to provide structured references to the data.
May 24 2021 04:59 AM
i have attached a file, my problem is when two vendors have same rate then it should come in O column, like in Australia rate are 13000, but of different vendors but in my formula same Logistics vendor -3 occurs, i want in O column Logistics vendor 5 should come.
May 24 2021 11:59 AM
I prefer the simplicity of the 365 array solution but that is only any good if you have access to the app.
Otherwise you should be able to use something like
= INDEX(
availableRates,
MATCH( 1,
1 + COUNTIFS(availableRates,"<"&availableRates)
+ COUNTIFS(availableRates,availableRates, vendor,"<"&vendor),
0)
)
May 25 2021 12:07 AM
everything is ok in your normal excel formula, but problem occurs if any vendor give no rates or 0 then they should not be counted, like i highlighted in yellowed color .
May 25 2021 01:28 AM - edited May 25 2021 01:35 AM
I only develop for 365 these days, having disposed of earlier versions of Excel as unfit for purpose! However, casting my mind back to how I used to do these things, it would be relatively easy to test for zero rates. At present, I interpret a zero rate as an offer to carry the goods without charge (as opposed to a blank field or any text field that would lead to the vendor being eliminated from consideration).
To reverse this it would be necessary to add a specific test for zero rates
= INDEX( availableRates,
MATCH(1,
1 + COUNTIFS(availableRates, "<"&availableRates,
availableRates, ">0")
+ COUNTIFS(availableRates, availableRates,
vendor, "<"&vendor),
0)
)
In 365, this requires a FILTER
= LET(
sortedRates, SORTBY(availableRates,availableRates),
sortedVendors, SORTBY(vendor, availableRates),
stacked, IF({1;0}, sortedRates, sortedVendors),
filtered, FILTER(stacked, sortedRates>0),
INDEX(filtered, {1,2,1,2}, {1,1,2,2})
)
May 25 2021 11:57 AM
I applied the same logic but didn't work, as check the attached sheet, where AG column highlighted with red color are the error as no rate comes in L-1 .
May 25 2021 09:41 PM - edited May 25 2021 11:58 PM
@Hans Vogelaar @Peter Bartholomew
i have attached a file, i need min rates L-1,L-2,L-3, L-4 RATES, FROM VENDORS RATES G180:AF180 ROWS RANGE, AND ON BASIS OF MIN RATE, VENDORS NAME SHOULD BE REQUIRED .
May 26 2021 04:17 AM
@Hans Vogelaar @Peter Bartholomew
have attached a file, i need min rates L-1,L-2,L-3, L-4 RATES, FROM VENDORS RATES G180:AF180 ROWS RANGE, AND ON BASIS OF MIN RATE, VENDORS NAME SHOULD BE REQUIRED .
May 26 2021 06:26 AM
The text fields weren't ranked as I expected. The attached contains a test to eliminate text fields from consideration and may behave a little better.
Basically I switched to Excel 365 to avoid traditional spreadsheet formulas like this so I am probably not the best person to perfect the formulas.