Forum Discussion
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
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.
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,
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.
