How to write a formula to return best pricing and order qty among multiple price breaks

[Marco Mama]

Hi, I am trying to do an analysis to determine what is the best order qty and pricing considering inventory carrying cost. I put together an example but my file has more than 200 lines.



This table has my source data and calculation to determine what is the best order qty and pricing.

Take PN 1 for example, it is best for me to order at 200 units at pricing of 0.6.

PN	Order Qty	Price	Best Order Qty
PN1	100​	0.7​	Yes
PN1	200​	0.6​	Yes
PN1	300​	0.5​	No
PN2	150​	0.4​	Yes
PN2	250​	0.3​	Yes
PN2	350​	0.2​	No
PN2	450​	0.1​	No
PN3	50​	1.8​	Yes
PN3	100​	1.7​	No
PN3	150​	1.6​	No
PN3	200​	1.5​	No

I would like to know how to write a formulate using above input to spit out a table below.
Thanks!

PN	Best Order Qty	Price
PN1	200​	0.6​
PN2	250​	0.3​
PN3	50​	1.8​

 

[Scott T]

Does this do what you want

Book1
	A	B	C	D
1	PN	Order Qty	Price	Best Order Qty
2	PN1	100	0.7	Yes
3	PN1	200	0.6	Yes
4	PN1	300	0.5	No
5	PN2	150	0.4	yes
6	PN2	250	0.3	Yes
7	PN2	350	0.2	No
8	PN2	450	0.1	No
9	PN3	50	1.8	Yes
10	PN3	100	1.7	No
11	PN3	150	1.6	No
12	PN3	200	1.5	No
13
14
15
16
17	PN	Best Order Qty	Price
18	PN1	200	0.6
19	PN2	250	0.3
20	PN3	50	1.8
Sheet1

Cell Formulas
Range	Formula
B18:C20	B18	=INDEX(B$2:B$12,AGGREGATE(14,6,(ROW($A$2:$A$12)-ROW($A$2)+1)/(($A$2:$A$12=$A18)*($D$2:$D$12="yes")),1))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.