New Contributor

# Excel formulas/function help

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 300 units at pricing of 0.6.

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

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

 PN Best Order Qty Price PN1 200 0.6 PN2 250 0.2 PN3 50 1.5

Thank you for any help/tip!

3 Replies

# Re: Excel formulas/function help

Check this ,, has formula in cell D3:

``=IF(AND(B3=\$F\$2,C3>=\$F\$3),"Yes","No")``

An array (CSE) formula in cell H3:

``{=IFERROR(INDEX(A\$3:A\$13, SMALL(IF(\$D\$3:\$D\$13="Yes", ROW(\$D\$3:\$D\$13)-ROW(\$D\$3)+1), ROW(1:1))),"" )}``

• Finish formula with Ctrl+Shift+Enter, and fill across the range.
• Adjust cell references in the formula as needed,

# Re: Excel formulas/function help

@Rajesh-S thank you very much.

does this mean to calculate values in D (best order qty), I need to adjust F2 and F3, as each PN have different pricing tiers?

That is something I hope to shy away as my actual file has maybe 100-200 unique PNs and each PN has 3-6 price tiers.

# Re: Excel formulas/function help

Simplest way is to use Power Query if you consider such option - filter on Best Order; sort by PN (ascending) and Price (descending), remove duplicates.