Mar 02 2021 02:10 PM
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!
Mar 02 2021 10:37 PM
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))),"" )}
Mar 04 2021 10:30 AM
@Rajesh_Sinha 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.
Mar 04 2021 11:26 AM
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.