Min if multiple criteria

[Caly]

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 Price	Current margin	margin Floor
$33.50​	30%​	15%​
Price 1	margin	Price 2	Margin	Price 3	margin	Price 4	margin	price 5	Margin
$20​	10%​	$30​	15%​	$40​	20%​	$50​	30%​	$36​	40%​
Expected Result
Price	margin	Source
30​	15%​	Price 2

 

[KRice]

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
	A	B	C	D	E	F	G	H	I	J
1	Current
2	Price	Margin	Margin Flr
3	$33.50	30%	15%
4
5	Offers
6	Price 1	Margin	Price 2	Margin	Price 3	Margin	Price 4	Margin	Price 5	Margin
7	$20	10%	$30	15%	$40	20%	$50	30%	$36	40%
8
9	Source	Price	Margin
10	Price 2	30	0.15
Sheet1

Cell Formulas
Range	Formula
A10:C10	A10	=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.

 

[Caly]

Thank you.
Would something like this work using a Min(If?

Current Price	Current margin	margin Floor
$33.50​	30%​	15%​
Price 1	margin	Price 2	Margin	Price 3	margin	Price 4	margin	price 5	Margin
$20​	10%​	$30​	15%​	$40​	20%​	$50​	30%​	$36​	40%​
Expected Result
Price	margin	Source
30​	15%​	Price 2
Price 1	Price 2	Price 3	Price 4	price 5	margin	Margin	margin	margin	Margin
$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?

 

[KRice]

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).

 

[Caly]

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))

 

[Caly]

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 1	Price 2	Price 3	Price 4	price 5	margin 1	Margin 2	margin 3	margin 4	Margin 5
$34	$30	$0	$50	$36	10%	15%	0%	30%	40%

And still using the above competitor to compare against the current and the margin floor

Current Price	Current margin	margin Floor
$33.50	30%	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

 

[KRice]

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
	A	B	C	D	E	F	G
1	Current				Offeror	Price	Margin
2	Price	Margin	Margin Flr		Vendor1	$ 20.00	10%
3	$33.50	30%	15%		Vendor2	$ 30.00	15%
4					Vendor3	$ 40.00	20%
5					Vendor4	$ 50.00	30%
6					Vendor5	$ 36.00	40%
7
8	Preferred
9	Vendor2	30	0.15
Sheet2

Cell Formulas
Range	Formula
A9:C9	A9	=TAKE(SORT(FILTER(E2:G6,G2:G6>=C3),2),1)
Dynamic array formulas.

The single formula here is...

=TAKE(SORT(FILTER(E2:G6,G2:G6>=C3),2),1)

• It can be analyzed in three steps, beginning with the innermost formula, this part...

  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...

Vendor2	30	0.15
Vendor3	40	0.2
Vendor4	50	0.3
Vendor5	36	0.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)...

  SORT(filtered array from above,2)

...and the resultant array from the SORT operation looks like this...

Vendor2	30	0.15
Vendor5	36	0.4
Vendor3	40	0.2
Vendor4	50	0.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...

  TAKE(sorted and filtered array from above,1)

...and since only one row is extracted, the resultant array from this formula looks like this...

Vendor2	30	0.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.

=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.

 

[KRice]

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
	A	B	C	D	E	F	G
1	Current				Offeror	Price	Margin
2	Price	Margin	Margin Flr		Vendor1	$ 20.00	10%
3	$33.50	30%	15%		Vendor2	$ 30.00	15%
4					Vendor3	$ 30.00	20%
5					Vendor4	$ 50.00	30%
6					Vendor5	$ 36.00	40%
7
8	Preferred
9	Vendor3	30	0.2
Sheet2

Cell Formulas
Range	Formula
A9:C9	A9	=TAKE(SORT(FILTER(E2:G6,G2:G6>=C3),{2,3},{1,-1}),1)
Dynamic array formulas.

 

[Caly]

Thank you this worked great and I was able to adjust per your advice

 

[KRice]

You’re welcome…I’m happy to help.