Min if multiple criteria

Caly

Board Regular
Joined
Jul 19, 2015
Messages
160
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hello I have 5 prices from 5 different customers and looking to take the Minimum between all 5 but only where the margin is good.

Only way I can think of is to evaluate each of the 5 sets Of margins but that is very long

Is there a different way where if the formula will provide the lowest price based on a good margin?

Example data

In the below is the current price and margin along with teh acceptable margin floor

Underneath it shows the various prices to compare against

Under that is the expected result showing Price 2 since that margin is ok and is the lowest price point. Price 1 is lower but the margin is no good.

But how can I take the min price where the margin is good between 5 different values in a Formula?

Current PriceCurrent marginmargin Floor
$33.50​
30%​
15%​
Price 1marginPrice 2MarginPrice 3marginPrice 4marginprice 5Margin
$20​
10%​
$30​
15%​
$40​
20%​
$50​
30%​
$36​
40%​
Expected Result
PricemarginSource
30​
15%​
Price 2
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Most of this formula is working toward transforming your input data (arranging Price-Margin-Price-Margin...on a single row is not an efficient way to set up data to support analysis).
MrExcel_20231115.xlsx
ABCDEFGHIJ
1Current
2PriceMarginMargin Flr
3$33.5030%15%
4
5Offers
6Price 1MarginPrice 2MarginPrice 3MarginPrice 4MarginPrice 5Margin
7$2010%$3015%$4020%$5030%$3640%
8
9SourcePriceMargin
10Price 2300.15
Sheet1
Cell Formulas
RangeFormula
A10:C10A10=LET(pm,A6:J7,mflr,C3,tpm,TRANSPOSE(pm),p,CHOOSEROWS(tpm,SEQUENCE(ROWS(tpm)/2,1,1,2)),m,DROP(CHOOSEROWS(tpm,SEQUENCE(ROWS(tpm)/2,1,2,2)),,1),cpm,HSTACK(p,m),fpm,FILTER(cpm,INDEX(cpm,,3)>=mflr),TAKE(SORT(fpm,2),1))
Dynamic array formulas.
 
Upvote 0
Thank you.
Would something like this work using a Min(If?

Current PriceCurrent marginmargin Floor
$33.50​
30%​
15%​
Price 1marginPrice 2MarginPrice 3marginPrice 4marginprice 5Margin
$20​
10%​
$30​
15%​
$40​
20%​
$50​
30%​
$36​
40%​
Expected Result
PricemarginSource
30​
15%​
Price 2
Price 1Price 2Price 3Price 4price 5marginMarginmarginmarginMargin
$34​
$30​
$40​
$50​
$36​
10%​
15%​
20%​
30%​
40%​






Formula for the Price

MIN(IF(F16:J16>=$C$2,A16:E16,A2))


But how could I write this out where the words would show for where the price is coming from based on the min formula?
 
Upvote 0
Sorry, but I do not understand how your formula relates to the table excerpt that appears in your post...the range references don't make any sense to me. Please consider using the XL2BB add-in to make this easier. I've offered a solution to your original question...have you tried it?

If you want to tackle this problem using MIN or a similar function, then once the minimum value is determined, you would need to perform a lookup against the source data to determine where that minimum value can be found (this might involve any number of functions, such as INDEX/MATCH, INDEX/XMATCH, AGGREGATE used with ROW or COLUMN or SEQUENCE). But as presented, you have prices and margin percentages appearing in the same row, so finding the minimum price when prices are interspersed between other small values (the margins) cannot be done easily (this is the point I made in my earlier post about a single row of data).
 
Upvote 0
Thank you.
I don’t understand the different functions but would you be able to explain what each are doing so I may try it?

In the first portion what is LET referring to?
The pm? Tpm?

LET(pm,A6:J7,mflr,C3,tpm,TRANSPOSE(pm),p,CHOOSEROWS(tpm,SEQUENCE(ROWS(tpm)/2,1,1,2)),m,DROP(CHOOSEROWS(tpm,SEQUENCE(ROWS(tpm)/2,1,2,2)),,1),cpm,HSTACK(p,m),fpm,FILTER(cpm,INDEX(cpm,,3)>=mflr),TAKE(SORT(fpm,2),1))
 
Upvote 0
I rearranged the price and margin so they would appear not one after the other but instead have the prices together and then the margins together.

Price 1Price 2Price 3Price 4price 5margin 1Margin 2margin 3margin 4Margin 5
$34$30$0$50$3610%15%0%30%40%
And still using the above competitor to compare against the current and the margin floor
Current PriceCurrent marginmargin Floor
$33.5030%15%
I was trying this formula which returns the number price value okay
MIN(IF(F16:J16>=$C$2,A16:E16,A2))

But will this work if a value is zero? Or should I apply an iferror or ifna to factor if any of the 5 values are zero?

I am unsure how to write the formula so it will provide the source and approach used from where the price is coming from
 
Upvote 0
I rearranged the price and margin so they would appear not one after the other but instead have the prices together and then the margins together.
Unfortunately, that is also an undesirable structure. In Excel, formulas are easier to write and understand when the relationship between corresponding items is obvious and direct. If you have the option to restructure the initial data set, consider making it look like the blue range E1:G6 in the example below. Then if you want to use your "Current" margin (of 30%) to establish a minimum acceptable margin floor (15% in this case), you can more easily obtain the preferred Offer with a relatively simple formula.
MrExcel_20231115.xlsx
ABCDEFG
1CurrentOfferorPriceMargin
2PriceMarginMargin FlrVendor1$ 20.0010%
3$33.5030%15%Vendor2$ 30.0015%
4Vendor3$ 40.0020%
5Vendor4$ 50.0030%
6Vendor5$ 36.0040%
7
8Preferred
9Vendor2300.15
Sheet2
Cell Formulas
RangeFormula
A9:C9A9=TAKE(SORT(FILTER(E2:G6,G2:G6>=C3),2),1)
Dynamic array formulas.


The single formula here is...
Excel Formula:
=TAKE(SORT(FILTER(E2:G6,G2:G6>=C3),2),1)
  • It can be analyzed in three steps, beginning with the innermost formula, this part...
    Excel Formula:
    FILTER(E2:G6,G2:G6>=C3)
The initial full range of data (E2:G6) is filtered to extract only those rows where the margin (in cells G2:G6) is greater than or equal to the acceptable margin floor in C3. Note the continuous range G2:G6 where the margin array is located, and immediately beside that in F2:F6 is the price array, and immediately beside that in E2:E6 is the offeror array. This produces an intermediate array that looks like this...
Vendor2300.15
Vendor3400.2
Vendor4500.3
Vendor5360.4
  • The intermediate result is then processed by the SORT function and sorted by column 2 (the price array) in ascending order (the default), so up to this point, the innermost part of the formula resembles this (I've replaced the FILTER formula with a text description here)...
    Excel Formula:
    SORT(filtered array from above,2)
...and the resultant array from the SORT operation looks like this...
Vendor2300.15
Vendor5360.4
Vendor3400.2
Vendor4500.3
  • Because this last array was sorted with the smallest price in the first row, the final step is to simply extract the first row using the TAKE function, to produce this result, the offer whose margin satisfies the margin floor requirement and whose price is lowest...
    Excel Formula:
    TAKE(sorted and filtered array from above,1)
...and since only one row is extracted, the resultant array from this formula looks like this...
Vendor2300.15

To answer your earlier question about LET...LET is a function available in Excel 365 that allows you to define a variable name and assign a value, range, or formula to that name. This is convenient when multiple steps are involved, especially when those steps cannot be easily nested together. In the example just above, with the source data arranged in a more efficient structure, we can nest the three steps and obtain the final answer in a straightforward manner. In the earlier example, with the inefficient table structures shown in posts 1 and 2, we need to first create the well-structured arrays before getting down to the actual job of finding the preferred offer. So each of the oddly abbreviated items are variable names that I arbitrarily chose.
Excel Formula:
=LET(
pm,A6:J7, [pm is the range where we find the Price-Margin single row array with alternating price-margin values]
mflr,C3,    [mflr is the single cell reference to the acceptable margin floor]
tpm,TRANSPOSE(pm),   [tpm is the pm range transposed to create a single column of alternating price-margin values]
p,CHOOSEROWS(tpm,SEQUENCE(ROWS(tpm)/2,1,1,2)),  [p is the price array formed by extracting from tpm the odd-numbered array positions (odds generated by SEQUENCE)]
m,DROP(CHOOSEROWS(tpm,SEQUENCE(ROWS(tpm)/2,1,2,2)),,1),  [m is the margin array formed by extracting from tpm the even-numbered array positions (evens generated by SEQUENCE)]
cpm,HSTACK(p,m),  [cpm is the consolidated price margin array that looks like the preferred structure in the example above, with Offeror, Price, and Margin in 1st, 2nd, and 3rd columns]
fpm,FILTER(cpm,INDEX(cpm,,3)>=mflr),  [fpm is the filtered price-margin array...same approach described in the first step of the example above]
TAKE(SORT(fpm,2),1)   [the final step is to SORT fpm and then TAKE the top row...again, exactly the same approach described in the 2nd and 3rd steps of the example above]
)

You'll see that most of the steps in the LET function can be avoided by beginning with a better data table structure, thus avoiding the need to extract like types of data and transform them into arrays that can be used to efficiently solve the problem. Inside the LET function, we finally create a useful array with the "cpm" formula...a lot of work to reach that point, when we could have simply started with the source data in this preferred structure.

One last comment about something you mentioned: 0's and blanks. If a real zero is entered, then the formulas will consider them...so a Price of 0 would indeed be preferred over other prices. A blank, however, will be ignored.
 
Upvote 0
After thinking about this, an improved version extends the SORT operation: sorting first by the Price column in ascending order, and then by the Margin column in descending order. This finds the preferred option for the case where two vendors might both satisfy the margin floor requirement and they both have the same lowest price, but the margin for one of them is more attractive than the other:
MrExcel_20231115.xlsx
ABCDEFG
1CurrentOfferorPriceMargin
2PriceMarginMargin FlrVendor1$ 20.0010%
3$33.5030%15%Vendor2$ 30.0015%
4Vendor3$ 30.0020%
5Vendor4$ 50.0030%
6Vendor5$ 36.0040%
7
8Preferred
9Vendor3300.2
Sheet2
Cell Formulas
RangeFormula
A9:C9A9=TAKE(SORT(FILTER(E2:G6,G2:G6>=C3),{2,3},{1,-1}),1)
Dynamic array formulas.
 
Upvote 0
Solution
Thank you this worked great and I was able to adjust per your advice
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,636
Members
452,663
Latest member
MEMEH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top